On This Page
University of Washington
The client was looking for a central data repository, hosted in country, where they could access all of their patient level and aggregated data for patient management at the facility level and for PEPFAR MER reporting. The solution required the design and implementation of an ODK-based system for data collection on a mobile device and the data available in a relational database. The data should be available for DATIM export as was as through visualizations in a comprehensive dashboard.
Expanding HIV Care and Treatment in Zimbabwe »
Project Balance developed an integrated patient and aggregated data capture and reporting system with CommCare (data capture on tablets), MS SQL (data warehouse) and Power BI (reporting and analytics). The mobile application comprised of case management patient forms for Initiation, Index Testing and Patient Tracking as well as 22 PEPFAR MER data capture forms. Access rights were appropriately applied so that users at specific facilities could only see their patients and district, provincial and program users could only view aggregated data on the tablet and reports.
Web Power BI reports are configured to filter for dimensions such as age, sex, facility, district, and province and display a set of indicator “cascades” as well and data cleaning reports. Mobile Power BI reports are available to healthcare staff at facilities to view patient line lists for improved operational efficiency.
- MS SQL database
- Power BI
- Custom .Net ETL (Extract, Transform, Load) application
CommCare provides limited support for data cleaning, analysis, and visualization. Project Balance identified a need to extract, transform and load data into an external relational database in an easy to query logical format that facilitates data quality control and analysis. The application is cross-platform (can be hosted on Windows or Linux) and uses the CommCare API to extract all updated data on a daily basis, transforms it to an appropriate format for analysis and cleaning and loads it into a central data warehouse to be analyzed and aggregated with external data for reporting.
One of the key objectives of this project was to be able to move away from paper-based MER tally sheets that then had to be transcribed into Excel workbooks and then finally moved into DATIM. The solution not only allowed for more efficient use of time, that data collected are of significantly higher quality. The forms have data quality checks and the Power BI reports quickly bring to light any mis-mapped data.
A second objective of this project was to provide data to clinicians at the facility level to reduce or eliminate referring back to patient data in large registry books. The arduous work of Index Tracking using registers necessitates flipping through pages of books and finding the client along with erasing incorrect or out of date data in a patient’s record. Clinicians have access to facility-focused, patient line lists that can be used for Index and Patient Tracking. Furthermore, facility, MOH and country-level program managers have access to dashboard data, available from nightly data loads.
Finally, capacity development of the Zimbabwe team was very important to ITECH. Over a period of a year post MVP deployment, the Project Balance team worked with the in-country team to successfully take over form building and testing, running and updating the ETL.