Page Comparison

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.

...

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

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

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

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

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

...

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

PostSite

Populating from Intrepid

New

from vwPost and vwPostOtherSite

PostSpecialty

Populating from Intrepid

New

Code Block

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

ProgrammeCurriculum

Populating from Intrepid

old

Join of vwProgramme and vwCurriculum table

Note: ProgrammeCurriculum.gmcProgrammeCode = vwProgrammeCurriculum > CurriculumGMCReferenceNumber

ProgrammeMembership

Populating from Intrepid

New

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

RightToWork

Populating from Intrepid

New

Code Block
SELECT EEAResident, PermitToWork, Settled, PersonID as personIntrepidId, VisaDetails, VisaIssued, VisaValidTo, AmendedDate as amendedDate FROM HEELIVE.vwPerson p

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

.

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

Code Block
SELECT NTNID as intrepidId, NTNTrainingNumber as trainingNumber FROM HEELIVE.vwNTN

Code Block
SELECT NTNID as intrepidId, NTNTrainingNumber as trainingNumber FROM vwNTN

...

Versions Compared

Old Version 44

New Version 45

Key