Add web coverage reporting

[isabelcshaw]

Users will need to see reporting on key MDA program indicators on the Reveal Platform. See further requirements illustrated here

We will need to create a new option in the "Monitor" dropdown called "MDA Community Reporting."

The reporting views will follow the existing IRS workflows, where the user can click down through jurisdictions and see a set of statistics. For this project in Zambia, the pilot is expected to be implemented at the district level: Gwembe District.

Note: The lowest level of the location hierarchy / reporting will be the operational area.

Note: there are two statistics at the district level that need to have hardcoded denominators. This is allow them to see numbers of children found and treated against the greater population. They have only given us population numbers for the district level in one district - we do not need to show this statistic at any other level

Indicators: [District Level Only]

• % of children found --> # of children registered / # of children expected (Total number of children who were registered in the community as compared to the total number of children expected) - we will need to hardcode this denominator at the district level only: estimated # of children anticipated between 5-14 and 11 months in 25,771
• % of children treated (Actual vs Expected) --> Total number of children who were registered and received drugs in the community as compared to the total number of children who were expected to receive drugs - we will need to hardcode this denominator at the district level only: # of children anticipated between ages of 5-14 and 11 months is 25,771

[All Levels]

• % of eligible children treated --> Total number of children who were registered and received drugs in the community as compared to the total number of children the head of household says live in the house (this is a question asked in the registration process
• % structures found and visited (# of structures found/total number of structures) --> Number of structure found and either marked as not the main structure (grey) treated (green), partially treated (orange) or refused (red) over the current number of structures in the community (all of the above plus unfound structures (yellows)). This excludes structures marked as ineligible (black).
• # PZQ distributed --> # of tablets distributed at that jurisdiction

See the indicators sheet in the Data Dictionary for original requirements.

[gstuder-ona]

Overall tasks here are:

1) Create table for hardcoded population constants per-district and per-age-group (including adults). Pretty simple.

2) Create structures_report (materialized?) view of district structures and treatment status using geographic or logical boundaries. Question - can we assume structures are registered correctly to the right district? It's a whole lot easier when we can. This will mean aggregating across structure registrations and tasks similar to IRS reports. Finicky as structure event and structure task data fields have overlapping information which means they need to be carefully prioritized and deduped.

3) Create households_report (materialized?) view of district households, # children in household, visitation status, and (maybe?) structure id. Not too challenging but we'll need to search for latest household registration events.

This, combined with the existing child_reports (aggregated by district) should let us build this (sort of wonky) combined view. Question - does the view need to be multi-level, to drill down from plan->district?

[gstuder-ona]

@isabelcshaw questions above ^^^

[gstuder-ona]

pinged Wambere on slack too but she's not attached to this repo

[Wambere]

I don't have access to the Data dictionary, so not sure if info on the households and structures is supposed to be from the individual tables or from the events.

Also, this seems to be a duplicate of https://github.com/onaio/reveal-frontend/issues/1026 with another LOE there

[gstuder-ona]

re: @moshthepitt 's comment on https://github.com/onaio/reveal-frontend/issues/1026

Instead of hard coding as described in the issue above we should try and use the jurisdiction metadata functionality to upload the total population, total children etc.

This would actually be the best way vs a separate table, if there's an interface for the clients to use it.

[isabelcshaw]

@gstuder-ona @Wambere added you to the data dictionary

For the first question, yes, I think that's fine to assume. We were asked to replicate the IRS reporting mechanism, so I believe it should be acceptable to operate under the same assumptions.

For the second question, yes, this needs to be a drill down. The expectation is that the user lands on a national level view and can click down through the jurisdictions.

I'm not totally sure I understand your comment about a "combined" view (combined with the existing child_reports). Is this referencing the individual child view built for Eswatini? If so, that view is not necessary here. The lowest level of reporting will be the operational area.

Lastly, one other detail that I didn't mention above is that in this plan, there is the concept of "floating families." This just means that the end user can register and dispense drugs to families outside of a structure (they will not appear on the map). These families will just be associated with the location of the user. (Aka if the end user is credentialed at the operational area level, they are associated with that OA, if the user is credentialed at the district level, they will be associated with the district.) Does this complicate the reporting at all? We'd want these children to be counted in all of the stats above except 4 and 5.

[gstuder-ona]

I'm not totally sure I understand your comment about a "combined" view (combined with the existing child_reports)

I just meant that the information about children treated, broken down by jurisdiction, is already in an existing view in the db so we don't need to create it again. The webui slices for a bunch of reports is based on that, the core view data is just aggregated in different ways.

For the second question, yes, this needs to be a drill down. The expectation is that the user lands on a national level view and can click down through the jurisdictions.

That's fine - we'll just need the population metadata for each jurisdiction level (or at least all the lowest jurisdiction levels). That could be a lot of data that Akros will need to provide, and past history suggests we'll need to plan on not having it immediately :-).

re: floating users - aka if the end user is credentialed at the operational area level, they are associated with that OA, if the user is credentialed at the district level

This does complicate things, but we've dealt with it in the IRS views as "virtual jurisdictions" - basically a bucket at each level for extra families to go in. Just as a warning, for the IRS views we had lots of issues with Akros trying to validate the calculations on their side and opening incorrect issues because it's complex to take these into account. For example, 100% of children in all op-areas may have been given drugs, but then at the higher-level only 90% have since 10% of families were floating and these weren't addressed.

Technically we'll need to nail down exactly how to tie a drug dispense event to a location - via the default_location of the child, via the default_residence of a family, via the location in the task event data, or via the worker's "credentialed?" location? These will generally differ and are another big source of misunderstood-calculation issues being raised. The same problem for families - what criteria do we use for marking a family as "visited" (all children dispensed drugs, at least one task with status "visited") - and structures - what criteria do we use for marking a structure as "partially treated" or "treated" and does it depend on the family status above?

[gstuder-ona]

So the overall scoping here is - can be broken out into sub-tickets:

1) Get population metadata into OpenSRP location properties or a separate table (ideally not done by this team, but +2d if we have to get rough info in ourselves for sanity checking). EDIT: Aggregate this data recursively into counts at each jurisdiction level (1-2d) can probably be done as part of 4

2) Create client_households view associating clients with their households, extend mda_client_dispense_report with household identifiers for clients (1-2d)

2) Create mda_household_report with household identifier, jurisdiction(may be null), structure (may be null), last_mda_visit_jurisdiction (may be null), mda_visit_status (2d)

3) Create mda_structure_status_report with structure_id, jurisdiction, mda_treatment_status (3d - lots of structures makes it slow to iterate here)

4) Combine mdaclient status, mda_household status, structure status views with the (multi-level) jurisdictions of the MDA plans and aggregate values at each jurisdiction to create an mda_community_report view. (3d,+1d for back-and-forth with the webui team and +1d for productionizing and making sure NiFi refreshes views on an appropriate schedule)

@Wambere thoughts? Too aggressive?

EDIT: Think this work can be done by a data-solutions engineer with a 1/2 time canopy engineer for oversight and periodic deployment - parts 1-3 probably is mostly data-solutions working in a sandbox and then finalized with 4.

[Wambere]

lgtm

[moshthepitt]

Get population metadata into OpenSRP location properties or a separate table (ideally not done by this team, but +2d if we have to get rough info in ourselves for sanity checking). EDIT: Aggregate this data recursively into counts at each jurisdiction level (1-2d) can probably be done as part of 4

@gstuder-ona @Wambere if we use the web UI functionality to upload jurisdictions metadata, let us note that these end up being stored server-side (in OpenSRP) as "settings" and so to use them in Canopy we would need to use the settings API (the v2 settings endpoint - which allows us to get settings by identifier) to pull these into Canopy before we use them. And I think that this changes the LoE.

[gstuder-ona]

@moshthepitt ugh agree - is there any way to upload metadata to the properties of jurisdictions themselves? I don't really understand much about the "settings" endpoint but any new endpoint is going to be harder to manage.

By any luck does the settings endpoint support batch pulls, or is the idea that you always need to separately call it? If we've got a lot of jurisdictions that could get expensive. Also, I'm guessing updating settings doesn't actually update the jurisdiction serverVersions, so we'd have to trigger that manually?

[moshthepitt]

is there any way to upload metadata to the properties of jurisdictions themselves?

You can do this by sending to the locations API endpoint. But we are not using this, in the web app, because of:

1. We need a way to have arbitrary metadata i.e. it may be risk in Zambia or Total population in Nigeria or who-knows-what-else elsewhere and this may change per year e.g. risk2019, risk2020 etc. Using settings tied to jurisdictions seemed nicer.
2. Retrieving settings in bulk for a particular identifier (e.g. risk2019) for all jurisdictions that have it set is breezy and the payload received is nice and compact

By any luck does the settings endpoint support batch pulls, or is the idea that you always need to separately call it? If we've got a lot of jurisdictions that could get expensive.

Yes, using the v2 settings endpoint can batch pull based on the settings identifier e.g. give me all jurisdictions metadata for the identifier named risk 2019

Also, I'm guessing updating settings doesn't actually update the jurisdiction serverVersions, so we'd have to trigger that manually?

No it does not :(

[gstuder-ona]

@moshthepitt just looked at the source code - wasn't sure if there were more docs elsewhere

Seems like we can sync down settings separately as a 7th? OpenSRP entity that doesn't support incremental load. Logically you'd specify a type of "setting" and a settingName (and maybe settingVersion?) and then periodically clear? and reload all of these into the db with a processor?

@isabelcshaw @moshthepitt if I've got that right, figuring out how to do that well will take something like 3-5d (canopy) dev time but it's definitely something I'd tag as a scope-risk. It would probably be worth parallel pathing it and inserting a mock jurisdiction metadata table temporarily to unblock the rest of the data-solutions work.

[moshthepitt]

@gstuder-ona:

just looked at the source code - wasn't sure if there were more docs elsewhere

Sorry about this. I don't think there is clear documentation anywhere. @dubdabasoduba am I right?

Seems like we can sync down settings separately as a 7th? OpenSRP entity that doesn't support incremental load. Logically you'd specify a type of "setting" and a settingName (and maybe settingVersion?) and then periodically clear? and reload all of these into the db with a processor?

Yeah this sounds about right.

[dubdabasoduba]

@moshthepitt this is the only setting docuementation available