14/03/2022

Working Group

 

TIS@Alistair Pringle (Unlicensed)@Ashley Ransoo @Adewale Adekoya @James Harris @Kavitha.Shankar

North East, North West and YH@David Short (Unlicensed)@Liam.Lofthouse (Unlicensed) , @Brandon Knott (Unlicensed)

South (WES/TV/SEV/PEN): @Mike.Richardson (Unlicensed) @Kyle Embley (Unlicensed)

London/KSS/SE:  @Maria Kist (Unlicensed) @Sarah Krause @Maxine Owen (Unlicensed) @Leah Kline (Unlicensed)

East of England: @Jack.Adamson (Unlicensed)

Midlands: @Lauren Squires (Unlicensed) Hilary Williams

NIMDTA: @Mark.Oliver (Unlicensed)

Discussion/Action

Link to Metabase dashboard for data quality issues to resolve - https://build.tis.nhs.uk/metabase/dashboard/18

#

Discussion/Action

Responsible

Outcome

Status

#

Discussion/Action

Responsible

Outcome

Status

1

Resolve Duplicates - TISDEV-4841: Resolve the duplicate invalid GMC numbers, such as 'Retired,' and make them 'UNKNOWN'

Data leads

First discussed on DQ on 04/06: 

08/01: TISNEW-2364-Merging, then removal of Duplicate Person Records - Round 1DONE - Work completed for Round 1 on Stage and testing began. Since this was a flyway script, the importance was stressed on completion of the testing on stage as this would block anything else from being released to Production in out release pipeline

DS: Has done some testing. SB: leave manager tests carried out. JH/DS/AH: re-use some of the scripts that were used during consolidation for testing on metabase for next week.

DS: Prog membership, placements and assessments - Looks good so far. 

How long more this would need? - conclude by Wed 16/01.( and  are going to test this on Wednesday afternoon)

21/09: The scripts have been run on Prod over the weekend.

has taken extracts of the data on Friday afternoon ahead of the scripts being run. 

4/02: Round two will be needed. LaSE still to advise DS on what to do with the records that weren't an easy match. Chris picking this up - London may be ok but KSS need more attention

4/02: Leave manager - need to do checks for the circa 500 records to make sure that the round 2 de-duping would not leave orphaned records (as per the procedure for Round 1

01/04:

to provide the list of Id's/template for the second round of duplication (second strongest match rule). To send the list out to all regions be checked. Jen would have done for the South - to be checked?

15/04: Still awaiting for EMD, South and London.

24/06: Jen to speak to David tomorrow (25th) re queries she has found in the South. 

James to ask Gwil where has gotten to with this - returns from leave on the 1st July

08/07: David to resend file to Gwil and Jen is no longer confused   as she now understands that this part of the exercise will not remove ALL erroneous records. Work is continuing........

22/07: Ongoing, waiting for South and EMD data

02/09 EM have submitted, awaiting the South (ongoing)

14/10: Back with DS - to compare and rebuild the new list of what's been merged

  • Might be useful for bringing in the Northern Ireland data into TIS (@Adewale Adekoya

11/11: Completed by South. With David at the moment.

09/12: There will be some duplicates because of Study Leave. The merging would have to be done at some point on TIS. Almost ready to go. 

06/01: DS post on slack for additional feedback.

03/02: DS posted on slack for everyone else to confirm this is OK. (De-dupe Round 2 spreadsheet)

20/04: Recommendation: to be done ahead of Northern Ireland and HCS onboarding onto TIS.

  • Aim for May 2020

04/05:

  • Mids & East

  • North

  • South (requested a report from Hicom re People Id’s that relates to TIS Person ID’s) - To then check against David’s SS

  • London

20/05: Round 2 de-duplication file -

We can go ahead and de-duplicate the records identified (~20) @David Short (Unlicensed) to share those records, @Ashley Ransoo @James Harris @Sebastian Kwok (Unlicensed) create ticket for Round 2.

Some coordination with Hicom after the bulk has been done.

03/06: Dig out the template/ticket and the script for refinement. (@David Short (Unlicensed) @Ashley Ransoo)

15/06: Template in TIS format expected by end today.

29/06: Sue: Hicom sent over a national list (personTISIds) of ~18 approvers to exclude in the merge. (Based on anyone that has ever approved a study leave application). Some coordination with Hicom for these records. The list on Slack to go first iteration excluding these 18 records. Second iteration look at the Hicom coord. ones. Sue to check against the master file.

29/06: Received and ticket updated.

15/07:

South - sent findings on merged records of duplicate Trainees where duplicate trainee record has leave application records

27/07:

Iteration 1: Exclude Hicom Records

https://hee-tis.atlassian.net/browse/TISNEW-4705

Iteration 2: Hicom records only

https://hee-tis.atlassian.net/browse/TISNEW-4871

Include the 38 records to Round 2 - Iteration 2 (Hicom coordinated)

21/09: There are still some duplicates being encountered. Re-run DS script to identify?

Ticket to retrospectively set the training status - ?

06/10: Deduplication Round 3 -

Rules for Round 3 (TBD): (Regional Leads to take it away and look at the above before coming up with a set of Rules as a group)

  • Leave manager might take longer

  • @David Short (Unlicensed) to amend scenario 4 to remove SFB default merge and rerun count

  • 16/11:

Do the Round 3 split into 1000, followed by the hicom records.

14/12: Sue has reviewed this but will be picked up in the new year.

11/01:

https://healtheducationengland-my.sharepoint.com/personal/sue_barrand_hee_nhs_uk/_layouts/15/AccessDenied.aspx?Source=https%3A%2F%2Fhealtheducationengland-my.sharepoint.com%2Fpersonal%2Fsue_barrand_hee_nhs_uk%2FDocuments%2FWork in Progress%2FWork in Progress - Sue%2FData Leads%2FDuplicate Person records on TIS%2F27.11.2020%2FDeDup3.xlsx%3Fweb%3D1%26CT%3D1610365865477%26OR%3DOutlook-Attachments%26CID%3D3DAF47FC-C53D-4693-BC23-1BA7DF1CB05B%26wdLOR%3Dc10F86DBC-38A1-497A-8AFA-F036C59C7557&correlation=5e37a09f-206b-2000-59b2-7e5623befefc&Type=item&name=97d9aad2-370a-4a31-b760-c936ebc525be&listItemId=221480

11/01:

  • Suggestion is to wait until Accent Leave Manager migration, although that would mean re-running the script then. 08/02: Delayed beyond March 2021

  • Archiving work to follow.

08/03: Expecting LM to go live in June 2021.

12/04:

Intrepid Leave Manager ETL

Aim for end of August

Note: 4.30 UTC - NDW pulls data from Hicom

24/05: Testing of the ETL in progress. On target for end of August.

14/09: Accent Leave Manager expected go live 23/09. Go-NoGo on 16/09.

27/09: @David Short (Unlicensed) to rerun above when back from leave.

11/10: DS to share at next meeting.

25/10: DS to share the results on #dataleads.

08/11: Old query was intrepid based, needed to be refactored and apply a weighting as follows. Need to group the distinct records next for the final output and share sql and output on #dataleads.

 



2

Identification and removal of Consultant data on TIS

All

Removal of consultant data has a dependency on De-duplication work.

22/10: Dependency on the De-dupe work. Follow up with Chris after the de-dupe work has taken place. This is to identify which Consultant data belongs to which Trust so that they can have this if the trust wants.

29/10: Linked to the removal of the consultant information from TIS we also need to address the issue of the non-trainee posts. So a further piece of work needs to take place to identify and hard delete these posts. Will need to take a copy of this so that (like with the non-trainee records) we can pass this to the trusts if needed. JW to create ticket related to the deletion of posts.

19/11 - Once de-dupe is done, check the full list of People from Chris. Please review against the surviving records (IDs) onTISNEW-2057.

14/01: Wait until the testing of the de-dupe has been done on stage then carry on.- 

04/02: Awaiting on DS on testing outcome for TISNEW-2057 to prioritise TISNEW-2083. Does the script attached to 2083 has everything? 

Check with Rob about urgency of this re IG - JW to do this

01/04: to check with Rob/Ben.

15/04: response from Rob 'From an IG perspective - of we don't need it then we should not keep it.'

Supervisors and Leave approvers taken into account on Chris's script.

24/06: The South has undertaken a post audit and set to Inactive Consultant/non current posts which has resulted in a reduction of approx. 2000 posts

Suggestion that this is something that the other three regions can do while the Person de-dup is in the process of being completed.

Suspect most of the Consultant posts in the North and Mids and East are now inactive meaning that the majority of outstanding posts belong to KSS (possibly)

Once this has been completed for Posts they can remain as Inactive rather than undertake another unnecessary step.

22/07: Still waiting on the de-duplication work. To discuss about Supervisors after this piece of work.

17/02: Intrepid/Leave Manager roles to be sorted out ahead of de-dupe of consultants on TIS

20/04:

  • Aim for May 2020.

  • Action: Investigating Chris’s script, check for any leave manager records.

04/05: Report has been amended - Query saved on Data warehouse. Exclude list of Approvers before removing.

21/05: Update from the south and EM.

03/06: Waiting on Hicom.

29/06: Sue: Hicom sent over a national list (personTISIds) of ~38 approvers to exclude in the merge. (Based on anyone that has ever approved a study leave application). Some coordination with Hicom for these records. The list on Slack to go first iteration excluding these 18 records. Second iteration look at the Hicom coord. ones. Sue to check against the master file.

19/10:

Script for consultant data updated:

https://healtheducationengland.sharepoint.com/TIS/Shared Documents/Forms/AllItems.aspx?id=%2FTIS%2FShared Documents%2FJ. Data and Information%2FData Quality%2FNon Training Placements and Programmes.sql&parent=%2FTIS%2FShared Documents%2FJ. Data and Information%2FData Quality&p=true&originalPath=aHR0cHM6Ly9oZWFsdGhlZHVjYXRpb25lbmdsYW5kLnNoYXJlcG9pbnQuY29tLzp1Oi9nL1RJUy9FZTNBa3BqQXFvdEhoRmpBdkZnX04wOEI2WE1HazFrdWhFNXBuMDFMU212VWpnP3J0aW1lPWUxZEdUQ04wMkVn

19/10: Spreadsheet of grades ← James:

https://healtheducationengland.sharepoint.com/:x:/r/TIS/_layouts/15/Doc.aspx?sourcedoc={E10875EF-5AE0-4B22-B77D-4CE1A2484575}&file=IsTrainingGrades for Review RUN 15-10-2020.xlsx&action=default&mobileredirect=true

14/12: Sue has reviewed this but will be picked up in the new year.

11/01:

  • Suggestion is to wait until Accent Leave Manager migration, although that would mean re-running the script then. 08/02: Delayed beyond March 2021

  • Archiving work to follow.

  • 08/03: Expecting LM to go live in June 2021 with testing starting in April through to May

24/05: Testing of the ETL in progress. On target for end of August.

14/09: Accent Leave Manager expected go live 23/09. Go-NoGo on 16/09.

27/09: After running the above:

  • check how many remaining non training grade placements remain

  • unused posts that can be removed from TIS?

 

On hold until Point 1 has been completed to the group's satisfaction for People area

3

Post Funding with no end dates but are INACTIVE

James/All

Use the Bulk Post Funding Update tool to update them. (Note: by TIS_PostFunding_ID* not PostID)

Impact on leave manager? - @Mike.Richardson (Unlicensed) to check.

Check impact with ESR on Inactive Posts - @Ashley Ransoo / @James Harris

11/01: Post Status of CURRENT only are reconciled and placements exported against.

  • If Post Funding does not become active until at a point in Future, the status of the post has to be CURRENT 13 weeks ahead of the placement start date for them to be exported. May need to change the rule on the interface to still export the placement regardless of the post status in this case. @Ashley Ransoo / @James Harris to discuss with @Joseph (Pepe) Kelly . (confirmed)

  • OR, when a CURRENT/FUTURE placement is added to an Inactive Post, perhaps the post needs to be made CURRENT before being able to do so. (Query to check)

  • @Ashley Ransoo @Sebastian Kwok (Unlicensed) Ticket - consider to automate the Post Status changing.

    • Rules to automate TBD.

    • Consideration for a separate Funding Status? No.

    • Research into…Seb

  • 07/06:

    • Soft warning on the Funding dates being used for placement creation/edits being outside of the placement dates (UI) - ticket

    • PPT - Warning when adding placements against inactive post?

    • Bulk - Warning/info type message when adding placements against inactive post or prevent by validation?

    • What is Post status?

    • 21/06: https://hee-tis.atlassian.net/browse/TIS21-1737

 

4

New ESR Interface data to NDW for reporting

All

  • Ticket to send MongoDB to NDW?

  • https://hee-tis.atlassian.net/browse/TIS21-224

  • 24/05: The ticket is for giving access to Trusts to report on/investigate and not HEE.

  • 22/06: ‘ESR Open Data Warehouse’ available to Trusts only. Dataset includes Position ID and Assignment ID but not the TIS NPNs. Suggest sending those to NDW from TIS ESR mongo database.

    • Meeting next week to flesh out the complete dataset and reports requirement.

    • Ticket to send any additional data to NDW?

    • 27/09: Ticket in ready for development

  • 08/11: Posts reconciliation (POS/POR) to be sent to NDW. (ticket - @Ashley Ransoo )

  • 15/02: Posts reconciliation ticket refined to be build next. https://hee-tis.atlassian.net/browse/TIS21-2635

 

5

Oriel Integration

All

Oriel Workforce Extract - Programme name and number to be included (work starting Mid March and will take about 2-3 weeks), aiming to be ready for April 2021 when most of the trainees will be added.

  • Extension of the Leave Manager ETL with additional views into the NDW

  • These could be used as a basis to then convert into the TIS bulk People upload format (Volunteers to help James create the scripts: @Mike.Richardson (Unlicensed) , @Caroline Grisdale, @Mark.Oliver (Unlicensed) )

    • There will be a suite of reports including ability to compare on what’s on TIS and what yet to be uploaded on TIS.

    • CoP: by Early May the trainees to be on TIS

    • Note: currently recruitment team run report from Oriel and upload to TIS

    • Requires a change in process in Local Offices therefore probably defer to Round 2?

Oriel-TIS Integration recent and previous investigation summary | Assumptions & Discussions for Implementation

08/03: Awaiting on the first part of the development in LM

Oriel - (2020 - 2022) Discovery

12/04: Access to Hicom DR (Same location as Leave Manager V3)

24/05: Waiting on Hicom on DR3

14/09: Work blocked by corporate and Hicom finance issues. This has now been unblocked. Timescales still unknown.

27/09: This may still be blocked by finance.

08/11: In progress with Hicom.

15/02: Get an update from Hicom this afternoon.

 

6

Assessment Issues

James

https://aws-build.tis.nhs.uk/metabase/question/371 --To delete? https://hee-tis.atlassian.net/browse/TIS21-1502

https://aws-build.tis.nhs.uk/metabase/question/372 --To fix?

ID manual updates

24/05: Hard deletes for those requiring deletion. (@Joseph (Pepe) Kelly)

21/06: This has been done. Suggest closing. Assessment Bulk Update using TIS Id’s has been refined, however it was suggested we do not develop the bulk soft delete functionality via the last column on the template but a new template for bulk ‘Hard’ delete of assessments to be developed in the future similar to Placements Bulk Delete, that is hidden from the UI.

https://hee-tis.atlassian.net/browse/TIS21-1759

14/09: Front End deletion to be changed to Hard delete as well - ticket?

27/09: Looking into bring above ticket into development.

11/09: Development in progress for Bulk Update tool. (General use). Bulk Delete to be worked on after that. (Data Leads Only).

25/10: Assessments without Programme Membership Id’s - Data fix - Can use the Bulk Update tool. Curriculum ID and TIS Programme membership ID columns on the template.

@Yafang Deng / @Andy Dingley - Data leads suggestion: Rename the column ‘TIS Programme Membership ID’ on the template to ‘TIS Programme Curriculum Membership ID’.

08/11:

  • Bulk Hard delete assessments is on Production.

  • Ticket to prevent creation of duplicate assessment records by the Bulk Assessment upload. TIS21-2285

  • Question of whether we should hard delete all the soft deleted ones or make this a regular/periodical thing? - Purge all (26k)? - Discuss @ PAG. (@Sebastian Kwok (Unlicensed))

15/02: Assessments Bulk Delete template is available for data leads to use.

 

8

Site and Trust Tables

James/All

  • There are 18000 current posts that have an 'invalid' site attached to them

  • https://hee-tis.atlassian.net/browse/TIS21-134

  • 07/06:

    • Segment known Site and Trusts issues and map them to regions to facilitate clean up

    • Map Site and Trusts or Organisation Types

    • Start to define what a regular DQ report will look like

      • Comparing placement end dates and site/trust closure

      • How to slice and filter

      • Dealing with time delay of mergers aka 'grace period'.

      • Identifying successor organisations when have full ODS tables

    • GMC Approvals Data

      • ODG topic of OOPT and Site Approvals

      • Speak to GMC at ODG about more automated pull of full approvals

      • Speak to JT about which db to add Approvals data to and if possible for data service team to start regularly uploading this data via SSIS

      • Upload one off static full approvals list to start querying the data

    • 22/06: 6th July another session.

  • 06/07 Barbados:

    • GMC Approvals Data - James has uploaded a copy in HEE TIS Programme - dbo.GMC_Approved_Programmes_and_Locations

      • James will drop and reupload table on demand (monthly)

    • Segment known Site and Trusts issues and map them to regions to facilitate clean up

      • Check sites (both main and other) associated with current posts are approved for their programme - @James Harris #1

      • Look at sites (and trusts) that are closed on the ODS list but still on GMC approval list - @David Short (Unlicensed) #2

      • Check site table site trust combination matches ODS list (may not match with GMC) - @James Harris

      • Check the GMC approvals to ODS combos - @James Harris

      • Check current sites on TIS, flag ones that should be inactive from ODS and identify whether there are posts connected. - @James Harris

      • Check current trusts on TIS, flag ones that should be inactive from ODS and identify whether there are posts connected. - @James Harris

      • Check if sites on GMC approval list have no current posts for that programme (warning) - @David Short (Unlicensed)

      • NB. the above is dependent on teams removing sites that should not be approved from the GMC approval list

    • FIXED - Trust linked to a Site not updating the trustId linked to it

      • Raise ticket to recalculate trust ID associated with site

    • Map Site and Trusts or Organisation Types

      • David to find and circulate list of organisation types - done 06/07

      • Possibly missing County Councils, HEIs (universities), Local Authorities

      • Map different organisation types

      • Decide on naming convention

14/09:

25/10: Review what NDW is doing with the above with Reuben.

08/11: Next to look at its implementation in NDW then consider TIS.

 

9

Person records deletion from TIS

All

  • e.g. foundation trainees not having started, needs deleting/Archiving from IG perspective.

  • 07/06:

    • Person Records added to TIS with no programme membership after X months? → Rules to archive/remove from TIS.

      • X months = 3 months after added date

      • Look at the roles attached to the Person record - Excluding records where Leave approver, ES and CS roles are attached.

      • Look at whether it has programme memberships/placements or not

      • Any other considerations?

      • NIMDTA - Archiving? → same GDPR rules should apply. @Alistair Pringle (Unlicensed) to speak with Marc.

    • 22/06: Archiving in the NDW.

  • 15/02: Not in scope of archiving work. Foundation trainees with pending exam results after accepting an offer for a programme. Consideration for Oriel Integration work.

 

10

Local Post Number field for NI

All

 

12

Removal of Person Records Solely existing for Leave Manager

Mike/James/All

  • 14/09: In the past to create bulk ‘non-administrators’ in Intrepid Leave Manager involved creating them in bulk on TIS and allowing them to be sent across on the ETL

  • With Accent Leave Manager, only trainee records will be sent from TIS to LM as other accounts will be managed in LM

  • Need to identify and remove these person records from TIS

  • 27/09: @Mike.Richardson (Unlicensed) and @Kyle Embley (Unlicensed) start developing script to identify them.

 

13

Assessments without Programme Membership Id’s - Data fix

All

27/09: @Mike.Richardson (Unlicensed) and @Caroline Grisdale take a look into those affected and see what can be done.

Potentially may need to have a cut off date on historical records.

25/10: Mike’s investigation:  re. assessments with NULL programme IDs, the only ones I can find (going back a few years) are ones with ridiculous review dates. There are however 36 assessments in the current ARCP period (Aug 2020 to Aug 2021) than have NULL curriculum IDs, so maybe that's what we are more interested in. Most of these assessments do have a programme ID.

Caroline: re assessments with NULL programme ID's, we don't have any. However we have 15 in total that have NULL curriculum ID's

Mike to share how to get the results to other regions.

To test scenarios - Deletion of programme membership after the assessment has been created, Changing of dates.

15/02: dependent on #15 (programme membership refactoring)

 

14

Placements with:

  • same Main Site and Other site

  • same Main specialty and Other specialties

Posts with:

  • same Main Site/Other site

  • same Main specialty/Other

  • same Approved grade and Other Grades

All

Placement same Main Site and Other site:

https://build.tis.nhs.uk/metabase/question/437/notebook - 14 records on Live

15/02: No valid use case for adding validation as a priority.

 

15

Programme Membership refactoring on TIS

All

https://hee-tis.atlassian.net/browse/TIS21-2446

 

16

Changing delimiters to Semi colon on bulk uploads

All

https://hee-tis.atlassian.net/browse/TIS21-2649

Sub-specialty will be added to bulk placement- https://hee-tis.atlassian.net/browse/TIS21-2356

 

17

National Reporting Dashboard

All

Conversation with Hicom to provide the recruitment data.

https://hee-tis.atlassian.net/wiki/spaces/NTCS/pages/3354001414/National+Reporting+Requirements#Questions-/-Assumptions-for-Discussion

We’ll be looking at the reporting needs/users next.

 

18

Expansion Posts

All

‘Long Term Plan’ Funding type currently on TIS which is Tariff Funded.

Potential new funding type for ‘NHS E/I Expansion Posts’ which are also Tariff Funded.

Need a way to differentiate between established posts Funding type and type of Tariff funded types. Perhaps a new field e.g. 'Funding instance' or relabelling of the funding types to avoid confusion.

Implication of adding new field.

15/02: Add as new Funding type.

28/02:

 

19

Discrepancy between curriculum membership and Assessment curriculum

Liam/David/All

Currently it’s a snapshot at the point in time when the assessment is created. It was the expected behaviour.

Does this need to change to dynamically cascade if there is an update to the curriculum membership? - Not at the moment.

 

 

 

 

 

 

 

Jira Tickets (Reference Tables)

key summary type created updated due assignee reporter priority status resolution
Loading...
Refresh

Jira Tickets - Data Leads to Prioritise

key summary type created updated due assignee reporter priority status resolution
Loading...
Refresh

 


HEE Events Timeline - Indicative Dates