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.

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

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

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.

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.

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.

Clay Crosby (Unlicensed)'s comments:

Thanks for providing the URL. It looks like we'll receive one URL per location per year. If so, we would need to have one iFrame per location per year. Depending upon the amount of locations, this could be quite extensive.

Would it be possible for NexLeaf to give us the raw data in some other way than via an API? Could they produce a monthly CSV extract? What data do they use to generate this chart?

FYI I've provided a screenshot of the Chart's configuration here:

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/

Hi Clay Crosby (Unlicensed). I gave NexLeaf some more thought while on the plane and agree that our tentative approach isn’t ideal. Although we can always ask NexLeaf to manually produce a CSV extract, I’m concerned that we would be:

1. A) Degrading the solution’s automation and thus value, and
2. B) Adding reliance on the ongoing effort of a team which may not be beholden long-term to VR, the MoH, of this project. (I may be totally off-base here. It seems the groups we’re asking to manually provide CSV files, though, are directly affiliated with the MoH and can thus reasonably be expected to deliver.)

A drawback of reliance on NexLeaf’s visualizations is that they’re definitely a departure (and really a subset) of what the current dashboard displays. I think we first need to establish whether that’s acceptable. If it is, we can then focus on implementation detail. Because we only have three provinces, it seems we’d need just 3 HTTP requests. That’s probably palatable. Rather than pull the data from NexLeaf as needed and on demand, though, it seems that a better solution would likely be to pull and save NexLeaf’s images to our local database as part of the daily ingestion process. The benefits would be:

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

I realize this approach would be a more work than simply using an iFrame. If we indeed want to rely on NexLeaf’s imagery, though, I hope it’s one we can consider.

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.

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.

RED/REC

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

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.