Issues with using OpenMRS database for reporting/analysis
- Too normalised, leading to too many joins, hard to write performant queries, having to tune the tables for reporting purpose may not be ideal for online system
- Non-aggregate in nature
- Hard to convert row based (map like) data to column like reports
- Not suited for analysis and ad-hoc querying
Tool Options
Kibana (with ES)
. Scales horizontally to multiple servers (but this is not necessarily a must have because most hospital’s data can fit on a single hard disk)
+ Simple to use
- Is in its infancy feature wise, for example doesn’t even support nested objects, lists. Probably skunkworks (my assessment). Project not very active https://github.com/elasticsearch/kibana.
. Filtering based on condition has to be done via MDX
Jasper
. JasperServer is not very pretty, but embedded jasper provides lot more flexibility
+ Very mature and has all features we need for reporting
. Supports multiple databases (we don’t really have a need for it though)
- Has only paid version for analysis
Saiku (analytics-lab)
+ Very active project
+ Best open source analytics solution for a relational database (star schema)
. Not meant for reporting
+ Provides stats
DHIS 2
- Too generic for us.
- Not designed for our kind of use case
(let me know if you want to know more details)
Star Schema
Dimensions (not a complete list)
Location
Date (+ Week, Month, Year)
Provider
Facts (also called measures; not a complete list)
Patients Diagnosed
Drug Orders Given
Accessions
Lab Results
Reference
- Simple introduction => http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx
- http://www.amazon.com/Data-Warehouse-Toolkit-Definitive-Dimensional/dp/1118530802/ref=sr_1_1?s=books&ie=UTF8&qid=1405491662&sr=1-1 (I have this book, if you want to borrow)
Aggregated database or de-normalized transactional database?
(applies only if we go with relational database)
- Aggregated database wouldn’t allow one to drill down to a patient
- Non aggregated star schema with patient information are unnecessarily large and difficult to model. They can be cumbersome when the data is sourced from multiple systems (e.g. OpenERP, OpenMRS). In fact star schema was never designed for it.
- Drill down to patient level is not analysis or reporting. Such concerns arguably should be handled by main application
- Aggregated database doesn't allow for doing union, intersections because of missing patient level information