Date	2017-08-23
Authors	Graham O'Regan (Unlicensed)
Status	complete
Summary	As a result of adding HELASE to the Intrepid.DataRepository.HEE database, Hicom's DR refresh failed to complete the TIS ETLs only had access to the previous Friday's data.
Impact	Revalidation nationally had stale data.

Root Cause

As part of the Consolidation project, the HELASE schema was moved to the Intrepid.DataRepository.HEE database but the increase in data size caused the Hicom ETL to fail.

Trigger

Moving new schema to DR

Resolution

Hicom are planning on letting the process run naturally tonight to get the data back up to date.

Detection

Our nightly checks detected changes between the DR and mirror. Hicom also emailed us to let us know that they had had a problem.

Action Items

None

Timeline

8:16 Russell Steel (Unlicensed) confirmed that the data had appeared in the new database.

9:29 Martin at Hicom emailed us to let us know that they had hit problems.

Supporting Information

Slack Transcript

russ [8:16 AM]
HELASE has now moved over to Intrepid.DataRepository.HEE;

TABLE_CATALOG table_schema (No column name)
Intrepid.DataRepository.HEE HEYHD 63
Intrepid.DataRepository.HEE HENW 63
Intrepid.DataRepository.HEE HEWMD 63
Intrepid.DataRepository.HEE HEEMD 63
Intrepid.DataRepository.HEE HELASE 63
Intrepid.DataRepository.HEE HEEOE 63
Intrepid.DataRepository.HEE HEELIVE 63
Intrepid.DataRepository.HEE HENE 63

russ [8:43 AM]
uploaded and commented on this file
RowCounts 230817.xlsx
49kB
Excel Spreadsheet
Click to download
3 Comments
@srochani a couple of diffs between the DR and mirror this morning;

owner tab_name rc distinc_rc Vlookup Mirror RC RC Diff
HEEOE vwPostOtherSite 11094 11094 11086 -8
HELASE vwPerson 76724 76724 19781 -56943
HEELIVE vwPerson 57508 57508 57506 -2
HEELIVE vwPlacement 238398 238398 238401 3

russ [9:01 AM]
Here are the missing 2 HEELIVE.vwPerson;
select * from HEELIVE.vwPerson where personid in
(269841179,
269974769)
Both from the 21st;
2017-08-21 16:16:13.923
2017-08-21 13:12:30.173

graham [9:03 AM]
@srochani looks like the load is still running, guessing that is just because lase is coming in for the first time?

[9:03]
mind you, the other day should have aligned by now

srochani [9:16 AM]
@graham yes it's for Lase

russ [9:20 AM]
commented on RowCounts 230817.xlsx
Here are the missing 8 missing HEEOE.vwPostOtherSite rows;

select * from HEEOE.vwPostOtherSite where postothersiteid in (
50135180,
50135181,
50135182,
50135183,
50135184,
50135185,
50135186,
50135187)

srochani [9:40 AM]
uploaded and commented on this image: Screen Shot 2017-08-23 at 09.39.36.png
1 Comment
@russ I’ve checked the today’s extracted csv files, these ids are not present in csv.
It looks like these are new ids and just added into DR

Browser not supported