Target release
Epic	OLMIS-3760 - Getting issue details... STATUS
Document status	IN PROGRESS
Document owner	Mary Jo Kochendorfer (Deactivated) and Clay Crosby (Unlicensed)
Technical Lead	Josh Zamor (Deactivated)

Table of Contents

Goals/Scope

The reporting and analytics vision is to support OpenLMIS users with both routine reporting and ad-hoc analysis. Users need a way to extract and visualize data. This specification is not defining the exact list of reports needed but rather what types of reports (map, offline, etc.) and functionality is desired. Please see the Scope Out section for a clear explanation of what pieces this spec does not include like alerts (via email/sms) and integrations to systems like DHIS2.

This wiki page is an attempt to define the reporting and analysis needs for OpenLMIS users based on the following generalized user personas identified by the community.

Background

In an attempt to gain buy-in and agreement by the OpenLMIS community on what the needs, requirements and definitions are for reporting and analytics within OpenLMIS. Do not focus on the technical solution as there may be multiple approaches to meeting the desired behaviors. Focus of this spec is to outline what the end users will need and want. This spec will support the decision making process for implementing reporting and analytics within OpenLMIS.

To support this conversation, we define reporting, dashboards and analytics in the following ways:

Reports, routine reporting and sometimes called built-in reporting contain detailed data in a tabular format and typically display numbers and text only, but they can use visualizations to highlight key data. Some key characteristics of a report include :

It presents numbers and text in a table.
It can contain visualizations (like a map, chart, graph) but only used to highlight findings in the data.
It is optimized for printing and exporting to a digital document format such as CSV, Word or PDF.
It is geared towards people who prefer to read data, for example, lawyers, who would rather read text over interpreting visualizations, and accountants, who are comfortable working with raw numbers.
Rendered from a defined query/template.
Does not require high technical capacity to run or access the report
Usually viewed or run on a routine bases (more than once a year)
Usually drives users to an action

Assumptions:

Designer is an engineer.

Routine reporting applies typically to more than one implementation.

Dashboards are a data visualization tool that displays the current status of metrics and key performance indicators (KPIs) for OpenLMIS end users. Dashboards consolidate and arrange numbers, metrics and sometimes performance scorecards on a single screen. Some key characteristics of a dashboard:

All the visualizations fit on a single computer screen — scrolling to see more violates the definition of a dashboard.
It shows the most important performance indicators / performance measures to be monitored.
Interactivity such as filtering and drill-down can be used in a dashboard; however, those types of actions should not be required to see which performance indicators are under performing.
It is not designed exclusively for high-level decision makers or but rather should be used by the general workforce as effective dashboards are easy to understand and use.
The displayed data automatically updated without any assistance from the user. The frequency of the update will vary by organization and by purpose. The most effective dashboards have data updated at least on a daily basis.
Does not require high technical capacity to access

Assumptions:

Designer is an engineer.

A dashboard applies typically to more than one implementation.

Analytics and Ad-hoc analysis leverages tools that offer the ability to select ad-hoc date ranges, pick different products, or drill down to more detailed data to be dashboards and my dynamic than reports. Some key characteristics of data analytics include :

user can define desired data element combinations (via a programming language or wizard).
Not routine and the combination of data elements is run infrequently (once year).
It fits on one screen, but there may be scroll bars for tables with too many rows or charts with too many data points.
It is highly interactive and usually provides functionality like filtering and drill downs.
It is primarily used to find correlations, trends, outliers (anomalies), patterns, and business conditions in data.
The data used in a ad-hoc analysis is generally historical data. However, there are some cases where real-time data is analyzed.
An outcome of ad-hoc analysis might be to define/identify performance indicators for use in reporting and/or dashboards.
It is typically relied on by technically savvy users like data analysts and researchers with knowledge of and experience with report programming languages.

Assumptions:

Designer needs to be technically inclined to their BI&A tool of choice (e.g. Excel, DHIS2, Tableau, etc). Per-seat licensing for report designer and consumer may apply depending on tool choice.

Typically more implementation specific, less re-use across implementations.

Above definitions were taken and adapted from a D ashboardInsights article.

User Personas

User	Tech aptitude	Scope of supervision	Kind of Reports	Access (how/when)	Frequency**	Report Types
Store Manager Facility in-charge/administrator	Low	Only responsible for one geographic facility but may have multiple storerooms	Routine reporting	Application or email ( ? )	Monthly or more (if available)	Offline reports (PDF, Excel) Printable reports dashboard
Intermediate Store Manager (could be district, regional, province) District/regional/provincial health officer, program coordinators, pharmacist	Low Can navigate excel (relies on an existing format)	Supervises a subset of facilities or specific zone. Facilities must be mapped to zones.	Routine reporting	Email and Application Can be delayed (days)	Monthly or weekly depending on replenishment schedules Daily around due dates for requisitions.	offline reports Tabulated/paginated printable reports dashboards maps (reporting rate, stock out, etc.) Filterable results Serach-able results Aggregate reports with drill down to granular items
Central Personnel or Central Program personnel	Medium Strong Excel skills (can do filters, charts and graphs) No programming experience knows DHIS2	National supervision of all geographic facilities OR national supervision of one program.	Routine Some ad-hoc (where VAN is rolled out)	Application Immediate	Monthly (depending on replenishment schedules) bi-annually or annually (for forecasting/planning)	ad hoc donor visualizations maps (stock outs, CCE performance) tabulated/paginated reports Excel export of results filterable results Serach-able results Aggregate reports with drill down to granular items printable reports offline reports (potentially for program leads)
Technical Administrator (can be from the MIS department of the MOH; sometimes there is both a Technical Administrator and a Managerial Administrator)	Medium-High Database and Programming experience Have some knowledge of the data model May have some knowledge of other reporting tools (Tableau, etc)	Create new slices and dashboards, templates and conduct ad-hoc reporting.	Ad-hoc requests from MOH, stakeholders ("I want to see...")	Upon request		ad hoc requests from MOH, stakeholders establish new reports and create visualizations (including types above such as maps, printable, etc) may extract data from OpenLMIS into other reporting tools Open Question: How much of this is built-in functionality in OpenLMIS versus in a connected tool?
Implementer (most likely a contractor/vendor supporting the system)	High Probably knows Java and SQL Probably can configure DHIS2	May create new slices and dashboards, or customize and alter existing slices and dashboards based on requests from stakeholders	Ad-hoc Routine (help running or troubleshooting/ customizing routine reports)	Upon request (either at time of implementation or as a support request)		(Would use and customize all of the above)
Stakeholders outside OpenLMIS	Varies	May represent directorates (public health, any interest programs), stakeholders, partners and donors like UNICEF, GAVI, etc.	Ad-hoc	Reports may be shared with these users outside OpenLMIS as needed, whether on paper or by forwarding an email containing a PDF		PDF

**If the implementer is using stock management, frequency of reports would increase.

Assumptions

If the implementer is using stock management, frequency of reports would increase with transaction data.

Project Status - Phase 1

Phase 1 Scope: DISC Indicators 1 and 2 (Full Stock Availability and Closed Vial Wastage) - Last Update 1/16

AMI of base infrastructure setup and/or deployment scripts created
- Write Ansible scripts to install and configure a running instance
- Set up NiFi for data ingestion
- Set up Kafka for real-time streaming & joining
- Set up s3 (HDFS) for historical indexing
- Set up Druid for storage and aggregations
- Set up Superset for visualizations
Load mock data through NiFi, index in s3, and into Druid
- In progress - Ona team will generate bulk demo data via Mockaroo. Clay meeting with Chongsun on March 5 to review Mockaroo, and then Ona will use Mockaroo to generate records
Working prototype of end-to-end system (OpenLMIS to dashboard)
- NiFi pull CSV/JSON data from a static hosted resource
- NiFi pulls data directly from OpenLMIS from 2-3 API endpoints, flows through Kafka, s3, Druid, to Superset for visualization
  - Build complete. Requires testing
- Write query for DISC 1 - Full Stock Availability in Superset
  - In progress - queries drafted
- Write query for DISC 2 - Closed Vial Wastage in Superset
  - In progress - queries drafted
Source code with open source license for reporting tooling and specific reports created
Documentation (developer documentation in the wiki or ReadTheDocs) to allow OpenLMIS Community to learn how to develop additional indicators and reports subsequent to the engagement
Reusable components that the OpenLMIS Community can use to build additional reports subsequent to the engagement

Data Model

Stock cards - each record / "row" in the database is a stock card line item, and will have the following data elements / fields / "columns" ("row" and "column" are used to show analogous terms to relational databases, whose structure is more familiar, although in this particular database we store the data as .json records)

Stock card line item id ("id")
Stock card id
Occurred date
Orderable name
Orderable id
Orderable code
Commodity type
Trade item
Lot #
Program name
Program id
Program status
Facility supported programs
Quantity
Stock on hand
Reason type
Reason category
Reason name
Source
Facility id
Facility name
Facility type
District
Region
Region code - ISO code
Country
Ideal stock amount - value is added for all orderables within the corresponding commodity type + facility pair

Prioritized List of DISC Indicators & Business Logic for Each

Priority	Indicator Name	Numerator	Denominator	Group Bys	Notes
1	Full Stock Availability	Count of facilities where, for all line items within reporting period, stock on hand is always > 0.	Count of facilities	Facility, district, province, country, orderable, commodity type, trade item	Defined as absence of stockouts between re-supply periods
2	Closed Vial Wastage	Quantity of vaccines that were lost due to Closed vial wastage. "Closed vial wastage" is defined as: Reason type = "Debit" Reason category = "Adjustment" Reason name intersects: Expired Damage Lost Stolen Cold chain failure Note: these reason names may vary based upon your implementation's configuration	Total vaccine stock under management during reporting period "Stock under management" is defined as the sum of stock on hand at the start of the reporting period plus the sum of the quantity for all receipts Receipts are defined in one of the following three ways: Source <> null Reason name = "Transfer In" Reason type = "Credit" and reason category = "Transfer"	Facility, district, province, country, orderable, commodity type, trade item
3	Stocked According to Plan	Yes/No indicator where "Yes" means the sum of the stock on hand for all orderables within a commodity type is between the Maximum stock level and Minimum stock level for that facility. Maximum stock level = the ideal stock amount for that Commodity Type + Facility Minimum stock level = 25% of the ideal stock amount for that Commodity Type + Facility	N/A - this metric is a count	Facility, district, province, country, commodity type	Because this indicator relies on Ideal Stock Amount, which is only as granular as Commodity Type, this indicator should not be viewed at the orderable-level.
4	Forecast Demand Ratio	Quantity of doses consumed "Consumption" is defined as having reason name = "Transfer Out"	# of doses forecasted "Forecasted" = the ideal stock amount	Facility, district, province, country, commodity type	Because this indicator relies on Ideal Stock Amount, which is only as granular as Commodity Type, this indicator should not be viewed at the orderable-level.
5	On-Time and In-Full Deliveries
6	Functional Status of Cold Chain Equipment				FYI Vidya Sampath moved this down since we will have a screen to see which CCE is functioning and non-functioning
7	Temperature Alarm Rates

Additional detail can be found in the initial design document: DISC Indicators.PDF

Implementation Considerations

The following items will need to be reviewed and potentially customized for your particular implementation's configuration

Product model hierarchy
- Based upon the Commodity Types your instance uses, you may need to ensure the data model includes the proper tiers for your instance.
Geo Zone hierarchy
- By default, we used the following Geo Zone hierarchy, but you may need to adjust this based upon the hierarchy in place at your instance:
  - Facility
  - District
  - Province / region
  - Country
Closed Vial Wastage
- You should review the definition of line items that count as "closed vial wastage" ("numerator" column for Closed Vial Wastage in business logic table above) to ensure this definition comprehensively applies at your instance.
- You should review the definition of "receipts" ("denominator" column for Closed Vial Wastage in business logic table above) to ensure this definition comprehensively applies at your instance.
Consumption
- You should review the definition of "consumption" ("numerator' column for Forecasted Demand Ratio in business logic table above) to ensure this definition comprehensively applies at your instance.

User Stories

	Title	User Story	Label	Importance	Notes
1	Infastructure	As an Implementer, I need a way to create reporting indicators based off of OpenLMIS data so that my users can easily see trends and important KPIs.	Vaccines	Must Have	OLMIS-3660 - Getting issue details... STATUS OLMIS-3729 - Getting issue details... STATUS OLMIS-3671 - Getting issue details... STATUS
2	Report writing	As a Technical Administrator, I need a way to create ad-hoc reports based on OpenLMIS data already mapped to Superset so that I can create new slices (charts, graphs, etc) based feedback from users. The reporting platform comes with a predefined data model based on stock card and requisition data (future), and this data model was architected with these DISC indicators in mind. Note: if your deployment has additional indicators you would like added that require data elements not included in the current data model, please contact your OpenLMIS representative.	Vaccines	Must Have	OLMIS-3767 - Getting issue details... STATUS
3	Full Stock Availability	As an Intermediate Store Manager, I need to see the full stock availability for my supervised facilities so that I can effectively reorder stock to reduce stock outs at my supervised facilities.	Vaccines	Must Have	OLMIS-3603 - Getting issue details... STATUS OLMIS-3748 - Getting issue details... STATUS OLMIS-3895 - Getting issue details... STATUS
4	Closed Vial Wastage	As an Intermediate Store Manager, I need to see the closed vial wastage for my supervised facilities so that I can identify high levels of wastage to figure out where focus on improving the vaccine ordering, distribution and storeroom management.	Vaccines	Must Have	OLMIS-3595 - Getting issue details... STATUS OLMIS-3810 - Getting issue details... STATUS OLMIS-3749 - Getting issue details... STATUS
5	Stocked According to Plan	As an Intermediate Store Manager, I need to see which of my supervised facilities are stocked according to plan so that I can effectively reorder stock to reduce stock outs at my supervised facilities.	Vaccines	Must have	OLMIS-4139 - Getting issue details... STATUS OLMIS-4093 - Getting issue details... STATUS
6	Forecasted Demand Ratio	As an Intermediate Store Manager, I need to see the forecasted demand ratio for my supervised facilities so that I can effectively validate and improve forecasting practices and assumptions (e.g. target population, coverage, wastage) in order to increase forecasting accuracy.	Vaccines	Must have	OLMIS-4130 - Getting issue details... STATUS OLMIS-4094 - Getting issue details... STATUS

Diagrams

Dependencies

Description	Link
Create user personas and outline objectives	OLMIS-2077 - Getting issue details... STATUS
Research of technical approach/options	OLMIS-2078 - Getting issue details... STATUS

Example reports

Community members are encouraged to share key reports and examples for the team to review and keep in mind as moving forward. Assuming the perspective of a national-level program manager who wants visibility into the activity of their program by region/district, but who would also like the option to get granular-level visibility into facility-by-facility data as well.

Example reports made for ESMS

OpenLMIS ESMS Report examples.docx

1 - Stockout days

Ideally would be able to view on a map geographically
Could show avg. # of stockout days in a district – aggregated data from all facilities in that district to give a picture of the overall stockout rate for that district
User could click on the facility list and see the specific facilities reporting stockouts

2- Stock levels/Consumption (these are two different indicators)

Nice to view on a map geographically
Shows SOH at beginning of period and SOH at the end of period for each facility
Graph could show min/max levels to indicate that stock delivery is adequate to bring stock levels up to max, if they’re ordering too much, or too little
Can show that a facility stock level is below the min or near stocking out
Should show value of commodity, even if the commodity is provided to the consumer for free, in order to reinforce idea that stocks have real value

3 -Timeliness and completeness of reporting

Could show aggregate for the district of whether district is reporting on time
Similar to Stockout Report, user could click on the facility list and see the specific facilities which were not reporting on time

4 - On Time and full deliveries

Could show aggregate data for a district/region for on time and complete deliveries that month.
Similar to Stockout Report, user could click on the facility list and see the specific facilities which reported delayed or incomplete deliveries

5 - Inventory aging

Show items near to expiry (configurable date range, since length of the pipeline will determine how critical a given range is)
Show items with VVM stage 2 status

6 - Expiry/wastage/Loss (based on reason codes)

Show items that expired/damaged/lost and require disposal (units and value)
Show loss rate; total lost / total usable stock used during period as percent (units and value)

7 - Stocked according to plan (traffic light or similar indicator)--this is similar to but distinct from #2

Show stocks that are within planned parameters (min/max or EOP/max, with config tolerance thresholds) for all commodities managed
Show stocks that are below threshold for min/EOP
Show stocks that are above threshold for max

8 - Forecast accuracy

Show the ratio of actual consumption by product during a particular period compared to the consumption forecasted for the same period (central level dashboard)

9 - Emergency order rate

Show number of unplanned orders/deliveries in period compared to planned deliveries

10 - Performance Score Card

Compare HF/district/region against all peers for period and over time with a balanced scorecard comprising critical KPIs (configurable)
This is both for self-assessment (How are we doing?) and for supervision/oversight (how are they doing?)

Some other ideas we tossed around but didn’t discuss as thoroughly were:

Financial Reports

A program manager would use this report to measure how much money a facility should have (relating to cost recovery)

A program manager could also see a graph depicting the total costs of requisitions for their area (get a clearer picture of where their money is going)

Could see whether facility is in the red/black based on cost recovery analysis

Should show value of inventory, value of good sold/issued/dispensed, value of goods lost

Emergency Orders

Good to show on a map - district level making emergency orders to provincial level, potential risk of stockouts.

Out of Scope

Please note this spec does not include in application alerts (like an alert to an approver that a requisitions is ready for approval) which are addressed within each functional area (like Requisitions) based on business logic.
This spec does not include the scope of exporting data into another system like DHIS2. If an implementation of OpenLMIS decides to use another application for all reporting needs, then the following feature set would not be used by that implementer.

Open Questions / Assumptions

Requirements document

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

#	Question	Status
1	How much money does a MOH want to spend on resources to support creating report templates? We want to figure out guardrails on what types of budgets MOHs have to support the creation of reports. This will help us assess reporting tool options. For example, if one solution only allows for highly-specialized resources which cost a lot, we may not go with it. FYI, Josh Zamor (Deactivated).	In progress.
2	What type of skill sets can we expect implementers to have to create the report templates for routine reporting?	In progress.
3	Do users at the facility level want to be able to define their own reports? If so, what level of complexity would they be able to work with. Alfred Mchau (Deactivated) could share some of his learnings as he digs into what the University of Dar Es Salaam did within eLMIS.
4	What is the approach if we have all the data within our systems? What is the approach for when there is an aggregate HMIS (DHIS2) in place which we pull indicators from for resupply?

Closed Vial Wastage denominator - reporting on the stock on hand at the start of the reporting period.

Consider the following data set where each row is a stock card line item. Note "quantity" is null for the first instances of each orderable id because stock on hand is the field we're anchoring this portion of the metric off of (and so quantity is irrelevant for our purposes here):

date	orderable id	quantity	stock on hand
12/31/2017	1		40
1/1/2018	2		50
1/2/2018	1	60	100
1/3/2018	2	30	80

For a reporting period that spans the month of January 2018 (1/1/2018 - 1/31/2018), the stock on hand at the start of the reporting period would be 40 for orderable 1 and 50 for orderable 2. However, Druid and Superset cannot find data for rows with dates that are BEFORE the reporting period start date (1/1/2018). So, the stock card line item for orderable 1 which represents the stock on hand at the start of the reporting period would be excluded from the query as it has a date of 12/31/2017. There has been no stock activity on orderable 1 in facility 123. Note this date could be any date before the reporting period, but for the sake of simplicity we're using the day just before the reporting period.

To ensure that the Closed Vial Wastage denominator is accurately capturing the stock on hand for orderables whose most recent activity predates the reporting period start date, we will write a script to infer additional rows in Druid. These inferred rows would be duplicates of the most recent "real" row, with quantity set to 0 and reasons set to NULL to avoid erroneously counting the inferred rows when summing the quantity column or by counting rows with specific reason types for other indicators. These rows would be inferred for orderables attached to active programs during the dates for which the programs are active. I.e. if a program is active from 1/1/2017 to 6/1/2018, we would start inferring rows on 1/2/2017 (because there would need to be an anchor "true" row that we would infer from) and we would stop inferring rows on 6/1/2018. Because we want to support a daily granularity with this metric, i.e. users should be able to adjust the start date to 1/2/2018, we would do this inference each day.

So, extending the example above, the table would look like this for dates 12/31/2017 - 1/3/2018, where the italicized/emboldened rows are inferred rows.

date	orderable id	quantity	stock on hand
12/31/2017	1		40
*1/1/2018*	1	0	40
1/1/2018	2		50
1/2/2018	1	60	100
*1/2/2018*	2	0	50
*1/3/2018*	1	0	90
1/3/2018	2	30	80

Druid would be able to pull the value of the stock on hand column for each orderable on the reporting period start date because each orderable has a row for 1/1/2018.