[Support Request]: cannot view the progress report on the website (after data sync successfully)

[pongthepm]

Describe the issue

plan: A1 ห้วยสัตว์เล็ก (7608040501) 2022-11-17 id: 723b8cad-c385-5bd1-8d78-acb6f3c1fc89 user: vbdu_5.2.2-1 problem: the regular plan created successfully. It includes the following activities: 1) family registration, 2) blood screening, 3) BCC. Blood draw data recorded successfully. The data synchronized successfully, but we cannot view the progress report on the website.

Web UI: on the website, there is no activity to check Dashboard: มีข้อมูลblood screening = 49

expire: 2022-12-07

Please share other relevant information about the issue

Add any other important information about the problem here.

[AngelaKabari]

Hello @elijahfilip and @truongnt kindly provide authorisation to proceed with support for this issue, that is investigate the cause and generate a LOE estimate for resolution of the issue. In my opinion, this issue warrants a Priority 3 classification - kindly let me know if you agree.

[truongnt]

Agree, please go ahead @AngelaKabari

[ekigamba]

Assuming that the issue is similar to the previous one affecting vbdu_2.1.1-1 and that it might affect other users/plans, let's request the users to sync any data on their devices and not to logout. If they need to logout, kindly request them to export the DBs as a precaution. We found out the the user vbdu_2.1.1-1 had a lot of unsynced data ~98% and we had to upload it manually from the exported database

[pongthepm]

@ekigamba Thank you for your kind support. I will ask the user do it.

[pongthepm]

I have just talked to a bvbd staff, she said the user vbdu_5.2.2-1 synchronized successfully.

[AngelaKabari]

@pongthepm was the user vbdu_5.2.2-1 able to see the data on the dashboard after they successfully synchronised?

[HusnaHariz]

@AngelaKabari

Tasks are in sync between the opensSRP and Canopy DBs

OpenSRP DB

opensrp=# select count(*) from core.task where json ->> 'planIdentifier' = '723b8cad-c385-5bd1-8d78-acb6f3c1fc89';
 count
-------
     550
(1 row)

Canopy DB

reveal1=> select count(*) from thailand_prod.raw_tasks where plan_identifier ='723b8cad-c385-5bd1-8d78-acb6f3c1fc89' ;
 count
-------
     550
(1 row)

[ekigamba]

@HusnaHariz Should this query return 0 items

SELECT id AS id,\n       plan_id AS plan_id,\n       jurisdiction_id AS jurisdiction_id,\n       goal_id AS goal_id,\n       measure AS measure,\n       goal_value AS goal_value,\n       goal_comparator AS goal_comparator,\n       goal_unit AS goal_unit,\n       action_code AS action_code,\n       action_description AS action_description,\n       action_prefix AS action_prefix,\n       action_reason AS action_reason,\n       action_title AS action_title,\n       task_count AS task_count,\n       completed_task_count AS completed_task_count,\n       task_business_status_map AS task_business_status_map\nFROM thailand_prod.targets_materialized_view\nWHERE plan_id = '723b8cad-c385-5bd1-8d78-acb6f3c1fc89'\nORDER BY action_prefix ASC\nLIMIT 15000

This query provides the goals that are rendered on the web.

If you compare to this one

SELECT id AS id,\n       plan_id AS plan_id,\n       jurisdiction_id AS jurisdiction_id,\n       goal_id AS goal_id,\n       measure AS measure,\n       goal_value AS goal_value,\n       goal_comparator AS goal_comparator,\n       goal_unit AS goal_unit,\n       action_code AS action_code,\n       action_description AS action_description,\n       action_prefix AS action_prefix,\n       action_reason AS action_reason,\n       action_title AS action_title,\n       task_count AS task_count,\n       completed_task_count AS completed_task_count,\n       task_business_status_map AS task_business_status_map\nFROM thailand_prod.targets_materialized_view\nWHERE plan_id = '00ae063d-de62-47f9-84c1-ed63bf755eb8'\nORDER BY action_prefix ASC\nLIMIT 15000

[HusnaHariz]

@ekigamba Having a look at why this is the case. The goals for the plan hadn't been pushed to the goal_target table. Working on syncing that and whatever other table that may have missing records. Thanks for the pointer

[HusnaHariz]

The query now returns three items

reveal1=> select count(*) from (SELECT id AS id,
       plan_id AS plan_id,
       jurisdiction_id AS jurisdiction_id,
       goal_id AS goal_id,
       measure AS measure,
       goal_value AS goal_value,
       goal_comparator AS goal_comparator,
       goal_unit AS goal_unit,
       action_code AS action_code,
       action_description AS action_description,
       action_prefix AS action_prefix,
       action_reason AS action_reason,
       action_title AS action_title,
       task_count AS task_count,
       completed_task_count AS completed_task_count,
       task_business_status_map AS task_business_status_map
FROM thailand_prod.targets_materialized_view
WHERE plan_id = '723b8cad-c385-5bd1-8d78-acb6f3c1fc89'
ORDER BY action_prefix ASC
LIMIT 15000) foo;
 count
-------
     3
(1 row)

[ekigamba]

@HusnaHariz It's now reflecting on the dashboard https://mhealth.ddc.moph.go.th/focus-investigation/map/5c77f04f-83a2-5804-87cb-16c31b929fe3 . Thanks

is it clear why this happened?

[AngelaKabari]

@HusnaHariz, do we know why the goals for the plan hadn't been pushed to the goal_target table?

[HusnaHariz]

It's not clear yet. Needs further investigation.

[AngelaKabari]

Okay @HusnaHariz - please look into this and let me know what your investigation finds?

[Wambere]

@AngelaKabari this needs support from the web team. They know what mat view is used where and will be able to narrow down faster where data is missing. We can pick up from them once they find this

[AngelaKabari]

Thanks for the feedback @Wambere. I'll look into this with @p-netm then get back to you.

[peterMuriuki]

@Wambere @AngelaKabari I understand this issue is fixed. The problem was that the web was not receiving goals data from superset slice 3. IMO, The retrospective to understand why the issue happened in the first place would be on the data engineering team's plate.

[AngelaKabari]

Hello @HusnaHariz,

Any progress on establishing why the goals for the plan hadn't been pushed to the goal_target table?

[HusnaHariz]

Unfortunately, it is not possible to understand the exact reason why the goals for the plan hadn’t been pushed to the goal_target table. At the time we checked there were no failed flow files. We have in the recent past found that some plans/tasks were failing because of a missing jurisdiction. The plans workflow has a jurisdiction validation check that drops flowfiles, if it is determined that the jurisdiction doesn't exist.

We are therefore hypothesising that it is possible the jurisdiction was not validated and hence the plan failed to be pushed to Canopy.

For these recent issues, the point of failure seems to be the jurisdiction flow since records exist in the raw table(raw_jurisdiction) but not the normalised tables (jurisdiction). We are therefore working on a flow that will be looking for un-synced records and pushing those again to the normalised tables. This should help mitigate this issue from occurring.

[HusnaHariz]

The Diff Process Group (PG) to process unsynced entities to the normalised tables has been added to all the entity workflows here. Specifically a diff PG has been added for the jurisdiction, location, plan, clients, tasks and events entites.

[AngelaKabari]

Thank you for this @HusnaHariz. We shall monitor this with the DVBD and Biophics teams for the next few months to see if any other sync issues occur.