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
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	No query in existing ETL. Currently taken from DR only, using: `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	SELECT distinct(ReligiousBelief) FROM vwPerson	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	SELECT distinct(SexualOrientation) FROM vwPerson	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		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
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	SELECT PostID as postIntrepidId, Specialty1ID as specialtyIntrepidIdTemp FROM HEELIVE.vwPost p WHERE Specialty1ID IS NOT NULL and SELECT PostID as postIntrepidId, SpecialtyID as specialtyIntrepidIdTemp FROM HEELIVE.vwPostOtherSpecialty p WHERE SpecialtyID IS NOT NULL;	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	SELECT EEAResident, PermitToWork, Settled, PersonID as personIntrepidId, VisaDetails, VisaIssued, VisaValidTo, AmendedDate as amendedDate FROM HEELIVE.vwPerson p	SELECT EEAResident, PermitToWork, Settled, PersonID as personIntrepidId, VisaDetails, VisaIssued, VisaValidTo, AmendedDate as amendedDate FROM vwPerson p
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	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	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.	Only populated for inserted specialty, not populated when match found in HEE data.
TariffFundingTypeFields	Populating from Intrepid	old	select * from vwPostFunding	N/A	Not required.
TrainingNumber	Populating from Intrepid	New	SELECT NTNID as intrepidId, NTNTrainingNumber as trainingNumber FROM HEELIVE.vwNTN	SELECT NTNID as intrepidId, NTNTrainingNumber as trainingNumber FROM vwNTN

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
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

Review of Consolidated DR ETL used in previous Migrations

Working Group

Overarching assumptions

TIS Reference

TCS

Assessment

DEV Queries