Reporting - Custom and Metabase reports

Target release
EpicReporting - Custom
Document status
DRAFTED
PriorityHIGH
eLMIS StatusImplemented
OpenLMIS StatusImplemented with Tableau
PATH 
OpenLMIS Mary Jo Kochendorfer (Deactivated)
JSI Ashraf Islam (Unlicensed)

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

#TitleUser StoryLabelImportanceNotesJIRA Tickets
1

Custom reports

As an administrator I want to be able to define custom reports with parameters defined for report filtering Custom reportsMust have



2Custom reportsAs an administrator I want to be able to add users to custom reports roleCustom reportsMust have

3Custom reportsAs a user I want to be able to select custom reports from menu option, to be able to set filtering parameter and view output in HTML and be able to download the underlying data in CSV/Excel formatCustom reportsMust have

4Custom reportsAs a user/supervisor/admin I need to be able to get Annual consumption figuresCustom reportsMust have- TZ, ZnZ, ZMSee Figure 1: Annual Consumption by Facility
5Custom reportsAs a user/supervisor/admin I need to be able to get Consumption by product categoryCustom reportsMust have- TZ, ZnZ, ZMSee Figure 2: Consumption by Product Category
6Custom reportsAs a user/supervisor/admin I need to be able to get a List of users by role/typeCustom reportsMust have- TZ, ZnZ, ZMSee Figure 3: List of users by role/type
7Custom reportsAs a user/supervisor/admin I need to be able to get Top Expired ProductsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 4: Top Expired Products
8Custom reportsAs a user/supervisor/admin I need to be able to get a List of Facilities where a specific product is expiring in large numbersCustom reportsMust have- TZ, ZnZ, ZMSee Figure 5: List of Facilities where a specific product is expiring in large numbers
9Custom reportsAs a user/supervisor/admin I need to be able to get a Count of facilities reported, by facility typeCustom reportsMust have- TZ, ZnZ, ZMSee Figure 6: Count of facilities reported, by facility type
10Custom reportsAs a user/supervisor/admin I need to be able to get a list of Top 100 Ordered productsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 7: Consumption by Product Category
11Custom reportsAs a user/supervisor/admin I need to be able to get a List of Products being carried by different types of facilitiesCustom reportsMust have- TZ, ZnZ, ZMSee Figure 8: Products being carried by different types of facilities
12Custom reportsAs a user/supervisor/admin I need to be able to get a List of  R&R Reporting by types of facilitiesCustom reportsMust have- TZ, ZnZ, ZMSee Figure 9:  R&R Reporting by types of facilities
13Custom reportsAs a user/supervisor/admin I need to be able to get a List of Count of total R&Rs submitted by programsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 10: Count of total R&Rs submitted by programs
14Custom reportsAs a user/supervisor/admin I need to be able to get a List of Patients on ARV RegimensCustom reportsMust have- TZ, ZnZ, ZMSee Figure 11: Patients on ARV Regimens
15Custom reportsAs a user/supervisor/admin I need to be able to get a List of Patients on ARV Regimens, by FacilityCustom reportsMust have- TZ, ZnZ, ZMSee Figure 12: Patients on ARV Regimens, by Facility
16Custom reportsAs a user/supervisor/admin I need to be able to get a List of Regular Vs Emergency R&RsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 13: Regular Vs Emergency R&Rs
17Custom reportsAs a user/supervisor/admin I need to be able to get a List of R&Rs being initiated by facility, user and timestampCustom reportsMust have- TZ, ZnZ, ZMSee Figure 14: R&Rs being initiated by facility, user and timestamp
18Custom reportsAs a user/supervisor/admin I need to be able to get a List of users by role/typeCustom reportsMust have- TZ, ZnZ, ZMSee Figure 15: R&R by workflow status
19Custom reportsAs a user/supervisor/admin I need to be able to get a List of R&Rs by reporting groupsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 16: R&Rs by reporting groups
20Custom reportsAs a user/supervisor/admin I need to be able to get a List of R&R by program and reporting groupCustom reportsMust have- TZ, ZnZ, ZMSee Figure 17: R&R by program and reporting group
21Custom reportsAs a user/supervisor/admin I need to be able to get a List of Top 100 stocked out productsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 18: Top 100 stocked out products
22Custom reportsAs a user/supervisor/admin I need to be able to get a List of Losses and Adjustments by FacilityCustom reportsMust have- TZ, ZnZ, ZMSee Figure 19: Losses and Adjustments by Facility
23Custom reportsAs a user/supervisor/admin I need to be able to get a List of Current stock on hand by locationCustom reportsMust have- TZ, ZnZ, ZMSee Figure 20: Current stock on hand by location
24Custom reportsAs a user/supervisor/admin I need to be able to get a List of Top R&R ApproversCustom reportsMust have- TZ, ZnZ, ZMSee Figure 21: Top R&R Approvers
25Custom reportsAs a user/supervisor/admin I need to be able to get a List of Top R&R InitiatorsCustom reportsMust have- TZ, ZnZ, ZMSee Figure 22: Top R&R Initiators














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

DescriptionLink


Open Questions

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

#QuestionOutcomeStatus
1How important is it for users to be able to create cutom reports within OpenLMIS instead through a third party BI tool? Need to understand if OpenLMIS wants to put the resources behind building out ad hoc reporting or use other approaches.

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? (smile)
  • 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:
    • 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.