DHIS2 Tracker/Event – Power BI data modeling

DHIS2 provides a robust Web API that allows external systems or tools to access and manipulate data stored within your DHIS2 instance. This API is particularly useful for the following purposes:

• Metadata Management:
  • Import or export metadata into/from your DHIS2 instance.
• Data Integration:
  • Push or pull data into a DHIS2 instance. For instance, data exchange between a Ministry of Health (MoH) and an Implementing Partner.
  • Integration with analytical tools such as PowerBI and Tableau for data visualization and analysis.

In this writeup I will focus on describing how to construct a Tracker/Event-based data model within PowerBI.

The Tracker Web API in DHIS2 encompasses three main endpoints: Tracked Entity Instances, Enrollments, and Events, all of which support Create, Read, Update, and Delete (CRUD) operations (as documented by DHIS2).

Base URL:

https://yourinstance.com/

Core Endpoints:

Tracked Entity Instance

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

This endpoint allows you to retrieve tracked entity instance data, including relevant attributes, for a specific program and organization unit.

Enrollment

Endpoint:

/api/enrollments.json?ou=rootOrgUnitID&ouMode=DESCENDANTS&program=programID&fields=orgUnit,
trackedEntityInstance,enrollmentDate,incidentDate,program &paging=false&skipMeta=true

This endpoint retrieves enrollment information for a specific program and organization unit. Multiple enrollments may exist, but this example focuses on one.

Events

Endpoint:

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

This endpoint retrieves event data with nested payload elements. You can customize the response by specifying fields of interest and applying filters.

Other Important Endpoints for your Model:

Data Elements

Endpoint:

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

This endpoint fetches data elements used within a specific program identified by its ID.

Organisation Units

Endpoint:

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

This endpoint retrieves organization units, including their parent units and hierarchical information. It’s useful for drill-down analyses in PowerBI.

OptionSet Values

Endpoint:

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

This endpoint is used to retrieve values from custom option sets, which can be essential when dealing with specific data selections.

Now that we have good understanding of the essential end points; to import data from each of these endpoints into Power BI, follow these steps:

1. In Power BI, select the “Get Data” option.
2. Choose the “Web” source within Power Query to establish a connection to your desired DHIS2 API endpoint.
3. Import the corresponding tables into Power BI.
4. Depending on the structure of your data, you might need to perform data transformations, especially for tables like “trackedEntityInstances” and “events.”

By following these steps, you can efficiently bring DHIS2 data into your Power BI environment, allowing for further analysis and visualization.

Creating a reusable Power Query function in Power BI for parameterized API calls to DHIS2 endpoints, building relationships, and performing data analysis and visualization is a multi-step process. Below, I’ll outline the steps along with Power Query and Power BI functions for reusability:

Step 1: Create Parameterized API Function in Power Query

In Power Query, you can create a function to call DHIS2 API endpoints. Here’s an example of how to create a parameterized function to fetch data from a DHIS2 API endpoint:

let
    GetDHIS2Data = (BaseUrl as text, Endpoint as text, QueryParameters as text) =>
        let
            Url = BaseUrl & Endpoint & QueryParameters,
            Source = Json.Document(Web.Contents(Url)),
            Data = Source[data]
        in
            Data
in
    GetDHIS2Data

This function takes three parameters: BaseUrl (the base URL of your DHIS2 instance), Endpoint (the specific API endpoint), and QueryParameters (additional parameters to customize the API call).

Step 2: Call the API Function

You can use this function to call various DHIS2 API endpoints and fetch data. Here’s an example of how to call the function to retrieve Tracked Entity Instances:

let
    BaseUrl = "https://yourinstance.com/",
    Endpoint = "api/trackedEntityInstances.json?",
    QueryParameters = "ou=rootOrgUnitID&ouMode=DESCENDANTS&program=programID&
fields=trackedEntityInstance,trackedEntityType,orgUnit,created,lastUpdated,
attributes[displayName,attribute,value,storedBy]&
paging=false&skipMeta=true",
    TrackedEntityInstances = GetDHIS2Data(BaseUrl, Endpoint, QueryParameters)
in
    TrackedEntityInstances

Step 3: Build Relationships in Power BI

In Power BI, you can use the Power Query results to build relationships between tables. You can also use the “Manage Relationships” feature to create relationships between tables based on common fields such as trackedEntityInstance or orgUnit.

Step 4: Data Analysis and Visualization

With your data model and relationships established, you can perform data analysis and create visualizations. This includes creating DAX measures for calculations.

Here’s an example of how to create a simple DAX measure to count Tracked Entity Instances:

Total Tracked Entities = COUNTROWS(TrackedEntityInstances)

Step 5: Reusability and Extensibility

To enhance reusability and extensibility, you can create additional functions for other DHIS2 endpoints, such as Events, OrganisationUnits, DataElements, OptionSets, TrackedEntityAttributes, Enrollments, etc., following a similar pattern as the GetDHIS2Data function.

You can then use these functions in different parts of your Power BI report to fetch data from various DHIS2 endpoints as needed.

Step 6: Visualize and Report

Use Power BI’s visualization capabilities to create reports and dashboards based on the data you’ve imported. You can drag and drop fields onto the canvas to create charts, tables, maps, and more.

Step 7: Data Refresh and Publishing

Configure data refresh settings in Power BI Service to keep your reports up-to-date with the latest data from DHIS2. After thorough testing, publish your report to Power BI Service for wider access.

By parameterizing your API calls and creating reusable functions, you make it easier to maintain and expand your Power BI report as new data becomes available or new DHIS2 endpoints are needed. Additionally, documenting how these endpoints relate to each other in your data model will help users understand the structure and logic of your report.