Building Your DHIS2 Tracker Event -Power BI Model

 DHIS2 offers the Web API as a robust means for external systems or tools to access and manipulate data stored in your DHIS2 instance. This comes in handy whenever you want to:

  1. Push metadata in or out of the instance,
  2. Push/pull data into a DHIS2 instance i.e. MoH to Implementing Partner or into analytical tools such as PowerBI and Tableau.

The focus of this document is building a Tracker/Event-based data model in PowerBI. Of importance, the tracker Web API consists of 3 endpoints (tracked entity instances, enrollments, events) with full CRUD support (DHIS2 Documentation), refer – Figure 1.


Figure 1: EndPoints

Baseurl: https://yourdhis2.com/prod/


Essential/Core Endpoints

Endpoint

Sample Payload

API Call

TrackedEntity Instance




Baseurl/api/trackedEntityInstances.json?ou=L66MANY1j43&ouMode=DESCENDANTS&program=yw0mVtYaERN&trackedEntityInstance=ydKJVwz8h3t&fields=trackedEntityInstance,trackedEntityType,orgUnit,created,lastUpdated,attributes[displayName,attribute,value,storedBy]&paging=false&skipMeta=true

 

Enrollment

Baseurl/api/enrollments.json?ou=L66MANY1j43&ouMode=DESCENDANTS&program=yw0mVtYaERN&fields=orgUnit,trackedEntityInstance,enrollmentDate,incidentDate,program&trackedEntityInstance=ydKJVwz8h3t&paging=false&skipMeta=true

 

You may have more than one enrollment in your case, in this example I only have one. In that case, you would see the enrollment

Events

Baseurl/api/events.json?fields=event,trackedEntityInstance,attributeOptionCombo,attributeCategoryOptions,dataValues[dataElement,value,lastUpdated]&ou=L66MANY1j43&ouMode=DESCENDANTS&program=yw0mVtYaERN&paging=true&event=svW9QXl1xmc

 

Note – I consider this the most interesting entity, given how nested the payload is setup. Declaring “fields:all” will get you all parameters within each response, this can make the call very heavy. Therefore , limit the parameters you are interested in. Filters included here are: orgunits, program and the event (am only pulling one event for demo purposes, drop that last filter)

Other Important Entities for your model

Data Elements


 

 

Baseurl/api/programs/yw0mVtYaERN.json?fields=id,name,programStages[id,name,programStageDataElements[dataElement[id,name]]]&paging=false

 

Note – here, we are only getting the data elements used within the program of interest i.e program id = yw0mVtYaERN

Organisation Units

Baseurl/api/organisationUnits/L66MANY1j43.json?fields=parent[id,name,level],name,id,level&includeDescendants=true&paging=false

 

Note – This pull’s all org units under the root i.e includeDescendants = true. In my setup, I have a global hierarchy with multiple countries, as such, am only interested with the facilities of one country (id= L66MANY1j43), and their parent ids/facilities since this will be important in your sites drill down in PowerBI

 

OptionSets values**

Baseurl/api/optionSets/mOwSXwV34qE.json?fields=name,options[name,id]

 

 

** –In this example, the data clerk has three options to select form, thus the need for a custom option set. In case you have custom option sets in your tool, then you can use the API call above.

 


For each end point above, use the “Web” option in Power Query to pull each table.  You may need to transform some of the sources accordingly (i.e the trackedEntityInstances and events tables – we can look in to this in a different session)

Note – It will prompt you for authentication details to your DHIS2, this happens once since the settings will apply globally.

Comments

Popular posts from this blog

DHIS2 Database Migration/Restoration Process

Certbot Installation and Configuration(Ubuntu)