Versions Compared

Old Version 43

changes.mady.by.user Andy Dingley

Saved on

compared with

New Version 44

changes.mady.by.user Ashley Ransoo

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:

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

(error)

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

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

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)

...