Explaining the MVP Prototype

Project Origin

Late in 2011, I approached the OpenMRS team, offering community contributions toward the use of Pentaho tooling to fill their need for analytic reporting against the OpenMRS data. The team introduced me to Andy Kanter, an assistant professor at Columbia University who manages the eHealth aspects of a program called the Millennium Villages Project (further referenced as MVP).  The eHealth system is called the Millennium Villages Global Network (MVG-Net) which mostly consists of an SMS-based Community Health Worker (CHW) information system known as ChildCount+ linked to the OpenMRS database in each site. MVP has a team of Monitoring and Evaluation experts who hope to use the data warehouse to combine data from 14 different MVP sites in Africa. Columbia University has several implementations of OpenMRS servicing the MVP project; it is from a sample of de-identified MVP OpenMRS data that I have prototyped two analytic models, using the Pentaho Business Intelligence Suite, Community Edition.

Source Resources

The source of requirements for the project came from several resources supplied to me by Columbia University. These included

1. A set of de-identified data from the MVP implementation. This data came in the form of an OpenMRS database schema populated with nearly 335,000 encounters and approximately 58,000 person records from a single MVP site;
2. A set of queries that Columbia currently uses to manipulate the OpenMRS data to get the ChildCount organization analytics they require. These queries will be referred to as the MVP Pivot Table queries;
3. A set of queries that retrieve data from the result of the previous queries that match codes from the ChildCount organization data;
4. These matches then map to a series of spreadsheets that I received, that have the detailed definition of the ChildCount+ codes, the required indicators that Columbia is looking for in their analytics, and definitions of calculations (percentages) that they require as well.

With this information, I was able to build prototype ETL transformations using Pentaho Data Integration that would transform the OpenMRS data into a structure that is conducive for use with the Pentaho Analytics engine, Mondrian. The following sections detail this work, the underlying data structures, the nuances of working with the OpenMRS data and finally my conclusions regarding the effort and next steps.