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
- [OpenMRS] Non-aggregate in nature
- [OpenMRS] Hard to convert row based (map like) data to column like reports
- [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.

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.

- Filtering based on condition has to be done via MDX

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)

- Jasper editions comparison https://www.jaspersoft.com/editions

Aggregated database or de-normalized patient level 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 large in size (but even with estimates on high side support 10 million patients should not be a problem on commodity server)
- Aggregated database doesn't allow for doing union, intersections etc. because of missing patient level information. For example how many patients were diagnosed with TB and HIV.

Further Analysis

- Embedding Jasper vs. New front end on JasperServer vs. Using Jasper as it is

- How to handle custom attributes

- How to handle observations made on concept. or in other words when the data type is not known in advance.

Spikes

- How difficult would it be to write MDX filters in Saiku

Browser not supported