...

The PAV Dashboard is intended to display data collection from the miscellany of sources described on this page. It's important to note that two of them, SELV and SISMA (DHIS-2), follow the MoH standard described here wherein reporting periods begin on the 20th, end on the 21st, and thereby span two Gregorian months. Every other report relevant to the PEV Dashboard uses the traditional calendar, wherein each period begins on the 1st of the month and ends on the last day of the same month. The original Excel-based version of the Dashboard reflects these differences. For example, users viewing "February" data which originates in SELV or DHIS-2 are shown information relevant to January 20th - February 21st. Meanwhile, users reviewing "February" data from a system like ColdTrace are presented information related to February 1st - February 28th. The new version of the Dashboard should behave the same way as the original version in this regard.

With few exceptions, most of our reports' data should be provided via CSV files sent via email attachments to dashboard-pav@openlmis.org.

SELV

SELV is a legacy version of OpenLMIS maintained by VillageReach. As such, the SELV-Dashboard will be able to directly connect to its database. Please see the Credentials page for connectivity details.

A somewhat dated ERD diagram may be found here. Note that a table called facility_visits_report exists within the database but, because it's not used by OpenLMIS directly, does not appear in the ERD diagram. It's populated by an ETL script run daily and is one of very few tables from with VillageReach's internal Tableau reports pull data. Due to the size of the facility_visits_report table, it may be preferable to draw data from  with the source (OLTP) tables instead.

SELV includes the following form, which seems to offer everything necessary to report on stockout days: 

...

There was discussion at one point that the MoH wanted to define 1 "stockout" event within the SELV+ Dashboard as 5 consecutive days without stock. This is no longer the case. The SELV+ dashboard could therefore draw its stockout values directly from OpenLMIS if it were to report on them. Ultimately, however, because the Dashboard doesn't have to directly report stockout days, it doesn't  matter how they're defined. The dashboard only needs to show the percent of facilities which have incurred one or more stockouts (regardless of duration) during the past reporting period. A single day sans stock qualifies a facility to count toward this percentage.

The MoH would like the Dashboard to display the number of days which current stock levels are projected to last. The Vaccines_Needs_Formula_Stock Availability_Months_weeks_Days.xlsx spreadsheet illustrates how to calculate the projected stock amounts, and these calculations should be performed during the data-ingestion process. Note that the coefficients shown within the spreadsheet should be easily configurable rather than hardcoded.

Although we previously thought otherwise, there is value in preserving and presenting historical stockout projections. 
It would be sufficient and appropriate to show the projected stockout values in months. (For example: stock is projected to run out in 0.5 months, or in 1.5 months.)
The projected stockout value should 

The data collected within the SELV form shown above is saved to the epi_use_line_items table. The following query is slow as-as, but may serve to illustrate the data we need:

...

1. Potentially less load on NexLeaf (X requests per day at most, rather X requests every time a user views the dashboard).
2. A better user experience given that our users have limited bandwidth, and retrieving X local files is more performant than performing X HTTP requests.
3. Inoculation to a degree against NexLeaf system outages. Even if NexLeaf is down when a user views a dashboard, we'd still be able to present the previously polled data.

In descending order of specificity, facilities belong to a Central Level -> Province -> District. A current proof of concept created for the new Dashboard shows provinces like Gaza and Niassa - which is sufficient. If NexLeaf provides the requisite images, though, it would be nice if the Dashboard could show District data as well. This, however, it isn't crucial. If need be, we can simply refer users to NexLeaf's website for additional detail.

Because NexLeaf's imagery isn't necessarily user-friendly, we should include a key/legend explaining it.

...

RED stands for "Reach Every District" and REC means "Reach Every Community." The RED/REC report is a CSV file which will be delivered via an email attachment. When it is, it will obviate all previous RED/REC data which has been submitted. In other words, the RED/REC attachments are complete and wholistic rather than cumulative. They're also unique in this way - every other CSV provided to the system will exclusively contain data for the most recent period.

Although referred to as RED/REC report, the data emailed to the Dashboard will exclusively contain Districts. For our purposes, we need not worry about "Community" data.

The dashboard should display the total number and rate of districts for which planning and implementation has occurred.

SMT

The SMT is a static Excel tool provided by the WHO for province level stock management. We can consider it an immutable data sources.

...