DHIS2 to PowerBI connector is ready! Check out the video, test it, use it and give us feedback!

DHIS2-to-Power BI connector

Strengthening DHIS2 Data Visualizations

Background

Recently, the use of DHIS2 by several Ministries of Health, global health and development organizations to collect, manage and visualize routine program data has reached a new level of sophistication, with many countries using DHIS2 at national scale on several health areas and with increased data granularity. This has required more sophisticated and robust data use for adaptive program management, and data users are demanding more advanced analytics capabilities beyond DHIS2’s core functionality. In addition, organizations maintain databases housed in other formats such as Excel, Access, ODK/Kobo/ONA, SQL, Sharepoint, Salesforce, and others. As a result, there is demand for more robust data analytics platforms that enable data mashups from the variety of sources where data are stored as well as more advanced visualization features. One of the many popular business intelligence (BI) tools used by organizations is Microsoft PowerBI which enables the design of interactive visualizations that can be used to create reports, dashboards and custom visualizations. It also provides a desktop-based interface known as “PowerBI Desktop” which offers data warehouse capabilities such as data preparation, data discovery and interactive dashboards. The use of PowerBI for visualizing DHIS2 data is increasing amongst global health and development organizations, however, users often require support to more efficiently exchange data between DHIS2 and Power BI. Currently, many users perform a manual and time-consuming data exchange process, exporting and importing clean, aggregate data from DHIS2 into PowerBI, rather than utilizing DHIS2’s API.

In order to respond to the needs of data users, thanks to funding from Digital Square, BAO Systems in partnership with Population Services International (PSI) developed the DHIS2-to-PowerBI Connector as a user-friendly utility designed to enable users to more seamlessly:

  • Connect to DHIS2 via user authentication
  • Get data from DHIS2 for querying
  • Create an optimal data model and shape data for robust analytics in PowerBI
  • Refresh the data model with the latest DHIS2 data

Link to YouTube video:

Methodology

The custom connector leverages the Power BI Connector SDK to retrieve core data from DHIS2 using DHIS2 web API methods. These data are transformed by the connector into a preliminary data model optimized for Power BI, which can then be further tailored to meet data analysis needs. By integrating seamlessly into the “Get Data” function in Power BI Desktop, the data connector makes it easy for users to query, shape and mashup DHIS2 data to build custom reports and interactive dashboards.
The connector allows import of: Organisation Unit metadata (basic attributes including Lat/Long), Data Element metadata (core attributes), Data Element Values (numeric values associated with Data Element between Start/End Date range for OU Levels specified by the PBI data modeler), Dynamic Dimensions like Categories, Attributes, Category Option Group Sets, Organisation Unit Group Sets and Data Element Group Sets, a Date table (dynamically generated by the Connector), and a Periods table (a dynamically generated ‘join’ table to facilitate connecting Dates with Data Element Values).
The connector currently functions with DHIS2 versions 2.29, 2.30, 2.31, 2.32 (API versions 29, 30, 31, and 32 respectively). The connector has a known incompatibility with DHIS2 version 2.28 and has not been tested with versions prior. The connector imports data element values in the DHIS2 Aggregate data model and it does not currently import Indicators, Events, and Tracker models, however, these features are on the connector’s roadmap for future development efforts.

Once users install the DHIS2 Custom Connector on Power BI Desktop, they can easily import the desired data through an interactive interface by providing log-in details to the desired DHIS2 instance and selecting the metadata and data they want to import. Once the user’s credentials are authenticated, a Navigation box will pop up, displaying the various DHIS2 tables available to import, including:

  • Dimensions: multiple tables representing category option combinations and attribute option combinations,
  • Data Element Values: the values associated with each individual data element,
  • Data Elements: all data elements available in the DHIS2 instance selected,
  • Date,
  • Organization Units,
  • Periods.

After data import, users can work on the desired data model and clean and manipulate data leveraging analytics capabilities of Power BI, including importing Relationship type and Org unit hierarchies. When need be, it is also possible to refresh data to sync newly available data from the DHIS2 instance selected and update the data model in Power BI accordingly.

Please try it, use it and provide us with feedback so we can make this tool more useful and widely available within the DHIS2 community. We look forward to hearing from you all!

Population Services International & BAO Systems

DHIS2-to-PBI Connector User Manual.pdf (2.3 MB)

Link to the Connector: CLICK HERE

24 Likes

Where is the connector?

1 Like

Hi @Waka_Wafubwa, We will be updating the link to the files shortly on the post above.

2 Likes

Thank you

1 Like

@Waka_Wafubwa you can find it here DHIS2-PowerBI Connector , the tool is very useful. Great appreciation to the developers!

4 Likes

Hi there.

I do not able to install the .mez file.

1 Like

Thanks for sharing! The connector works well. Saved a lot of data cleaning and wrangling time.

3 Likes

Did you create the following folder path (make sure that allspaces and casing are maintained):
*C:\Users<Your User Name>\Documents\Power BI Desktop\Custom Connectors*

image

Please refer to pages 5 - 7 of the installation manual. If not successful I can assist you remotely.

2 Likes

Thanks @mykbitz . It works now.

1 Like

Big thanks to the developers for this exciting contribution! What is the known issue with 2.28?

2 Likes

Thanks for sharing this. I tried it and it is working well. The only problem I am facing is that there are some “Org Unit Ids” missing under “Data Element Values”, so I cannot effectively link the “Organizations Units” with “Data Element value” Thanks for your help

1 Like

Thanks

1 Like

Hi everyone. I tried to access the connector but it redirected me to an access right submission for my gmail account. Since yesterday i didn’t get any news. Is there any other solution ?

Thanks,

Abdou

1 Like

Hi @abdouseye, sorry about that; kindly try again and let me know if you are unable to.

1 Like

Hi @jomutsani, thank you very much for the help. It now works fine, i was able to download.

1 Like

This is great. Hope the connector for Tracker will also come soon.

1 Like

Awesome feature! Would it be possible to have Tracker Events also be extracted into PowerBI? And if so, is there any kind of timeline of when we could be expecting this feature? Thanks :slight_smile:

2 Likes

I tried this and saw the error “Expression.Error: Url scheme must be HTTPS” any suggestion?
thanks,
derebe

Dear Team,

I have faced this issue. Any help and advice? Thanks!

I registered free SSL on Tomcat.