This page documents the notes taking during the in person meeting.

Attendees:

Day 1

Introductions

Introduced each attendee:

Craig (Ona) based in Seattle, Health Technical Lead
Clay (Ona) based in Nairobi, Reporting lead
Wendy (JSI Zambia) is based in Zambia and is the MIS lead who runs all JSI activities in Zambia across multiple projects.
Ashraf (JSI HQ) is based in Arlington, Virginia. Noted that JSI is also working on ERP work.
Josh (VR) is the architect, working with interoperability, standards and integrations
Wes (VR) Technical Program Manager located in New York state and will soon move to Nairobi
Brandon (VR) Director of Information Systems Group
Mary Jo (VR) Product owner of OpenLMIS and Grant Administrator
Muhammad (JSI HQ) is based in Toronto and worked on many projects, mostly logistics management. Wrote the majority of reports, including database structures.

Overview of Requirements

Goals of these meetings

Alignment and knowledge transfer
Define scope and plan
Documentation

What we know

Industry indicator research
eLMIS reporting review
OpenLMIS reporting project 1
SELV+
Organization hierarchy & user tiers

Path forward

Discovery (now)
Solution architecture
Indicator list
SRS
Build

Intro to the reporting stack

Discussion

If something is missing from eLMIS, can we live with it and not add additional data points to OpenLMIS v3.
We need to understand things that exist in the current state and decide if we will be building them in the future state. What is the process for this? How can governance and country teams be involved?
- ACTION ITEM define governance criteria and end user integration with these changing requirements
We have order and item fill rate in eLMIS. How do we get this in? It should be included in the Orders and Rquisitions On-Time and In-Full
Can we name these reports built on top of the existing logistics process?
- There are SOP documents, training, etc.
- ACTION ITEM Get the SOPs from eLMIS country implementations (Wendy can do this)
ACTION ITEM Attach profiles to each report and why they use the report
Everything needs to be configurable and contextualized.
Orders and Requisitions
- We also track the time lag between the convert to order → central warehouse ERP → fulfillment
  - This helps people understand the time lag at each stage. Facilities can see that they are stocked out and they aren't getting fulfilled
  - Could this be considered a specific example of a more general need to know the lag between changes in state for the requisition/order within the system?
Cold Chain
- This is more broadly including asset management. This includes Viral Load equipment.
- We need to identify exactly the time lag when things were unavailable.
We need to consider the connectivity from the users perspective.
- What is the role of internet vs system functionality?
- What needs to be displayed while offline
- Which reports need to come from the Enterprise Data Warehouse and which need to be operational from the transactional database?
- Make sure the loading icon is there every time a report is run
Drill-down functionality is critical and Excel
- When we export to excel, every data element is available so they can further manipulate the information.
- Is there a feedback loop on what is done in Excel?
  - There is a monthly change control board and biweekly meeting where the team goes through new enhancements, showcases of what has been done, what has been tested and it walks through the approval process. PSM leads that meeting. There are times when the customer doesn't know what they want.
  - Excel outputs are much better if everything is already tabular.
- We would like input on both what is currently doing and what is critical for success in the future. (i.e. competitor software)
Our hypothesis is replicate the manual process and identify areas where we are growing.
- We still have to replicate the manual process. This is critical. They want the same thing everyday as they currently have it because it takes a lot of behavior change.
- We are transitioning to data for decision making. What is the vision here?
- We have learned from a number of sources to shift toward data driven decision making (i.e. SELV+)
Cross-cutting:
- Automated data filtering
- Excel spreadsheet
- Column naming and standard definitions are critical
Stock Imbalance by Facility Report is the most important report.← GET THIS RIGHT
- This report reduces stock outs
- Facilities know who they can get stock forms and redistribute. They use the following fields to get the products they need:
  - Months of Stock (MOS)
  - Average Monthly Consumption (AMC)
- Filters are on the left side. If you can't find anything, you can filter by province to see what is around your area.
- In the future, this could filter by geographic location.
- There are different users who use these reports for different reasons:
  - District person can identify which facilities have them and which don't
  - A facility person can look at the report, call the person and do a transfer-in or transfer-out
    - This is often done when they do a stock count
    - In the future, could this process be direct assisted by using the data provided in this report specifically to help find low-stock commodities from nearby facilities?
- Supply flows top down also within the same level. Requisitions go up and transfers go laterally.
- Issue affects your average monthly consumption, but transfer-in transfer-out does not affect that AMC.
  ACTION ITEM make sure the definitions of Issuing and Transfers are accurate on the wiki and relationship with Stock Management needs to be double-checked (CHAI drove the development of Stock Management and may be more
- ENHANCEMENT Can we add analytics to this so we can show the trend? Each report needs a table view. However, can we us
Vaccine terminologies are different than other programs (Essential Meds, Family Planning, HIV). However, other programs have similarities.
Make sure column names are the same across reports and one column name is the same across each report. If you call it 'Overstocked', it is overstocked in every report.
- Always remember the user who focuses on delivering a specific outcome.
Vaccines:
- In Zambia, a different system is managing vaccines alltogether
- We need to be cognizant that essential medicines, labs and HIV have large investments. i.e. if you're doing vaccines, you're only doing a fraction of the total supply chain process.
Organizational Hierarchy
- Tanzania has hubs across the country at the province level and there are multiple in specific locations. These hubs are based on population density.
  - These hubs will requisition from Central Medical Stores. They are stock holding hubs. Facilities within that catchment area will requisition for these hubs.
- In Zambia, there aren't stock holding hubs, they are pass-through hubs.
  - It goes from the central medical store to specific facilities.
- For other countries there aren't hubs, they just go from central medical store to facilities.
- In some cases provinces are hubs, but they don't necessarily touch the supply chain, they just view
- The ideal scenario is that each hub is a stock holding hub. Stock holding is a complex process that requires physical infrastructure.
- The configurability of this hierarchy is critical.
- The size of the country, population density and storage are all indicators in the
Stock Management in OpenLMIS does not have the ability to view a shipment moving through it. It focuses on breaking down stock so we can use that stock at this facility.
ENHANCEMENT - Facilities say they have sent a requisition, central store doesn't say they received it. It's incredibly important for facilities to know where their requisition is. Facilities need a way to do time series analysis on each transaction on the requisition, order and shipment. A lot of this is in the third party ERP system. We need to build a report that identifies how much is in the warehouse before the truck arrives.
- This requires an integration with an ERP to get the information. We need to consider structuring reporting around shipments to augment these missing integrations.
- Look into "feedback" report to know when their order has been 'picked and packed' at the warehouse
- We have an idea here around converting to locations from facilities. OpenLMIS v3 wants to transition to this.
Diagram from Wendy:
- When we define facilities, the country team determines the architecture of the organization hierarchy
- Tanzania and Zambia. How things are interlinked:
  - In the country, you have different tools. At the facility level, we have Facility Edition and an Electronic Health Record (i.e. Smartcare or OpenMRS)
    - The EHR captures information on the clients who receive the commodity and pushes to DHIS2
    - The prescription is generated in the EHR
    - There is an interface between Facility Edition and the EHR.
    - When there is a prescription, eLMIS dispenses the drugs from the store room. That data is pushed daily to the eLMIS central edition (stock status report)
  - The ERP interfaces with the eLMIS and you can push data to a pipeline
    - Pipeline is a software that helps with forecasting of demand and coming up with a procurement plan. This planning tool also helps with storage throughout.
  - At the Central level, you have ERP, eLMIS and Pipeline
  - At the Hub level, you have inventory management and eLMIS
  - At the Service Delivery Point, you have eLMIS Facility Edition and EHR
  - At the client level, you have the patient record system.
  - Each of these push reports to DHIS2.
Abstraction of different levels:
- Central Warehouse is the national source for stock. You can have 1 or N central warehouses
- Stock Holding Hub places orders to the central warehouse, receives them, unpacks them and adds it to their stock on hand. This hub receives orders from service delivery points and distributes. Examples include district and provincial stores.
- (new) Pass Through is a location in the middle that receives prepackaged shipments, but doesn't unpack them. They just store and forward shipments.
- Service Delivery Point is the location where commodities are dispensed and consumed. This level could be a brick and mortar health post, department and a hospital or community health worker.

Group Discussion User Profiling

User Profiles were built in the Product Committee meeting

Ashraf's explanation of the configuration of user profiles in eLMIS (Shared presentation):

eLMIS reports are part of eLMIS itself. The reports are built in as part of the same code base. There isn't a data warehousing solution.
When a report is developed, we give the report a name and add it to the rights table so that it can be assigned to a role.
Once you add a report as a right, you can then assign it to one or more roles. i.e All Reports View
- The role that you define as a district, or persona. Then, you assign this to the persona and that's how they are configurable.
- When you create a role, there is a report viewing right.
- Then, you assign a role to the user.
- Grouping of roles can include Administrator reports, district level reports, facility reports
There is also a custom report process which supports ad hoc reporting. System administrators who know SQL are able to create these custom reports with a SQL editor.
There is also a way to show which users are able to view which reports. The individual data is not filtered through the reporting view structure. The supervisory node roles are what restricts the drop downs in report filters.
Users are often responsible for their working environment and program.
Roles are based on:
- Supervisory nodes
- Geographic zones
ACTION ITEM Team Ona, scope this process from eLMIS and how we link the information from the rights table in the database and the tables where data is stored
The reports are built into angular. The reports are built into the controller in elmis/modules/openlmis-web/src/main/controller
Data is pulled from a REST API.
The report builder is where the SQL query is defined.
The data structure in OpenLMIS v3 will be defined as the data warehouse.
ACTION ITEM Discuss Auth service and role definition as a service for Superset. Where can we define these roles in a common place
Muhammad's point:
- We are able to create a view that filters the role level data. Query name is "public.vw_user_facilities" Then, if you create a view, that can be joined with any query to be able to filter the query for that particular user.
- We could create a reporting group and add facilities to the reporting group. We can define the parameters in a single way and then filter the facilities.
- Just like the requisition group, which is a set of facilities. The end users are able to create their own groupings.
  - Example: Seasonality
    - There are some facilities that are cut off due to the rainy season.
    - you can group those facilities together and run a report based on their group.
  - The data is still available through the supervisory node structure, but I want to group a subset of my total view.
dw_order is a table that has pre-computed values. It's populated by SQL stored procedures.
OpenLMIS v3 includes a list of permission strings per user

Malawi:

Created tableau workbooks, one for each province.
We don't have individual rights per report.
Each report has a filter built into them so you can select the period, program and district that they are interested in seeing.
Central level, district or facility level.
We made multiple reports at the central level to show district level, then at the district level, we can see facility information.
ACTION ITEM Clay & Craig - Mary Jo sent example Malawi reports. Review these.
In the end, we need a role based access control system with roles and rights as outlined in eLMIS

Goal of the profile discussion: Identify the reason the users view the reports and why they use a particular report. The users

There are some reports that are needed once per year, or ad hoc. We have an archetype of each user. We need to abstract that from what we use

What needs to be available offline:

Print R&R or POD

Note: Each program area has two to four tracer commodities that need to be shown in the dashboard.

General Reporting Strategy

We group the reports. If you are a facility, you can see all of these reports.

Groups include:

Donor Reports
Implementing Partners
Ministry of Health High level

All users at a facility has a view to all reports within a particular category. This is different from the store in charge model where a particular user profile has access to a subset of reports.

Giving users access to reports:

Wendy showed how they grouped the various reports.
They create different roles with access to different groups (one was for donors, one was implementing partners, stakeholders, specific lower level users)
The higher the level, the fewer the reports.

OpenLMIS v3 uses the same rights and roles process

Which grouping of rights did you put into a role.

Countries need to be able to define who gets access to which reports.

Stock Imbalance Report

Link: Reporting - Stock Imbalance Report
This report should be developed together with the Stock Status Summary with Map report.
All of this data comes from the requisition (note: we can only make one requisition per reporting period)
ACTION ITEM During the implementation discussion, we need to identify the source of information from requisitions vs. stock management
- We have a stock based requisition. So, if we manage the stock within the system, it's easier to create a requisition.
Order Quantity is built from the Requisition
Product Category:
- A program will have multiple categories. i.e. anti-malarials
This report only takes into account of the regular requisition. Emergency requisitions are not appropriate for this report.
ENHANCEMENT We can create notifications based on this report.
Unknown (AMC = 0) filter - You have stock, but you aren't using them. There are some brands that are available, but aren't being used.
No Drilldown in this report
Who is looking at this?
- District level above including M&E, managers, etc.
- This is a generic report to be used for anyone who wants to use it

Stock Status Summary with Map

Link: Reporting - Stock Status Summary with Map
Reports>Stock Keeping>Stock-Status by Location
- Program: Antiretroviral Drugs
- Schedule: Monthly
- Year: 2018
- Period: Mar 2018
This particular report is giving the stock status, but with a map.
The user views the boundaries on the map and drills down to their area.
This report tells whether a group has a stock out or not. It just shows visually what is available. If a user clicks any district, they get a summary bubble
Only some facilities handle a particular program. The bubble displays the total number of facilities and the expected number of facilities that should be reporting
Lower table in bubble:
- Which of the facilities that were over stocked, under stocked and adequately stocked.
- This is months of stock based on consumption
There are underlying calculations that are happening in the background. These are defined per program area.
- The months of stock is defined by commodity and program. The whole program will have a total number of months of stock.
- Essential Medicines has changed a couple of times, but other programs have not.
ACTION ITEM Discuss the gradient on this report in the map. Muhammad Ahmed (Unlicensed) can share the technical details here.
Who is looking at this?
- Almost everyone at the district and national level. When I look at my district, I can see how I compare to others.
- The facility can click on the table below and a facility list is available.
- If I am at the service delivery point, I need to see this for my facility as a snapshot.
- Multiple reasons:
  - District manager I see which facilities are overstocked
  - M&E team at a district and I need to provide routine supervision on how often they are having stock outs and reporting. Quarterly, before I go visit a facility, I need to go through this report and view this, stock imbalances report and reporting rates report. I'm trying to identify facilities that are performing poorly to plan my visits.
    - ENHANCEMENT We could link these reports together in a single dashboard showing trends over time. Are they just having a stock-out because it's a one time thing?
      - Maybe it would be better for the user to have a stock management reporting interface. We could put all of these charts near each other.
  - Stock redistribution has
- A program manager is looking from a birds eye view, worrying about expiry data (district or provincial levels)
- At the facility, the in-charge, wants to make sure they are not having overstock
  - They also want to make sure they can find others who may have something they need per program.
At the bottom of the report, when you click on red, orange or blue, the name of facility pops up. Then, you can drill down to the R&R.

Reporting Rate and Timeliness

Link: Reporting - Reporting rate and timeliness
We shouldn't put a particular date for this report. It needs to be configurable at the global level. i.e. we don't need it to be on the 22nd of a particular month, we need to be able to configure this.
- Zambia: Operationally, everyone needs to report by the 5th of the month. The system should only start to calculate this report based on the 10th of the month.
- There needs to be a place in the system that defines the reporting due date. From there, we need to calculate the report timeliness.
- There is a timely reporting date, grace period and non-reporting date.
We would want to capture this in OpenLMIS and then reporting would just key on it.
In Malawi:
- There is a default date on timeliness (i.e. 20 days after the reporting period that they select)
- You specify the reporting period interval, you have a grace period afterwards and then define the grace period.
- The Status "Submitted" is counted as reported. We need to establish the difference between the date it's authorized vs the date it's submitted.
Who is looking at this?
- This report is mainly for district level up.
- The facility uses this report to verify that they submitted.

Consumption Report

Link: Reporting - Consumption Report
This consumption data is one of the outputs.
This report shows consumption, converts it to packs
Consumption:
- Issues from store room to department then consider consumed. (issues data is a proxy for consumption)
- Dispensing directly to a patient
Consumption per pack is converted to the number o packs based on the pack level.
Adjusted consumption is based on number of days stocked out. The formula for Adjusted Consumption is available in the system
- See: OLMIS-886 - Getting issue details... STATUS
We need to identify the lowest level of the health system and then run this report at that level. Consumption is not calculated in the intermediate levels.
You can also drill down
Who is looking at this?
- Higher than facility level (district and higher)
- One of the biggest uses of this report is for quantification and forecasting. This quantifies what the country needs based on consumption.
- If I am rationing commodities, we need to understand the high volume consumption. Then, you balance these needs as planning.
- External users compare third party sources against this report.
- Supervisors also track abnormalities in the system. If you see a spike, why is this?
This report will also need a trendline for consumption → See Metabase report for this
This could utilize the OpenLMIS tagging feature.
- This needs to go into the configuration

Administrative Reports

Link: Reporting - Administrative
The list of users report should be built into the UI of OpenLMIS.
The User Summary Report can be expanded
- This is useful for troubleshooting and auditing
- We need to identify a role for system administrator, what information they need and then move through to identify how their lives could be improved through reporting and dashboards.

Order Summary

Link: Reporting - Order Summary
If you have a budget tagged to a report, you will need to request additional budget and business processing. In some cases, there is the need to have a supplemental budget to augment what you currently do.
- Groups will want to manage their budgets, but no implementations are there at this time.
OpenLMIS v3 does not currently manage budgets. Costs are calculated per product, requisition and order, but we do not have budgeting features.
ACTION ITEM Figure out in the implementation portion how to deal with budgets.
Who is looking at this?
- Primary audience is the Logistical Management Unit - Central Level - who makes the shipments. Only the people who do the "convert to order" will be interested in this report.

Order & Item Fill Rate Report

Link: Reporting - Order & Item Fill Rate Report
This is a report that has their systems interfaced with the warehouse application. When the requisition is converted to an order, it's sent to an ERP. This report is most valuable when OpenLMIS is integrated with another pack size.
Item fill rate is something that comes from central warehouse
Quantity received is from the proof of delivery and the facility accepts what they received.
Warehouse Management System interface is required for this.
This sounds like an operational report that would be valuable within OpenLMIS. The item fill rate value is very valuable for trend analysis.
ACTION ITEM During the implementation meeting, we need to identify the operational value of this report versus the advanced shipment notification in the POD.
Who is looking at this?
- The warehouse so they can meet their Key Performance Indicators
- The facility they would like to know in advanced what is shipped. If they aren't getting anything, they need to be able to pu
Dependencies:
- Sending orders to WMS or ERP
- Receiving shipments from WMS or ERP.
- Proof of Delivery Integration

Feedback Report

This is the one and only report that facilities use and it is not currently in the gap reports.
ACTION ITEM Spec this out and add it to the gap list.
The central system prints this report and attaches the delivery documents.
Facilities don't have access to see this directly.
This report shows the quantity approved and supplied. If there is a discrepancy between quantity approved and supplied, it's put in bold. They then have an idea of which quantities are being approved and supplied.
Action after viewing this report:
- If there is something that wasn't fulfilled, they look at consumption and decide to look around for that value and get it.
- If you look at this commodity, you can see that the central store has it in 1000s, but they only needed in 100s. This report can help you with the pack size issues. This shows that you got the commodity, but in a different pack size. It's a substitution.
Who is looking at this?
- Facilities look at this every month when they expect to receive requisitions.

Logistics Summary Report

Link: Reporting - Logistics Summary report
This shows the health of the supply chain how it is performing.
The district person will show one row per product. When you disaggregate it, you see all facilities.
Who is looking at this?
- This is viewed at the district up
- Facility level people do not want to run this

Adjustment Summary Report

Link: Reporting - Adjustment Summary Report
This is losses and adjustments. This includes inter-facility transfers.
The source of this is requisition based, entering the total number of items that were transferred in
This is a valuable report at the facility level for auditing
Who is looking at this?
- This is viewed at the district level up
- This could be valuable at the facility level during auditing.

Total Cost of Orders

Link: Reporting - Total Cost of Orders
This report is a total cost of orders for a particular facility.
This is used for budgeting
Who is looking at this?
- This report is not done in Zambia. It's valuable for Tanzania

Lessons Learned from Reporting in Zambia

Make sure reports are delivered up front
- There are routine reports that need to be done straightforward
This is an area of continuous change.
We need visibility of reports and the continuum drives user based
All of the reports were in Supply Chain Manager -
Maps, graphs and charts are very valuable because they represent the capabilities of the system, showing maturity throughout.
We have to get buy-in for each report and define a common ground for each report. Some groups have deep interest in a particular reporting area that adds minimal value to other areas.
If we get this right, other areas won't have to go through this?
Have each country think through the reports. The core needs to be configurable, we need to develop reports that are country specific.
Users don't always know what they want until you show it to them.
There is a request for real time data and we need to logically think through the value of this information.
The team has been doing annual analytics modes.
We have transitioned from paper to computers. Now, we are identifying how it adds value to the system and moving toward data for decision making.
- Can we figure out how to make decisions on this data? Those who have learned how to make decisions, we are moving toward analytics.
There are different donor reporting needs. Identifying that these reports are for them.
This is a progression and we move forward as users become more mature in the system.
There is competition in the space that impacts the facility users.
Future value:
- There is historical data that can add a lot of value to the future of eLMIS and OpenLMIS.
- Zambia is a country that is greatly impacted by flooding. We could identify cost efficiency and reduced disease spreads.
- We need to perform total cost of ownership for the entire solution
We have to demonstrate what we are doing, with reason and we can identify how to improve.
ACTION ITEM Evaluate the current feedback mechanisms from implementations that identify new features and integrate it with the current governance structures.

Day 2

Carry Over Discussion on Strategy

The team had a free form discussion in the morning for 1 hour discussing a strategy
- We will have:
  - 1 owner per report who is responsible for organizing the team and ensuring it's developed
  - A clear process for defining business logic in the reporting stack
  - Clear documentation guidelines for each report including:
    - Source APIs
    - Nifi integration
    - Postgres data tables
    - Superset visualization information
- The primary focus is on the requisition service reporting followed by
- (THIS NEEDS MORE NOTES)

Cross Cutting Functional Requirements

Authentication, Roles and Rights

We discussed this yesterday in depth and will have a conversation on Wednesday in depth with Josh and Wes.

UX Consistency

Follow the same organization that is done in eLMIS with filters and such
We need to standardize column names across all reports
Try to put filters in the same place for these reports
Same type of headers and export format so it's straightforward
Prepopulate filters:
- For example, default to the last reporting period (requisition). Make this configurable and set it up.
Show last updated date in every area that shows a particular "as of time"

Exporting Reports

We need to have a way to choose to print as PDF, Excel or CSV
These items need to have logos and localization support
The PDF export for some reports have a different output than the HTML output. PHL81 is the report number.
Superset features:
- Can we have a print layout?
  - We can have a CSS template embedded in each dashboard in Superset
  - ACTION ITEM Test to see if you can create a print CSS media type page
- CSV export:
  - We can export the CSV on the top of each chart
    - If we have a table chart type, it looks best
    - If we export a chart, we just get the values that generate that chart.
- Superset is very valuable at visualization. To this end, we should create a tabular report for each report so users can see it and export it.
Superset limitations:
- PDF: What happens when you try to print a dashboard?
  - ACTION ITEM view what is printed on the page for each dashboard and make sure everything shows up. If it shows up, does it show everything?
  - ACTION ITEM Analyze the difference between the current PDF output in eLMIS compared to OpenLMIS and Superset.
- PowerPoint:
  - What is the process for getting these reports into a PowerPoint?
- Translations:
  - Can we setup the free text titles to be localized?
  - What do we need to localize column names?
    - We need to label all of the labels in the language
    - ACTION ITEM Lookup to see if this is a feature request in Superset.
- Excel:
  - Superset does not have the ability to export as Excel.
  - eLMIS users want to add headers to existing CSV reports and generate an Excel export that looks nice.
  - This is a lower priority compared to CSV, but we need to
- ACTION ITEM Can we do subtotals in exports? Can we export headers that show what information was filtered?
Scheduled reports:
- Every month, a user may want to be able to setup a schedule when there is a report that they want.
- We could create a monthly digest snapshot of the dashboard.
- This is a future feature that is a nice to have right now.

Offline Reporting Requirements

Which activities are available when not connected to the internet?
- R&R form
Within these activities, what adds values?
Decision: This is not a requirement for OpenLMIS v3.

Embedding charts into OpenLMIS

Requirement:
- Embed a chart within OpenLMIS when the user logs in on the dashboard
- Embed an entire dashboard within a page
- Provide menu items linking to the Superset dashboard or report
Discussion:
- This is dependent on the Auth process
- What about roles/rights?
- ACTION ITEM How does Superset do roles and rights?

Data structure in PostgreSQL and Performance

Look at the requisition reports, identify if there is a common structure that can be used for multiple reports
Orders in eLMIS has a flattened structure
Requisition uses a materialized views
Roles, rights, facility filters are in a view like we saw yesterday.
Those that don't use materialized views have many joins.
Performance:
- There are some issues if you are directly querying the transactional tables.
- There is a difference between using direct views and materialized views
- Is there a mechanism for archiving data in eLMIS?
  - When they migrated from supply chain manager to eLMIS, they kept an archive table
  - However, there isn't a core feature for archiving information over time regularly.
- Pagination standards (50, 100, n rows)
- They expect the entire dataset to be exported.
- Data set filters - There is a maximum query size on large reports that can be exported.
  - IMPORTANT Across the board, you can often pick the year and the period, not date ranges.
- Notes on Superset:
  - We need to be able to aggregate to whatever level they want.
  - Make sure to have a whereby clause that skips null values
  - Also, we need a skip status in the R&R if the R&R line item has been skipped all together.
  - You can also skip the requisition.
  - Geographic zone has created a view that views districts for a particular country and zone.

Data Migration and Demo Data

Data Migration is out of scope
Demo data will be required for the reporting solution.
ACTION ITEM Try to get this from Malawi.

Performance Metrics for Operation

A catchy dashboard was built, but it was very slow when the users load the homepage.
We are dividing the dashboard into different sections:
- High Level dashboards of high level items in the first band.
  - We run this regularly, refresh and expand
- The second band is only refreshed when expanded
  - More detailed stock status
- Third band contains more detailed information
ACTION ITEM See if Superset can do something like this.
Standard measures include:
- 10 seconds for the first band
- 15 seconds for most reports and 30 seconds for complex reports like a map and an entire year of data.
- Any data entry screen needs to be performant
Concurrent users:
- Toward the end of the reporting period, after the cut off time, this is a very intense period required for reporting.
- 50 concurrent users is the max.
How often is the dataset updated from OpenLMIS?
- This is dependent on:
  - How often Nifi queries OpenLMIS
  - How often the materialized view is updated
  - How often any stored procedures run
ACTION ITEM Define the target hardware size for the deployment of the reporting stack alongside OpenLMIS.

Metrics on User Behavior

Google Analytics:
- Integrate with Google Analytics
  - Which reports are most often used
  - Create a custom JavaScript code that captures user id showing who is the most active user

Technical Review of 12 Reports

There is a hierarchy of how the reports are structured:

Transactional tables in database
views (either postgres or materialized)
relationships
Java model
Java Controller
JavaScript view (Angular)

Critical Views to replicate from eLMIS:

vw_stock_status_2
vw_districts - Use for reference purpose, providing the parent child relationship. This district is serving these facilties
vw_user_facilities - Displays the row level data for the facilities and pass on the user_id of the logged in user
User Permission Strings from OpenLMIS v3 - This is a structure for filtering things like requisitions. The requisitions service would be valuable in

New views or materialized views to create that would be very valuable:

Consumption view
Stock_status view from requisitions
Timeliness and completeness report
Orders

IMPORTANT Core Work:

We need to filter at the database level based on the logged in user ID
- Sync the user information from OpenLMIS rights and roles into the reporting database.
- Have the database figure out the strings in the join clause
We need to figure out how Superset can get the username from OpenLMIS
Question: How do we tag the information within the Postgres reporting database so it displays the information based on the user's rights.
- Every table would have to have a permission string, facility, district, etc. in it to be able to perform the filters.

SQL Tricks:

Joining between the tables, just try to join the numeric ids across tables
Don't use functions in the where clause
Make sure the columns in the where clause are indexed. We have an index on the table in the where clause

Outstanding Questions:

How often do we update each API endpoint or microservice?
What is the role of data pumps?
When do we lock a particular metric such as a requisition?
How do we link the user's permissions in Superset? Should we join?

Stock Report Data Model Review (Stock Status Summary with Map)

Database

Does not use a view
There are calculations in this report that we need to replicate.
- Month of Stock
  - Divide month of stock for AMC
  - First check that AMC is not 0 because you would get an error. If 0, then we return zero
- Stock Status: Over stock, under stock, adequately stocked
  - Divide the stock on hand by AMC to 2 decimals then evaluate if it's greater than, less than.

Browser not supported