Strengthening DHIS2 data visualizations: DHIS2-to-Power BI connector

This article was written by Rodrigo Gramajo and originally published on the DHIS2 Community site.
The DHIS2-to-PowerBI Connector was funding through Digital Square’s Notice B.

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

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!