Issues with using OpenMRS database for reporting/analysis
- Too Current issues with reporting from the transactional database
- [OpenMRS] 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[OpenMRS] Non-aggregate in nature
- Hard [OpenMRS] Hard to convert row based (map like) data to column like reports
- Not [OpenMRS] Not suited for analysis and ad-hoc querying
- Cannot perform join of data across systems
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
...
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.large in size (but even with estimates on high side support 10 million patients should not be a problem on commodity server)
- 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