What is DHIS2?
DHIS is an open source software platform for reporting, analysis and dissemination of data for all health programs.
DHIS2 covers aggregated data (e.g. routine health facility data, staffing, equipment, infrastructure, population estimates), and event data (disease outbreaks, survey/audit data, patient satisfaction surveys, longitudinal patient records etc.).
DHIS 2 comes with its own easy to use analytics interface, and can be extended with Apps or used by third-party software through the open Web-API.
Why this Blog?
Though there are many articles/solutions that can help you to connect Power BI to DHIS2’s aggregated data. We realised that there’s almost no information that covers the “Event” & “Tracker capture” side of DHIS2. Therefore, in this blog I will guide you to the resources & basic methods using which you can connect to your own DHIS2 account & get the data related to Events & Tracker capture.
Authentication methods in DHIS2:
Before diving in the process of fetching the data via API, let’s discuss the authentication methods available in DHIS2.
The DHIS2 Web API supports two main protocols for authentication, i.e. Basic Authentication and OAuth 2.
As many may already be aware that OAuth2 authentication cannot be tackled directly in Power BI, it must be handled by creating a Custom connector (which will be covered in another Blog).
Today, we will be using Basic authentication, which is a simple technique which sends login credentials over HTTP to a web server.
Required Credentials:
To access the DHIS2 data, we need 3 things from DHIS2, which are URL, Username & Password.
- URL: It is the web URL which appears in your browser’s address bar when you are logged in to the DHIS2 account e.g. https://play.dhis2.org/2.34.0/
- Username & Password: These are the user credentials using which you get access to the account.
Endpoints in DHIS2:
There are endpoints to call every metric available in DHIS2. Today, I will only be showing the basics of calling endpoints in Power BI. Firstly, we will call “Organisation Units” (which is pretty straight forward) & then “Events” for which we will need to use the “Custom Function” feature of Power BI.
To get the full list of endpoints available in DHIS2 you can either call https://play.dhis2.org/2.34.0/api (Text before “/api” will change based on your URL) in your desired API testing application or you can check out the documentation here: https://docs.dhis2.org/2.34/en/dhis2_developer_manual/web-api.html
Just before we get started, if you’re aware of the paging limitation in API, you needn’t worry as it can be tackled in DHIS2 by simply adding “paging=false” at the end of your endpoints.
Get Organisation Units in Power BI:
First, we will see an example on how to get the Organisation units of the DHIS2 events in Power BI.
Step 1: Open Power BI desktop. Click on “Get Data” from the Home ribbon & select “Web”.
Step 2: Copy the URL. E.g. https://play.dhis2.org/2.34.0/api/organisationUnits?paging=false & paste in the URL textbox. Then, click on OK.
Step 3: Select “Basic” from the pane on the left & enter the DHIS2 account credentials.
Step 4: Click on the highlighted “List” to navigate to the content of the endpoint.
Step 5: Then from “Transform” ribbon, click on “To Table” option.
Step 6: A new column named “column1” will appear. Click on the box next to the column header & select the columns that needs to be expanded.
We have successfully brought DHIS2’s Organisation units in Power BI. The data will look like something below:
Get Events in Power BI:
Calling the Events endpoint in Power Bi is a little complicated because its endpoint requires an additional input of a unique id of your desired choice between programs, organisation units, stages or more.
We will be using the recently brought Organisation units, specifically the “id” column as the input to the endpoint so that it can call the events for all the Organisation units dynamically.
To do that you need to know about Power BI features such as “Parameters” & “Custom Functions”.
I will not be going into details about the parameters & custom function in this blog (Click here to see detailed blog on how to use Parameter & Custom Functions for calling API’s)
Step 1: Follow Step 1 to Step 5 Similar to “Get Organisation Units in Power BI”. Except in its Step 2 use https://play.dhis2.org/2.34.0/api/events?paging=false&orgUnit=<enter_orgunit> as the endpoint. Name the query “GetEvents”.
Step 2: Create a new parameter for “Organisation Unit” & make the query “GetEvents” get organisation unit from the parameters. Then create a custom function of “GetEvents”.
Step 3: Create a duplicate query of “Organisation Unit”. Click on its “id” column & select “Invoke Custom Function” from the “Add Column” ribbon & invoke the “GetEvents” custom function.
Step 4: Expand the new column to get data of the events for each organisation unit. Transform the column by using “remove error” function & expand the column accordingly.
Conclusion:
If you want to bring data from DHIS2 to Power BI, you can follow one of the two steps mentioned above depending on the requirements of the endpoint.
The data you get from following these steps are raw data coming directly from DHIS2. It will then require some transformations before you can start creating visualisations & analyse the data.
Karan Nair
Team Lead – Data Analytics
Addend Analytics