Addition of Custom Jobs/Views :

MSF today would like to leverage their patient data to improve efficiency, reduce unnecessary variation and waste, and identify and address gaps in quality of care. One of the main reasons today reporting and data analysis is not as effective as it could be is not being able to extract data from Bahmni and use it with the right tools. Most of the time today is spent managing and creating data (paper based excel reports are collected at various project centres) and constructing data sets in custom softwares like excel rather than generating ad-hoc reports on the fly and analysing the data.

One of the steps in the direction of easing reporting is to design how we store the input data generated from the hospitals and missions. For this we came up with the design of an analytics database where hierarchical database is flattened and pivoted. This database is called the Mart DB or simply the analytics DB. This piece of solution would

Make it easier for various Data Analytics tools to directly consume Bahmni Analytics database
Would make it possible for Implementers to extract data / modify existing datasets without totally understanding the openmrs data model.
Enable Developers to define new reports / extract datasets of interests faster.

In essence the pivoted and flattened structure will remove the hierarchical nature of the data.

A few views have also been created using the flattened tables of the analytical DB based on the needs of the users / statisticians which provides data from multiple tables by running simple sql queries

Jobs Types & Grouped tables:-

To simplify the amount of configuration that we are providing to the bahmni-mart application, we have packaged together similar tables under the respective types. Now the user just need to add a single job of the packaged type and can get the all the tables under that type

Below are the packaged job types and the jobs under that type

Job Type	Grouped tables
Programs	Programs_default, program_outcomes_default,patient_program_data_default,program_workflow_default, program_workflow_states_default, program_attributes
Patients	Patient_state_default, patient_allergy_status_default, patient_identifier
appointments	Patient_appointment_default, appointment_service_default, service_availability_default, appointment_speciality_default
bedManagement	Bed_patient_assignment_default, bed_tags_default, current_bed_details_default
location	Location_default, location_tag_map_default, location_attribute_details_default
operationTheater	Surgical_block_default, surgical_appointment_default, surgical_appointment_attribute_type_details_default, surgical_appointment_attributes
person	Person_details_default, person_address_default, person_attribute_info_default, address_hierarchy_level_default, person_attributes
provider	Provider_default, provider_attribute_details_default, provider_attributes
visitsAndEncounters	Patient_visit_details_default,patient_encounter_details_default,visit_attribute_details_default,visit_attributes
medicationAndOrders	Medication_data_default, [All forms under All Orderables as tables]
diagnosesAndConditions	Conditions_default, [All forms under Visit Diagnoses as tables]
bacteriology	[All forms under Bacteriology Concept Set as tables]
metadata	All concept related information class, datatype, with respect to source.
obs	[All forms under All Observation Templates as tables]
Forms 2.0	[All forms which are created with Implementer Interface ]
reg	reg_{table Name}
disposition	[All forms under Disposition Set as tables]

Link to the example grouped job configuration

{
      "name": "Person",
      "type": "person",
      "chunkSizeToRead": "500",
      "groupedJobConfigs": [
        {
          "tableName": "person_attributes",
          "columnsToIgnore": [
            "primaryContact",
            "secondaryContact",
            "primaryRelative",
            "familyNameLocal",
            "givenNameLocal",
            "middleNameLocal"
          ]
        },
        {
          "tableName": "person_details_default",
          "columnsToIgnore": [
            "prefix",
            "given_name",
            "middle_name",
            "family_name_prefix",
            "family_name",
            "family_name2",
            "family_name_suffix"
          ]
        }
      ]
    }

Addition of Custom Jobs/Views :

Bahmni-mart enables the user to add custom jobs & views in addition to the default jobs provided. Below are the respective example configurations.

Custom Sql Job Configuration: 
{
 "name": "Patient Info",
 "type": "customSql",
 "table_name": “patient_info",
 "readerSql": "SELECT * FROM patient",
 "incrementalUpdateConfig": {
   "updateOn": "patient_id",
   "eventCategory": "Patient",
   "openmrsTableName": "patient"
 }
 }

Note : Reader sql should be executable on openmrs database. Incremental configuration is optional and can be given only when you need an incremental update for this job.

Custom View Configuration :

{
 "name": "Person Details View",
 "sql": "SELECT * FROM person_details_default pdd JOIN
         person_address_default pad ON pad.person_id = pdd.person_id "
}

Note : sql should be executable on analytics database since we are creating view in analytics database

List of views from different jobs:

Difference between standard Grouped Jobs, Custom Jobs and Views:

As part of standard grouped jobs we flatten the data from openmrs but we don’t get to see the readerSql for each job. The readerSql gets generated dynamically as part of the code. So adding new columns is not straight forward and it requires change at code level.

When it comes to Custom jobs, we can directly see the readerSql and it redirects output to the desired table in the analytics. Adding new columns to the analytics table is straightforward as its a change in the sql query.

The only configuration possible for grouped and custom jobs so far is, columnsToIgnore. This will ignore the specified columns mentioned under columnsToIgnore json from openmrs database to analytics database.
For ignoring columns, each column in a table have to be specified. We can’t specify at job level and it has to be under that particular table name for the job.
We can select particular job to be incremental/full load irrespective of entire bahmni-mart job type.

eg: If the bahmni-mart is running on incremental load, we can still mention few jobs to run on full load.

For Views, we can see the sql query but the query runs on the analytics tables but not on the openmrs tables. As part of the views query, we usually combine one or more analytics tables and create a table(view) out of these tables. If there is a data change in the above analytics tables then the view gets updated automatically

Views will not have the option to ignore columns, incremental/full load options. If we want few columns to be added/ignored that can be directly done in the specific view query.

Setting Up CronJob for bahmni-mart:

To sync up analytics db with openmrs db, we can run bahmni-mart on regular intervals. That can be done once in a day/week based on the requirement. For to run bahmni-mart command automatically we can configure the cron job which will trigger the bahmni-mart command at the specified time in the day.

Sample cornjob for bahmni-mart

The above cronjob runs the Bahmni-mart command daily at 11 pm in the night. The preferable time would be running the mart is after working hours for an implementation. This will run the mart on incremental load so the entire job wouldn’t take much time.

Bahmni Wiki

Overview

Addition of Custom Jobs/Views :