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.