@solemabrothers, many sites have moved from this approach to an approach of flattening data within a secondary datastore and performing analytics there. Some have created bespoke SQL queries to do the flattening (can require some large & hard to maintain queries) and, more recently, people have been looking to existing open source tooling (e.g., debezium and Apache tools) to flatten data. There have been successes, but many are implementation-specific solutions.
PIH created petl as an extensible approach to defining flattening & reporting queries in SQL in a standard way that can be run by an engine.
The Analytics Engine Squad was created by groups trying to solve this problem in a way that could benefit others and be extended by the community. AMPATH and Google built openmrs-fhir-analytics to extract data into a FHIR schema and use Apache parquet (columnar datastore) to tackle the calculation of indicators.
@mseaton, @allan, @bashir, and myself have been trying to move our community reporting discussions in 2022 toward sharing approaches across four areas: CDC, ETL, data store, and analytics. For example, most approaches have found debezium to be the best tool for CDC.
We’d love to get folks from UgandaEMR, NigeriaMRS, KenyaEMR, PIH, AMPATH, Mekom, etc. (all of whom are trying to solve this problem in different ways) to share approaches & coordinate efforts on reporting & analytics to reduce unnecessary duplication of efforts, increase awareness & adoption of new tools (debezium, Apache tools, etc.), and (hopefully) begin discovering the parts of reporting that can benefit from shared tooling in place of bespoke solutions.