Root Cause Analysis - Data from plan is not syncing to web and dashboard

[AngelaKabari]

Describe the issue

A user reported an error where they filled in the data, then sync completed, but the corresponding data was not displaying on Reveal Web dashboard. The aforementioned issue was resolved by manually uploading the data from a db dump created by the user before they logged out. See Issue #1 for more details.

We are now trying to establish whether the data not showing on the dashboard was caused by a user error or software issue.

How is this expected to work?

To evaluate the cause of the issue, we require an OpenSRP Engineer to write a query to check the Reveal Thailand DB for plans that don’t have any data synced to them and are expired plans. This will help us figure out if Issue #1 was a software or user problem, because once we identify such plans, we can ask the users on those plans to sync their data. If other users can successfully sync and have the information show up on the dashboard then it was a user problem. If they can’t, then it is likely a software issue that we will require to replicate and then undertake a deep dive to resolve.

Please share other relevant information about the issue

None

[ekigamba]

Queries used

• SELECT p2.json->>'title' AS plan_title, p2.json->'effectivePeriod'->>'end' AS end_period, completed, total, ceil((completed * 100)::decimal/total) as perc_complete, date_created, date_deleted FROM (SELECT json->>'planIdentifier' AS plan_identifier, SUM (CASE WHEN json->>'status' = 'Completed' THEN 1 ELSE 0 END) AS completed, COUNT(id) AS total FROM core.task GROUP BY plan_identifier) p INNER JOIN core.plan p2 ON p.plan_identifier = p2.identifier ORDER BY perc_complete;
• \o plan_tasks/plan_taskscount-`date '+%Y-%m-%d-%H%M'`.txt

[ekigamba]

Here are the tabulated results:

• plan_tasks_count-order_by_end_period_2023-01-06-15_58.txt
• plan_tasks_count-order_by_completion_percentage_2023-01-05-22_16.txt

Both provide a completion percentage which is the a percentage of the completed tasks for a plan. One of the files has the results sorted by completion percentage while the other is sorted by plan end date.

The assumption is we do not have any tasks generated on the devices. The completion percentage should help review plans that were completed on ground but have little to no data available on the server

@AngelaKabari

[AngelaKabari]

Thanks for these @ekigamba. Is is possible for you to include a column with the username in the results? This will make it easier for DVBD to ask the health workers with plans that were completed on ground but have little to no data available on the server to sync their devices.

This will then let us test the hypothesis that Issue #1 was indeed a user error.

[ekigamba]

plan_tasks_count-order_by_perc_complete_2023-01-06-22_38.txt

plan_tasks_count-order_by_end_period_2023-01-06-19_58.txt

@AngelaKabari Here are the tabulated results with the task owner as the first column. Note that a plan can have multiple providers assigned/owners. I hope this helps

[AngelaKabari]

Indeed it does @ekigamba. Let me share this with the DVBD team...

[pongthepm]

I have sent the file plan_tasks_count-order_by_end_period_2023-01-06-19_58.txt with Namkang, the BVBD staff for checking. Last week, I asked her if BVBD still have the problem. She said "Yes", they still have. She will capture the screens of the problems for me (I have been waiting that). Also, she will ask the users to record a video when they sync the data next round of the plan.

[AngelaKabari]

Thanks @pongthepm. We shall wait to hear from Namkang.

[pongthepm]

She added she will tell the sites to change the plan status from inactive to cancelled. For the completed plans, they should be changed to completed.

[pongthepm]

Reported problem of the reveal web @AngelaKabari

plan: B1 ยายเภา (8606011901) 2023-01-23 กิจกรรม 29.055 M-Health id: 18fa167f-ae5f-5baf-9567-74e15c7835c6

user: 11.4.5-1

Detail: The user entered and sync the data successfully, but there is no information on the web. The user cannot check the work plan progress and activies. Howerver, the information is showing on the dashboard. Please see the attached files.

Thank you, Pongthep

[AngelaKabari]

I confirm receipt of this information @pongthepm - we shall review and get back to you in the course of tomorrow.

[AngelaKabari]

Hello @lincmba,

It seems that we are experiencing this error again.

Could you please take a look at the plan with the details below and re-run the ETL to update the raw_tasks in the canopy warehouse, and thereafter update the tasks data being served by the task_structures_materialized_view?

• Plan name: B1 ยายเภา (8606011901) 2023-01-23 กิจกรรม 29.055 M-Health
• Plan ID: 18fa167f-ae5f-5baf-9567-74e15c7835c6

• Focus Investigation View page

  Please let me know if that resolves the issue or we need some further investigation into this?

[lincmba]

@AngelaKabari, diognosed this with the help of @HusnaHariz and @p-netm. Found out the following.

• There was a plan in the raw_plans table of id 18fa167f-ae5f-5baf-9567-74e15c7835c6 but the same was missing in the plans table
• A plan cannot be created unless it has a valid jurisdiction and upon checking this plan's jurisdiction, the jurisdiction of id c36352b8-4a5f-4a2b-a3d8-4e8454094700 was in the raw_jurisdictions table but missing in the jurisdictions table

This usually means that NiFi failed to sync the entity type (jurisdiction and plan). This was therefore resolved with the following steps

• Manually specify the jurisdiction, using it's server version to nifi for reingestion and rerun the Jurisdictions - Process and Store process group for this jurisdiction
• Manually specify the plan, using it's server version to nifi for reingestion and rerun the Pland - Process and Store process group for this plan
• Refresh jurisdictions_materialized_view materialized view
• Refresh plans_materialzied_view materialized view.

This seems to have resolved the issue. Kindly review

[AngelaKabari]

@lincmba I confirm that the work plan progress and activities are now displaying as they should on the dashboard.

@pongthepm please check with DVBD to confirm that the issue has been resolved satisfactorily?

[pongthepm]

I told her to check it. Still waiting her reply.

[pongthepm]

@AngelaKabari Namkang told me that the problem is now fixed for her. Thank you for your support. Pongthep

[AngelaKabari]

Great, I'm glad to hear that @pongthepm. Please could you check with Namkang if there are any other users having sync issues so we can fix those too?