Reporting - Custom and Metabase reports

Goals/Scope

Ability to make ad-hoc queries against the database, define custom reports and add add such reports to application menu system without requiring programmer involvement or without requiring a software release (see notes from 4/12/2018 below). This feature is meant to support ad hoc needs that arise time to time and can not wait for next development/deployment cycle.

The scope will also cover the metabase reports. Prior to starting on this work, we will want to confirm with a BA on frequency of using these reports to support in the prioritization.

Status in eLMIS: Implemented.

Status in OpenLMIS: Implemented with Tableau.

Priority: High priority for Tanzania, Zanzibar, Zambia

Background

Users have many ad hoc data needs for upcoming meetings/presentations and information request from MoH or funder. A flexible mechanism is needed for a trained user to run ad hoc reports.

Assumptions

Basic SQL query training is given to the admin/super user. eLMIS database schema is available with the user. Users knows how to assign roles and which specific role to assign to others who wants to be able to run these ad hoc reports   

User Stories

Diagrams

Figure 1: Annual Consumption by Facility

Figure 2: Consumption by Product Category

Figure 3: List of users by role/type

Figure 4: Top Expired Products

Figure 5: List of Facilities where a specific product is expiring in large numbers

Figures 6: Count of facilities reported, by facility type

Figure 7: Top 100 Ordered products

Figure 8: Products being carried by different types of facilities

Figure 9: R&R Reporting by types of facilities

Figure 10: Count of total R&Rs submitted by programs

Figure 11: Patients on ARV Regimens

Figure 12: Patients on ARV Regimens, by Facility

Figure 13: Regular Vs Emergency R&Rs

Figure 14: R&Rs being initiated by facility, user and timestamp

Figure 15: R&R by workflow status

Figure 16: R&Rs by reporting groups

Figure 17: R&R by program and reporting group

Figure 18: Top 100 stocked out products

Figure 19: Losses and Adjustments by Facility

Figure 20: Current stock on hand by location

Figure 21: Top R&R Approvers

Figure 22: Top R&R Initiators

Dependencies

Open Questions

Below is a list of questions to be addressed as a result of this requirements document:

Out of Scope

Gap Estimation Notes 4/12/2018

• Attendees: Mary Jo, Josh, Ashraf, Muhammad, Peter, Craig, Brandon, Elias
• See the "Custom Report" section of the PDF provided by Muhammad and Ashraf.
• Question: How many people have ability to add custom reports?
  • It is a handful of people in Tanzania, for example (Alfred and a few of the folks there).
• Discussion: Is it fair to say "without programmer involvement"?
  • It does require SQL skills, which is basically a programmer. You have to understand SQL and database and performance.
  • But the benefit of this approach is it does not have to wait for a release cycle.
• How would we do this in the new reporting stack?
  • The new reporting stack would have an ad-hoc report ability so users could make new reports even without writing SQL. 
  • Users could still use SQL if they wanted to.
  • Josh: In the new stack, if the data they need is not already in Druid, will they have to do things in Druid to somehow get the additional data they need? It takes someone like Hassan to do ingestion work.
  • Ashraf: In Guineau or Cote d'Ivoire experience, there is no on-going support mechanism there. People needed this flexible mechanism for custom reports. We taught the folks in those implementations how to do this.
• What about roles/permissions in the new reporting stack?
• Question: Do you verify that the SQL is valid before running?
  • Elias: We pass it through a filter. It only allows SELECT statements. It prevents SQL injection attacks.
    • Does this check against encoded (e.g. b64) statements? 
• Question: Does everything directly hit the database?
  • Yes.
• Options for reports in eLMIS reports: 
  1. Export the eLMIS custom reports (grab their SQL) and
  2. Embed Superset or some of it's UI into OpenLMIS v3 UI?
     1. Could use iFrames for embed
     2. Could change the header of Superset to make it look like LMIS
  3. Entire data analysis platform.
     • Josh: We have a constraint that no service can directly hit the database. Why? For upgradeability. We do not version the database schema. The semantic versioning is on the API, not the database. We are writing API calls for our reports, not SQL queries. There is a pattern we can follow: data pumps. If we define a contract within the service–the service owns the data pump, that is fine. The semantic versioning of the data pump goes along with the service itself. But this architectural paradigm makes the "custom reports" feature moot.
• Discussion of data pump architecture:
  • A data pump is the ability to get data out of a micro-service where the format is defined by the service that owns the data.
    • It publishes the data, EG providing the data as an Atom feed, or a Web-Sub.
    • It is semantically versioned.
    • It's not that much different from an API.
    • The details are pub-sub, how do you notify others when there is new data; those details impact performance.
  • After the data is pumped out, it is out of the service's control. The other components can use that data however they need.
    • Another service can listen or subscribe to that data and use it how they need.
• Is there a down-side or concern with this data pump architecture?
  • Operational Support - server purchases. You will need 2 servers.
  • Craig: People on the ground will want access to their own data.
• Josh: We already decided this for version 3 because of lessons learned from prior versions.
• Peter: So the "custom report" feature with the ability to paste in SQL queries can still be possible in the new reporting stack using the data pump approach.
  • Ashraf: We are basically building the data warehouse. That could be relational or not relational. The SQL queries for the "custom reports" feature can be done against that data warehouse. In a cost-benefit analysis, does the data warehousing approach pay off? Is it worth the problem it solves?
  • Peter: Using a data warehouse is the only approach. It is more complex, but with micro-services it is the only approach we have.
  • Ashraf: What prevents us from directly connecting to a SQL database to pull out the data? Josh: As the architect, we would not recommend that. But nothing prevents an individual implementation from doing that.
  • Peter: Actually it would be prevented. When the next version of the micro-service system comes out, it could break the report when the schema changes.
  • Brandon: We should discourage the implementations from writing direct SQL queries, because it will be more brittle and it will be harder to query across micro-services than it was in OpenLMIS version 3.
• Question (Craig): I have a mental block on how a change in a micro-service requires us to change the reporting stack?
  • The data pumps are versioned. 
• Craig: What about Pick Lists and PDF print-outs?
  • Josh: We will need to see whether it is part of the user workflow or whether it should be part of the data warehouse.
  • Craig: Should we assume there is a Jasper reporting as part of each micro-service?
  • Josh: Currently that is the case. They are not for heavy reporting lifting. The Jasper reports break down with heavy reporting/dashboard requirements. There is also a "reporting service" but it needs more work to run off of a database that is powered by data pumps.
• Mary Jo: We will need to do more to make sure we understand and articulate and explain this data warehouse architecture and strategy to implementations and partners.