Introduction

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.

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 may be value in preserving and presenting historical stockout projections. 

Open Question: Stockout projections are based on the current stock, and this data presumably comes from SELV. However, SELV's data is reported a month (and occasionally two) after the fact. It seems as though even our most current stockout-projections will therefore be a month or two old. Are such projections nevertheless useful and actionable?

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:

...

*

...

* OpenLMIS treats "Sarampo" and "Sarampo 2 MSD" as two separate antigens. It really shouldn't though - they both refer to an antigen called "MR" (which stands for Measles and Rubella.) Although the SELV+ Dashboard currently references "Sarampo," it should instead just list "MR" (which, again, should represent a combination of OpenLMIS' "Sarampo" and "Sarampo 2 MSD". ** Question for Timoteo Chaluco (Unlicensed): verify that this doesn't have to appear in the SELV+ dashboard, given that it isn't in SELV.

The above table includes several items which appear in OpenLMIS but not the original version of the SELV+ Dashboard. These items do not need to appear in the new version of the SELV+ dashboard either.

...

Please see the Credentials and SIS-MA DHIS2 Software Requirement Specification pages for details.

Note: DHIS-2 currently contains no population estimates for 2018. Our reports are dependent on this data and, until the MoH adds it to DHIS-2, will alway show values of zero.

HSS Funds (former e-SISTAFE)

...

RED stands for "Reach Every District" and REC means "Reach Every Community."
On the dashboard, is to be displayed the total number and rate of districts on which the planning and implementation has occurred.

Note: CSV file will be submitted via email to be ingested.

The RED/REC data 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.  

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.

...