wk5 jun_epic Resolve missing encounters data in Facility file

[staceypark]

vizEnc and dataEnc run only through January in all the facility files

[staceypark]

@anazariz Once this issue is fixed, ping @lijenn and @staceypark on #2090 for Test QA. Then propagate in this order:

1. Marcia's site where the issue was first identified
2. All other facilities engaged in this wave

Ping @lijenn @staceypark on #2098 for PROD QA.

3. Rest of the country

Ping @lijenn @staceypark on #2098 for PROD QA.

We will also see if this resolves the issue of mismatched encounter data between dataUI and facility file identified by Marcia.

[anazariz]

The issue with SplashMothership.xlsb Excel file:

As this is a 32 Bit Excel file, it cannot handle large data well (see this).

In fact, the file fails often during propagation. At times these failures cause file corruption. A copy of an uncorrupted version must be available at all times to resume propagation (after configuring the uncorrupted file with the latest data where the corrupted file was interrupted). The exchanging of uncorrupted/corrupted file and preparation for resumption is a lengthy process, which occurs numerous time during the monthly propagation. In one such exchanges an older version of the file was used (as the good copy). This resulted in the VBA (in the Excel file) pointing to a wrong version of a table in PTSD_OMHO (without the vvc addition), which has not been refreshed since the vvc code was implemented. Unfortunately the correct version of the code is no longer available (as shown below) because the file that contained it (a good file) is also corrupted. I know what the corrupted file contained (as I wrote the original coding) and will proceed to correct the issue. It'll take a couple of hours for me to put the code back in, since the version that had the code is now corrupted, and and hour to test it.

--pointing to the correct view select max(b.visitdatetime) from schema.splash.SplashSourceEnc a join schema.splash.v_SplashSourceDiag b on a.visitsid = b.visitsid and a.sta3n = b.sta3n where a.sta3n = 'xxx' and b.sta3n = 'xxx'

--correct output: 2021-05-31 18:08:00 --___

--pointing to the wrong table select max(b.visitdatetime) from schema.splash.SplashSourceEnc_vvc a join schema.splash.SplashSourceDiag b on a.visitsid = b.visitsid and a.sta3n = b.sta3n where a.sta3n = 'xxx' and b.sta3n = 'xxx'

--incorrect output: 2021-01-30 21:19:00

[anazariz]

This item is fixed. RCA (Root Cause Analysis) explained earlier in this card.

Note – Facility file is having a hard time dealing with the large volumes of data in it. This is more pronounced in the cloud than the on-premises. Per my observation there is heavy data traffic between the application (Workbook) and the database, which now has to go through extra layers of security check in the cloud. This causes frequent timeouts with larger datasets (several Facilities). In some cases I have put error trappings that catch and display these timeouts so you can see what is going on. In some cases the errors are not trapped. So they happens all that is observed is missing data. This is not necessarily and error or ANDON, it is just a transaction that did not complete. After fixing 2089, we tested this by comparing the Facility in the cloud and the on-premises. The cloud version had all kinds of issues whereas the onsite did not.

[lijenn]

@staceypark & @lijenn to test and check for the missing data in co-planning Facility files and then live teams before propagation.

[lzim]

@staceypark & @lijenn to test and check for the missing data in co-planning Facility files and then live teams before propagation.

I really appreciate your help with these tasks Jenn & Stacey. I reminds me of making sure that we have specified the co-planning tasks as checklists for CRM.