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.

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 at the central (country) level as well as at the province level. The Vaccines_Needs_Formula_Stock Availability_Months_weeks_Days.xlsx spreadsheet illustrates how to calculate the projected stock amounts, and provides the population-value for the entire country. Population estimates for each of the provinces may be found within these files. Please note that the calculations should be performed during the data-ingestion process and that the coefficients in the spreadsheets that they rely on should be easily configurable rather than hardcoded.

Although we previously thought otherwise, there is value in preserving and presenting historical stockout projections. 

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:

SELECT 
f.name AS "facility_name",
gz.name AS "geo_zone_name" , 
gl.name AS "geo_level_name",
gl.levelnumber AS "geo_level_number",
gz_parent.name AS "geo_zone_parent",
euli.productgroupname, euli.stockatendofmonth /* Note that stockatendofmonth is nullable */, 
fv.visited AS "facility_visited",
fv.visitdate AS "facility_visit_date",
dist.status AS "distribution_status",
pp.name AS "period_name" , pp.startdate AS "period_startdate" , pp.enddate AS "period_enddate"
FROM epi_use_line_items euli JOIN facility_visits fv ON(euli.facilityvisitid = fv.id)
JOIN distributions dist ON(fv.distributionid = dist.id) 
JOIN processing_periods pp ON(dist.periodid = pp.id)
JOIN facilities f ON(fv.facilityid = f.id)
JOIN geographic_zones gz ON(f.geographiczoneid = gz.id)
JOIN geographic_levels gl ON(gz.levelid = gl.id)
JOIN geographic_zones gz_parent ON(gz.parentid = gz_parent.id)
WHERE dist.status = 'SYNCED';

Note that, as shown below, the collection of productgroupname members returned by the above query is a superset of the products listed in column C of Dados_Stock in the existing Excel dashboard. 

* 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". 

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.

The existing Excel dashboard includes the province of “Inhambane” on the Dados_Stock sheet. This is an experimental province which does not exist in OpenLMIS, and which need not exist in future versions of the SELV+ dashboard until it's introduced into OpenLMIS.

SIS-MA (DHIS2)

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)

The following files are included here only for historical reference:

Desmostrativo Consolidado actualizado.xls

Execucao da GAVI ate 31-12-2017.xlsx

The dev team should refer to either of these files:

English equivalent of the above:

The Excel file includes three tables/segments. We only care about the first one, titled "134GAVI - HSS". The data represented by the Excel spreadsheet will be furnished to us within a CSV file which exclusively includes the data represented by the "134GAVI - HSS" section. The MoH will deliver the CSV file via an email attachment.

By default, SELV+ Dashboard users should only see the data represented by column E of the Excel spreadsheet. The data in columns B, C, and D should be accessible only to users with an elevated permission level.

These reports should be submitted monthly via an email with a subject formatted as follows: YYYY-MM-Monthly-Report.

NexLeaf

NexLeaf won't be able to offer an API which exposes raw data prior to the end of June. They'll instead provide an endpoint which, when accessed with the correct credentials, returns an imagine which looks like this.

The implementation is currently under development. This is how they described it as of 5/14/2018:

There is no documentation right now. The URL looks like this currently:
https://coldtrace.org/plot/coldtrace/lloyd/Tete/2018/data/
... we may change that to be a little more clear name.

You'll see Tete in the url above. That is the location you list the province. 2018 is the year. There is also Niassa and Gaza... but Gaza seems a little broken. As I mentioned previously we have to reorganize our group structure and we will be doing that May/June.

I'll check with the team by next week what authentication looks like.

We can also easily add get parameters to control the image resolution in pixel (pxwd, pxht)... that code is else where and we can add it to this image as well.

Timoteo Chaluco (Unlicensed): As discussed early on, it may be wise to acclimate our users to the idea of having a UI somewhat different than the current one.

Update from Martin:

We were looking through the past plots we've experimented with, and we found one like below. This seems closer to what you have now and is an option for you to use as well. It is easy for us to make small modifications (like parameters to remove the titles, adjust the height, etc).

https://coldtrace.org/plot/coldtrace/lloyd/Tete/20180101/20180201/data/

Reliance on NexLeaf's imagery precludes presentation of the full range of ColdTrace data displayed by the original version of the Dashboard. Dércio Duvane confirmed, however, that this approach is still preferable than one which requires manual data entry

Rather than pull the data from NexLeaf as needed and on demand, though, it would be better to pull and save NexLeaf’s images to our local filesystem as part of the daily ingestion process. The benefits would be:

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 okay. We will refer users to NexLeaf's website if they need more granular detail. (It would have been nice if the Dashboard were able to include district-level detail. NexLeaf, however, doesn't currently expose such imagery via a URL. They plan to, but don't estimate that they'll be able to more than three days prior to our release date.) 

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

Trimestral Reports

Background

On a trimestral basis, provinces send to the central level a summary report in word format containing multiple indicators for the HSS Province Performance. This report, Relatório Descritivo de Actividades HSS_Provincias  includes the following KPI's per district:

•  Mobile Brigades: Planned and Executed;
•  Technical Assistances and Supervisions: Planned, Executed, Facilities visited per district, # of visits to each facility;
•  DQS - Data Quality Review Meetings: Planned, Executed, Facilities Visited, DQS Score per district;
•  Community Engagement and Mobilization
  •  Radio: Planned, Executed
  •  Lectures: Planned, Executed
•  Logistics:
  •  Vaccine Distribution to Province Warehouse: Planned delivery date, Reception Date
  •  Vaccine Distribution to districts: Planned delivery date, Reception Date
•  Trainings
•  Cold Chain Equipment Maintenance activities
•  Balance Meetings

Legend

   Not to be Included on Dashboard or Information not to be pulled from this source

   To templated into CSV and included on dashboard

Summary

The above Word file may be ignored. The two CSV files will be submitted via email and should be ingested. The email's subject should conform to the format YYYY-Period-N-Report, where N is a positive integer between 1 and 4 inclusive intended to identify the period.

RED/REC

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.

These reports should be submitted 4 times per year via an email with a subject that conforms to the format YYYY-Period-N-Report, where N is a positive integer between 1 and 4 inclusive which identifies the period.

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.