This abstract has been accepted at the 2024 DHIS2 Annual Conference
**Moving a Mountain: Google Sheets to DHIS2 Tracker with over 15,000 Program Indicators! **
In a perfect world, an optimal data collection platform would be adopted on Day #1 of project startup. Our team inherited the real-world challenge of strengthening the data infrastructure of a PEPFAR funded project which collected individual level data in Google Sheets. For more efficient DATIM reporting of PEPFAR MER indicators, our solution was to migrate data into DHIS2 Tracker where it would then be collected and reported on exclusively. This shift to DHIS2 Tracker wallowed for critical improvements such as centralizing data management, implementing data quality standards, and sharing automated data summaries including dashboard visualizations. The process of shifting individual-level data collection from the Google Sheets platform to DHIS2 Tracker—which is then manipulated into aggregate program indicator form—midway through the project presented unexpected challenges. In the end, this work required a monumental effort. A key obstacle to a DHIS2 transition was the collection of client data across multiple geographical regions, with clients often receiving services across multiple regions over time. These data were initially recorded on 20 unique Google Sheets forms representing 9-96 fields and 34-40,000 records per form. Google Sheets had limited functionality to prevent duplicates and noncompliance with data entry and data revision procedures. Emulating steps in the Google Sheets environment could not always be replicated in the DHIS Tracker environment resulting in mismatched calculated values. Calculating reporting indicators coming from different forms and different regions required us to force the assignment of calculated values to specific regions. This complexity also meant that we had to use enrollment-based program indicators in reporting rather than simpler event-based reporting. This was all unexplored territory for our team as well as much of the DHIS2 developer community. Ultimately, we “moved the mountain” with the creation and population of over 15,000 program indicators in the migration to DHIS2. The Program Dataset Connector (PDAC) was used to automate this process due to its ability to add disaggregation filters to program indicators. With 16 selected MER indicators, PDAC was able to save valuable time by the disaggregation of age, gender, regions, and key population types. API pulls were used to save the disaggregated indicators in the DHIS2 aggregate database which then allowed us to set up dashboards and output tables. This presentation will describe our initial challenges, our achievements, and lessons learned from these efforts so that others in the DHIS2 community may benefit from our experiences.
Primary Author: Rick Mitchell
Keywords:
Tracker PDAC Migration Data Quality Google Sheets Program Indicators
1 Like