Review of Consolidated DR ETL used in previous Migrations

Owned by Ashley Ransoo
Last updated: 18 Dec 2020 by Andy Dingley
11 min read

This page captures consolidated DR ETL which was used to migrate data from Intrepid to TIS for Local Offices. This page will be used to review and set out the mapping of Source entities to target entities for Northern Ireland data migration to TIS from the National Data Warehouse Hicom_Northern_Ireland Data Repository.

 

Working Group

@Ashley Ransoo @James Harris

Overarching assumptions

  • The solution design the dev team has come up with is a separate instance of TIS that sits in parallel with HEE TIS with a synchronisation mechanism in-between to sync the data.

  • Reference although desirable to be a common service to serve the different instances may not be available for MVP

  • Reference are separate for MVP and no sync mechanism for MVP. Making changes in one by the users may therefore result in those no reflecting in the other. Need to however look into a way of re-syncing the reference Post-MVP with those changes.

  • We do not have Reference from Hicom Intrepid DR, therefore will be derived with the exception of Site, Trusts and Grade.

  • ID’s (Primary Keys) are not overlapped or duplicated across instances.

  • The Consolidated DR ETL used previously may not be fully fit-for-purpose in its current form and will need some adjustments to use with Northern Ireland data migration which is highlighted in the tables that follow.

  • Intrepid Id’s in TIS NI would need to be removed when when migrated so that there is no overlap with Leave Manager in HEE TIS.

MVP: https://hee-tis.atlassian.net/browse/TISNEW-3822

It looks like we’ll need to run the OLD ETL for at least reference and that it would be wise to use both as fully as they can be: a)enable all steps in the old ETL & then the other ETL to patch over the top.

POST MVP SYNC: https://hee-tis.atlassian.net/browse/TISNEW-5691

TIS Reference

TIS table

Intrepid

Query

Hicom_Northern_Ireland DR - Data to migrate to TIS

Existing ETL Dev Notes

Dev Progress

TIS table

Intrepid

Query

Hicom_Northern_Ireland DR - Data to migrate to TIS

Existing ETL Dev Notes

Dev Progress

College

Populating from Intrepid

select DISTINCT(College) from vwSpecialty

  • Take CURRENT Reference > College from HEE TIS and keep as CURRENT on NI TIS

  • Take all College from NI vwSpecialty records that DO NOT match with CURRENT Reference > College from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

 

Country

Populating from Intrepid

select distinct(Country) from vwPersonQualification

  • Take CURRENT Reference > Country from HEE TIS and keep as CURRENT on NI TIS

  • Take all countries from NI Qualification records that DO NOT match with CURRENT Reference > Country from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

CurriculumSubType

Hard coded in Reference Service then populating from Intrepid

select distinct(CurriculumSubtype) from vwCurriculum

  • Take CURRENT list from HEE CurriculumSubType.

Migration disabled.

Requires manual step: extract and load CURRENT from HEE TIS.

DBC

Hard coded in Reference Service



  • NIMDTA - take what is available as CURRENT

  • Take current list from HEE and add NI onto it

  • What is the DBC code for NI? @Mark.Oliver (Unlicensed)

  • DBC Code for NI is 1-25U-830

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS with addition of NIMDTA value in script.

Using:

INSERT INTO `DBC` (`id`, `dbc`, `name`, `abbr`, `status`, `uuid`) VALUES (15, '1-25U-830', 'Northern Ireland Medical and Dental Training Agency', 'NIMDTA', 'CURRENT', '7c3c73b3-4160-11eb-8983-0242ac1e0108');

No results from:

select distinct(DesignatedBodyCode) from vwPersonRevalidationState where len(trim(DesignatedBodyCode)) > 0

EthinicOrigin

Populating from Intrepid

SELECT distinct(EthnicOrigin) FROM vwPerson

  • Take CURRENT Reference > EthinicOrigin from HEE TIS and keep as CURRENT on NI TIS

  • Take all EthinicOrigin from NI Person records that DO NOT match with CURRENT Reference > EthinicOrigin from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

Fundingissue

Populating from Intrepid

SELECT distinct(Issue) from vwPostFunding

N/A

 

No data to migrate.

FundingType

Hard coded in Reference Service



  • Take CURRENT list from HEE FundingType

Migration disabled.

Requires manual step: extract and load CURRENT from HEE TIS

GdcStatus

Hard coded in Reference Service

SELECT distinct(GdcStatus) FROM vwPerson

  • Take CURRENT list from HEE GdcStatus

Migration disabled.

Requires manual step: extract and load CURRENT from HEE TIS

Gender

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(Gender) FROM vwPerson

  • Take CURRENT Reference > Gender from HEE TIS and keep as CURRENT on NI TIS

  • Take all Gender from NI Person records that DO NOT match with CURRENT Reference > Gender from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

GmcStatus

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(GMCStatus) FROM vwPerson

  • Take CURRENT and INACTIVE values from HEE TIS

Migration disabled.

Requires manual step: extract and load all from HEE TIS.

Grade

Hard coded in Reference Service then populating from Intrepid

select * from vwGrade

  • Take CURRENT Reference > Grade from HEE TIS and keep as CURRENT on NI TIS

  • Take all Grade (CURRENT and INACTIVE) from NI Grade records that DO NOT match with CURRENT HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

InactiveReason

Populating from Intrepid 

SELECT distinct(InactiveNotes) FROM vwPerson

  • SELECT distinct(InactiveReason) FROM vwPerson

The field InactiveReason is being used. No InactiveNotes in vwPerson.

JsonPath

Internal table



N/A

 

Not required.

LeavingDestination

Populating from Intrepid

SELECT distinct(LeavingDestination) FROM vwCurriculumMembership

  • Take all the LeavingDestination from DR and keep in NI TIS in Backend

Note: This field is a legacy field. NI to use the new Leaving Reason field going forward.

Currently taken from DR only.

Seems to work with tweaked query (“Other” & “Western HSC Trust”)

LocalOffice

Hard coded in Reference Service



  • NIMDTA - take what is available as CURRENT

  • Take current list from HEE and add NI onto it

  • @Mark.Oliver (Unlicensed) to confirm the below:

    • Local Office Name = Northern Ireland Medical and Dental Training Agency

    • Abbreviation = NIMDTA

    • Post Abbreviation = NIR e.g. of a post NIR/1-5XKXJ/001/CT2/001

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

MaritalStatus

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(MaritalStatus) FROM vwPerson

  • Take CURRENT Reference > MaritalStatus from HEE TIS and keep as CURRENT on NI TIS

  • Take all MaritalStatus from NI Person records that DO NOT match with CURRENT Reference > MaritalStatus from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

MedicalSchool

Populating from Intrepid

SELECT distinct(MedicalSchool) from vwPersonQualification

  • Take CURRENT Reference > MedicalSchool from HEE TIS and keep as CURRENT on NI TIS

  • Take all MedicalSchool from NI PersonQualification records that DO NOT match with CURRENT Reference > MedicalSchool from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

Nationality

Populating from Intrepid

SELECT distinct(Nationality) FROM vwPerson

  • Take CURRENT Reference > Nationality from HEE TIS and keep as CURRENT on NI TIS

  • Take all Nationality from NI Person records that DO NOT match with CURRENT Reference > Nationality from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

PlacementType

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(PlacementType) FROM vwPlacement

  • Take CURRENT Reference > PlacementType from HEE TIS and keep as CURRENT on NI TIS

  • Take all PlacementType from NI Placement records that DO NOT match with CURRENT Reference > PlacementType from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

ProgrammeMembershipType

Hard coded in Reference Service

 

  • Take CURRENT Reference > ProgrammeMembershipType from HEE TIS and keep as CURRENT on NI TIS

  • Take all ProgrammeMembershipType from NI CurriculumMembership records that DO NOT match with CURRENT Reference > ProgrammeMembershipType from HEE TIS and make INACTIVE on NI TIS

Note: @Ashley Ransoo / @James Harris - Turn LAT to CURRENT on HEE TIS prior to migrating (Done)

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

RecordType

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(RecordType) FROM vwPerson

N/A

Currently taken from DR only.

Seems to work with tweaked query (“Both”, “Contact”, “Staff”)

ReligiousBelief

Populating from Intrepid 

  • Take CURRENT Reference > ReligiousBelief from HEE TIS and keep as CURRENT on NI TIS

  • Take all ReligiousBelief from NI Person records that DO NOT match with CURRENT Reference > ReligiousBelief from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

PermitToWork

 

 

  • Take CURRENT Reference > PermitToWork from HEE TIS and keep as CURRENT on NI TIS

  • Take all PermitToWork from NI Person records that DO NOT match with CURRENT Reference > PermitToWork from HEE TIS and make INACTIVE on NI TIS

Note: This is currently hard coded on TIS rather than reference. There are tickets in the backlog to sort this out in the future for all.

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

Role

Hard coded in Reference Service

 

  • Take CURRENT Reference > Role from HEE TIS and keep as CURRENT on NI TIS

  • Take all Role from NI Person records that DO NOT match with CURRENT Reference > Role from HEE TIS and make INACTIVE on NI TIS

  • From DR de-concatenate by comma delimiter and SELECT distinct(Role) FROM vwPerson

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

Rotation

 

 

N/A

 

Not required.

Settled

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(Settled) FROM vwPerson

  • Take CURRENT Reference > Settled from HEE TIS and keep as CURRENT on NI TIS

  • Take all Settled from NI Person records that DO NOT match with CURRENT Reference > Settled from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

SexualOrientation

Hard coded in Reference Service then populating from Intrepid

  • Take CURRENT Reference > SexualOrientation from HEE TIS and keep as CURRENT on NI TIS

  • Take all SexualOrientation from NI Person records that DO NOT match with CURRENT Reference > SexualOrientation from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

Site

Hard coded in Reference Service then populating from Intrepid

select * from vwSite

MVP:

  • Take all sites from NI SELECT * from vwSite

  • Keep as status as they are

POST MVP (With Sync):

  • Take CURRENT Reference > Sites from HEE TIS and keep as CURRENT on NI

  • In addition keep all existing Sites from NI previously migrated since MVP

Currently taken from DR only.

Seems to work with tweaked query

Status

Hard coded in Reference Service

 

N/A

 

Not required.

TariffRate

Populating from Intrepid

SELECT distinct(TariffRate) FROM vwPostFunding

N/A - Not in use by NI

Ignoring because of comment.

Not required.

Title

Hard coded in Reference Service then populating from Intrepid

SELECT distinct(Title) FROM vwPerson

  • Take CURRENT Title > Title from HEE TIS and keep as CURRENT on NI TIS

  • Take all Title from NI Person records that DO NOT match with CURRENT Reference > Title from HEE TIS and make INACTIVE on NI TIS

Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

​​

TrainingNumberType

Hard coded in Reference Service

 

N/A

 

Not required.

Trust

Hard coded in Reference Service then populating from Intrepid

select * from vwTrust

MVP:

  • Take all sites from NI SELECT * from vwTRust

  • Keep as status as they are

POST MVP (With Sync):

  • Take CURRENT Reference > Trust from HEE TIS and keep as CURRENT on NI

  • In addition keep all existing Trust from NI previously migrated since MVP

Currently taken from DR only.

Seems to work with tweaked query

 

TCS

  • Ensure the NI TIS ID’s/Primary Keys do not overlap with the ID’s in HEE TIS.

TIS table

Intrepid

From old/new ETL

Source

Hicom_Northern_Ireland DR - Data to migrate toTIS

Dev Progress

TIS table

Intrepid

From old/new ETL

Source

Hicom_Northern_Ireland DR - Data to migrate toTIS

Dev Progress

ContactDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

Curriculum

Populating from Intrepid

old

select c.*, s.SpecialtyID from vwCurriculum c left outer join vwSpecialty s on c.CurriculumSpecialtyID = s.SpecialtyID

  • Take CURRENT TCS> Curriculum from HEE TIS and keep as CURRENT on NI TIS

  • Take all Curriculum (CURRENT and INACTIVE) from NI Curriculum records that DO NOT match with CURRENT TCS>Curriculum from HEE TIS and make INACTIVE on NI TIS



Requires manual step: extract and load CURRENT from HEE TIS.

Funding

Populating from Intrepid

old

Select * from vwPostFunding

N/A

No data to migrate.

FundingComponents

Populating from Intrepid

old

select * from vwPostFundingComponent

N/A

No data to migrate.

GdcDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

GmcDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

Person

Populating from Intrepid

New

from vwPerson

FROM vwPerson

PersonOwner

Internal populated by stored proc

 

 

Populated by TIS SYNC

Not required.

PersonView

Internal populated by stored proc

 

 

N/A

@Ashley Ransoo - Get rid of this on TIS

Not required.

PersonalDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

Placement

Populating from Intrepid

New

from vwPlacement

FROM vwPlacement

PlacementFunder

 

 

 

N/A

@Ashley Ransoo - Get rid of this on TIS

Not required.

PlacementSpecialty

Populating from Intrepid

New

from vwPlacement

FROM vwPlacement

PlacementSupervisor

 

 

 

Select PlacementId, SupervisorPersonID, SupervisorType from vwPlacementSupervisor

  • Need to map Intrepid PlacementId’s and Intrepid SupervisorPersonID’s to newly created NI TIS PlacementID’s and NI TIS PersonID’s respectively.

  • SupervisorType = 2 (Educational Supervisor type on TIS)

Is getting populated, need to confirm data looks good.

Post

Populating from Intrepid

New

from vwPost

FROM vwPost

PostFunding

Populating from Intrepid

old

select * from vwPostFundingComponent

N/A

  • Use of TIS Post Funding Bulk Upload functionality

No data to migrate.

PostGrade

Populating from Intrepid

New

from vwPost and vwPostOtherGrade

from vwPost and vwPostOtherGrade

PostSite

Populating from Intrepid

New

from vwPost and vwPostOtherSite

from vwPost and vwPostOtherSite

PostSpecialty

Populating from Intrepid

New 

from PostSpecialty and PostOtherSpecialty

ProgrammePost

To do



TISDEV-3446 - Post to Programme many to many not one to one DONE

Select PostID, ProgrammeID from vwProgrammePost

  • Need to map Intrepid PostId’s and Intrepid ProgrammeID’s to newly created NI TIS PostID’s and NI TIS ProgrammeID’s respectively.

Is getting populated, need to confirm data looks good.

PostView

Internal populated by stored proc

old

 

N/A

@Ashley Ransoo - Get rid of this on TIS

Not required.

Programme

Populating from Intrepid

old

SELECT * FROM vwProgramme

SELECT * FROM vwProgramme

ProgrammeCurriculum

Populating from Intrepid

old

Join of vwProgramme and vwCurriculum table

Join of vwProgramme and vwCurriculum table

Note: ProgrammeCurriculum.gmcProgrammeCode = vwProgrammeCurriculum > CurriculumGMCReferenceNumber

ProgrammeMembership

Populating from Intrepid

New

from vwCurriculumMembership

from vwCurriculumMembership

Query on field source. Can we do this as part of migration or should it be done with existing script?

Qualification

Populating from Intrepid

New

from vwPersonQualification

from vwPersonQualification

RightToWork

Populating from Intrepid

New 

Specialty

Populating from Intrepid

old

SELECT * from vwSpecialty

  • Take CURRENT TCS > Specialty from HEE TIS and keep as CURRENT on NI TIS

  • Take all Specialties (CURRENT and INACTIVE) from NI Specialty records that DO NOT match with CURRENT TCS>Specialty from HEE TIS and make INACTIVE on NI TIS

Merged rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

SpecialtyGroup

Populating from Intrepid

old

Select distinct(SpecialtyGroup) from vwSpecialty

  • Take CURRENT TCS > SpecialtyGroup from HEE TIS and keep as CURRENT on NI TIS

  • Take all SpecialtyGroup (CURRENT and INACTIVE) from NI Specialty records that DO NOT match with CURRENT CURRENT TCS>SpecialtyGroup from HEE TIS and make INACTIVE on NI TIS

Merged rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

SpecialtyTypes

Hard coded in TCS service

 

 

  • All specialties to be inserted in SpecialityTypes table with a row for each type where type = 1 in the DR. Specialty types are PostSpecialty, PlacementSpeciality, CurriculumSpecialty and SubSpecialty.

Merged rules handled.

Requires manual step: extract and load where Specialty is CURRENT from HEE TIS.

TariffFundingTypeFields

Populating from Intrepid

old

select * from vwPostFunding

N/A

Not required.

TrainingNumber

Populating from Intrepid

New

 

Assessment

  • Full copy of Assessment records against the newly created NI TIS PersonIDs

TIS table

Intrepid

From old/new ETL

Source

Hicom_Northern_Ireland DR - Data to migrate to TIS

Dev Progress

TIS table

Intrepid

From old/new ETL

Source

Hicom_Northern_Ireland DR - Data to migrate to TIS

Dev Progress

Assessment

populating from intrepid

New

from vwAssessment

  • same rules which was used to copy Assessments from Intrepid previously (Full Copy)

AssessmentType

Hard coded in Reference Service

Old

from vwAssessment

  • Take CURRENT Reference > AssessmentType from HEE TIS and keep as CURRENT on NI TIS

  • Take all AssessmentType from NI vwAssessment records that DO NOT match with CURRENT Reference > AssessmentType from HEE TIS and make INACTIVE on NI TIS


Merge rules handled.

Requires manual step: extract and load CURRENT from HEE TIS.

AssessmentDetail

populating from intrepid

New

from vwAssessment

  • same rules which was used to copy Assessments from Intrepid previously (Full Copy)

AssessmentOutcome

populating from intrepid

New

from vwAssessment

  • same rules which was used to copy Assessments from Intrepid previously (Full Copy)

AssessmentOutcome_AUD

Internal for history

 

 

N/A


Not required.

Outcome

populating from intrepid

New

from vwAssessment

  • Take all Outcome from Assessment>Outcome in HEE TIS



Requires manual step: extract and load all from HEE TIS.

OutcomeReasons

Internal

 

 

  • same rules which was used to copy Assessments from Intrepid previously (Full Copy)


Requires manual step: extract and load all from HEE TIS.

Reason

populating from intrepid

New

from vwAssessment

  • Take Legacy=False Reasons from Assessment>Reason in HEE TIS



Requires manual step: extract and load non-legacy from HEE TIS.

Revalidation

populating from intrepid

New

from vwAssessment

  • same rules which was used to copy Assessments from Intrepid previously

DEV Queries

https://docs.google.com/spreadsheets/d/1njsFOWLQfWd4H3RwYd-DW56Su3KB2HfvxQef33iUNBQ/edit#gid=0

 

Slack: https://hee-nhs-tis.slack.com/
Jira issues: https://hee-tis.atlassian.net/issues/?filter=14213