Background and strategic fit
One of the key opportunities for greater collaboration within the OpenMRS community is around reporting through ETL (Extract, Transform, and Load). This was underscored during discussions at OMRS19. It’s difficult to do modern analytics on OpenMRS data. The manual process of designing and developing ETL queries is time-consuming, complex, and is being tackled one implementation at a time.
We have prototyped ETL approaches with both Spark and FHIR models.
We are handling most of the PEPFAR MER indicators using the traditional ETL pipeline, but we don't have a modular approach that allows more aggregate level analytics. Want to extract data out of OMRS in a structure that is easier to query so that it is easier to query these specific indicators. Using 2-3 specific indicators can be a benchmark - need to ensure we cover both complex and simple ones.
Goals
- Pick one concrete problem to solve
- An output that is general, that most implementations can use. (Since inputs are so different by organization.)
- Build on top of a FHIR-based datawarehouse (assumes people are comfortable working with FHIR schemas, and comfortable writing complex SQL queries → Need to validate)
3-4 Concrete Problems/Use Cases people need us to tackle with intermediate data handling:
PEPFAR MER Aggregates Identified for ETL PoC:
TB_ART | Treatment | Proportion of HIV-positive new and relapsed TB cases on ART during TB treatment |
TB_PREV | Prevention | Proportion of ART patients who started on a standard course of TB Preventive Treatment (TPT) in the previous reporting period who completed therapy |
TB_STAT | Testing | Percentage of new and relapse TB cases with documented HIV status |
TX_CURR | Treatment | Number of adults and children currently receiving antiretroviral therapy (ART) |
TX_ML | Treatment | Number of ART patients (who were on ART at the beginning of the quarterly reporting period) and then had no clinical contact since their last expected contact |
TX_NEW | Treatment | Number of adults and children newly enrolled on antiretroviral therapy (ART) |
TX_PVLS | Viral Suppression | Percentage of ART patients with a suppressed viral load (VL) result (<1000 copies/ml) documented in the medical or laboratory records/laboratory information systems (LIS) within the past 12 months |
TX_RTT | Treatment | Number of ART patients with no clinical contact (or ARV drug pick-up) for greater than 28 days since their last expected contact who restarted ARVs within the reporting period |
TX_TB | Treatment | Proportion of ART patients screened for TB in the semiannual reporting period who start TB treatment. |
Patient-Level Indicators Required to generate these aggregates
- Enrollment Date - Previous Definition
- ARV First Regimen Start Date - Previous Definition
- VL Count Result - Previous Definition
- VL Count Date - Previous Definition
- Current and Previous ARV Meds - Previous Definition
- TB Treatment Start Date - Previous Definitions
- TB Treatment End Date - Previous Definitions
- TB Prevention Start Date - Previous Definitions
- TB Prevention End Date - Previous Definitions
- Supporting Variables: patient_id, encounter_id, prev_rtc_date, is_clinical_encounter, location_id
User Stories
- ...
Questions
- ...