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

Attendees:

• @Ashraf

• @Brandon Bowersox-Johnson

• @Clay Crosby (Unlicensed)

• @Craig Appl (Unlicensed)

• @Josh Zamor (Deactivated)

• @Mary Jo Kochendorfer (Deactivated)

• @Muhammad Ahmed (Unlicensed)

• Wendy Bomett

• @Wesley Brown

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: Configure Adjusted Consumption (N)DonePreview

• 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.