Versions Compared

Old Version 45

changes.mady.by.user Andy Dingley

Saved on

compared with

New Version Current

changes.mady.by.user Andy Dingley

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 (tick) (question) (error)

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.

(tick)

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.

(tick)

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.

(tick)

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

Merge rules handled.

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

Using:

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

(error) (tick)

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.

(tick)

Fundingissue

Populating from Intrepid

SELECT distinct(Issue) from vwPostFunding

N/A

(tick) 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

(tick)

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

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

JsonPath

Internal table


N/A

(tick) 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”)

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

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

(tick)

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.

(tick)

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.

(tick)

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.

(tick)

Rotation

N/A

(tick) 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.

(tick)

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.

(tick)

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

(tick)

Status

Hard coded in Reference Service

N/A

(tick) Not required.

TariffRate

Populating from Intrepid

SELECT distinct(TariffRate) FROM vwPostFunding

N/A - Not in use by NI

👈 Ignoring because of comment.

(tick) 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.

​(tick)​

TrainingNumberType

Hard coded in Reference Service

N/A(question)

Required?(tick) 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

(tick)

...

TIS table

Intrepid

From old/new ETL

Source

Hicom_Northern_Ireland DR - Data to migrate toTIS

Dev Progress (tick) (question) (error)

ContactDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

(tick)

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

(tick)

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

Funding

Populating from Intrepid

old

Select * from vwPostFunding

N/A

(tick) No data to migrate.

FundingComponents

Populating from Intrepid

old

select * from vwPostFundingComponent

N/A

(tick) No data to migrate.

GdcDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

(tick)

GmcDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

(tick)

Person

Populating from Intrepid

New

from vwPerson

FROM vwPerson

(tick)

PersonOwner

Internal populated by stored proc

Populated by TIS SYNC

(tick) Not required.

PersonView

Internal populated by stored proc

N/A

Ashley Ransoo - Get rid of this on TIS

(tick) Not required.

PersonalDetails

Populating from Intrepid

New

from vwPerson

FROM vwPerson

(tick)

Placement

Populating from Intrepid

New

from vwPlacement

FROM vwPlacement

(tick)

PlacementFunder

N/A

Ashley Ransoo - Get rid of this on TIS

(tick) Not required.

PlacementSpecialty

Populating from Intrepid

New

from vwPlacement

FROM vwPlacement

(tick)

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)

(question) Is getting populated, need to confirm data looks good.

Post

Populating from Intrepid

New

from vwPost

FROM vwPost

(tick)

PostFunding

Populating from Intrepid

old

select * from vwPostFundingComponent

N/A

  • Use of TIS Post Funding Bulk Upload functionality

(tick) No data to migrate.

PostGrade

Populating from Intrepid

New

from vwPost and vwPostOtherGrade

from vwPost and vwPostOtherGrade

(tick)

PostSite

Populating from Intrepid

New

from vwPost and vwPostOtherSite

from vwPost and vwPostOtherSite

(tick)

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

(tick)

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.

(question) 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

(tick) Not required.

Programme

Populating from Intrepid

old

SELECT * FROM vwProgramme

SELECT * FROM vwProgramme

(tick)

ProgrammeCurriculum

Populating from Intrepid

old

Join of vwProgramme and vwCurriculum table

Join of vwProgramme and vwCurriculum table

Note: ProgrammeCurriculum.gmcProgrammeCode = vwProgrammeCurriculum > CurriculumGMCReferenceNumber

(tick)

ProgrammeMembership

Populating from Intrepid

New

from vwCurriculumMembership

from vwCurriculumMembership

(question) 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

(tick)

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

(tick)

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

(tick) 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

(tick) 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.

(tick) 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

(tick) 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

(tick)

...