Page Content
| Table of Contents |
|---|
Background
The Oriel Workforce Extract is a file based (XML) export from Oriel of Trainee details, the programmes they've been accepted on along with documents submitted with their Oriel application. There is currently an ETL that receives those files and documents from Oriel and stores them on Azure. (check if this is still running). This extract includes Trainees that are new to TIS (e.g. Foundation trainees) and those applying for Higher Specialties. The former result in creation of new trainee records on TIS whilst the latter result in additional Programme memberships and curriculum memberships being added onto their existing TIS record.
Previously, the process on Intrepid has been been to input the curriculum and programme details via Intrepid at the point of importing them into Intrepid to create their records. The reason for this is because post and programme details are not captured on Oriel and therefore unable to do a direct mapping to the Intrepid system when transferring the Offer Accepted Oriel Applicants.
A piece of work started to look at how to automatically ingest trainees into TIS when Intrepid is retired as a system. For MVP, it was initially proposed to have a semi-automated ingestion of some of the Oriel trainee details whilst HEE Admins perform a bulk upload of trainees to TIS. We went live in April/May 2018 with a bulk upload solution as an MVP, but this did not include the semi-automated transfer of Oriel details from the Oriel Workforce Extract files. We are now reviving this piece of work in order to deliver a potential full automation of extracting and creating trainees records from Oriel to TIS. In order to achieve this there are potential changes to be done on both TIS and Oriel sides.
In December 2023, a piece of work was started by the Business Analyst and The data team to Understand the process followed in each local office for onboarding new PGDiTs from Oriel to TIS. This piece of work is to achieve an understanding of the processes followed by each local office so that all perspectives can be considered when deciding solution options for the semi-automated ingestion method for Oriel trainee details into TIS and bulk upload by HEE Admins, ensuring alignment with local office practices.
(Previous discovery) Oriel Workforce Extract on Azure
/wiki/spaces/TISDEV/pages/59051715
N3 Location
TIS uses the following share location for Oriel:
...
Service
...
Server
...
Share
...
Oriel
...
10.196.16.50
...
C:\WorkforceExtract
(Previous discovery) MVP - Proposed Business Process to Import Trainees from Oriel into TIS
26/08/2020: The below process was not fully achieved and excluded the semi-auto ingestion of Oriel trainee details from Azure extracted Oriel Files. A bulk upload tool was developed for MVP for uploading Oriel trainees which requires pre-population from Oriel Reporting tool or in some Local Office’s what is known as the Rotor tool to transform/map the Oriel data along with Reference data (e.g. Nationality, sexual Orientation etc.) to what TIS can successfully upload.
...
| Attachments | ||
|---|---|---|
|
| View file | ||
|---|---|---|
|
Current Work Contacts
TIS Contacts
Kavitha Shankar- Data Analyst - kavitha.shankar1@nhs.net
Stan Ewenike - Data Analyst - stan.ewenike@nhs.net
James Harris - Data Lead - james.harris39@nhs.net
Adewale Adekoya - Business Analyst - adewale.adekoya@nhs.net
Ashley Ransoo - Business Analyst - ashley.ransoo@nhs.net
East and West Midlands
Lauren Squires - Regional Programme Information Coordinator - Lauren.squires1@nhs.net
Yorkshire and Humber
Kay Appleyard (Main contact) - Information Manager - kay.appleyard2@nhs.net
Philip Balcomb - Data Officer - philip.balcomb@nhs.net
Sarah Hill - sarah.hill131@nhs.net
North West
Liam Lofthouse (Main contact)- Information Manger - liam.lofthouse1@nhs.net
Jane Lovelady - jane.lovelady1@nhs.net
North East
Leane Lowe - Lead Employer Trust (Senior People Services)- leanne.lowe@nhs.net
South West
Louise Peckham - Education Programme Manager – Secondary Care Medicine, IMT, & Medical Ophthalmology- louise.peckham@nhs.net
London
Stuart Morris - Service Delivery Manager- stuart.morris@nhs.net
Sarah Krause - Senior BI Analyst - sarah.krause@nhs.net
Thames Valley
Maxine Grout - Education Programme Manager - Maxine.Grout@nhs.net
Holly Gannon - Education Programme Manager - holly.gannon@nhs.net
Wessex
Jemma Martell - Education Programme Manager - Jemma.martell@nhs.net
Anna Parsons - Education Programme Manager - anna.parsons9@nhs.net
Lucy Wyatt - Education Programme Manager - anna.parsons9@nhs.net
EOE
(Current Work) High Level as-is Activity Flow
...
(Current Work) Analysis of the current Process
...
Activity
...
E&W Midlands
...
Y&H
...
NW
...
NE
...
SW
...
London
...
Wessex
...
TV
...
EOE
...
Download Records with relevant documents:
Who performs this role?
...
The recruitment Team
...
The recruitment Team
...
The recruitment Team
...
HR/Lead Employer Team runs/extract Oriel Custom Report of trainees after Offer deadline
...
The recruitment Team depending on specialty IDs supplied by the programme Teams
...
The recruitment Team
...
The recruitment Team
...
The recruitment Team
...
Download Records with relevant documents:
How many people in the LO has got access to Oriel to the full report- Super User?
...
One person in the Team has got access
...
One person in the Team has got access
...
One person in the Team has got access
...
One person in the Team has got access
...
One person in the Team has got access
...
One person in the Team has got access
...
One person in the Team has got access
...
One person in the Team has got access
...
Download Records with relevant documents:
How often is the download made?
...
The download is weekly to check any new record
...
The download is weekly to check any new record
The download is weekly to check any new record and the Data and Programme Team are both informed of new records after download
Download from Oriel and upload into TIS are not weekly. They constantly check Oriel for new starter and upload them into TIS until post is full. It depends on availability of new record(s).
There is no Weekly extract from Oriel to Upload into TIS in this region but one big upload. There might be situations where some programmes’ recruitment might still be available after deadline, then another report is pulled after the deadline of such extra recruitment.
...
The Download is automated in London
After the recruitment team saved the CSV file as XIsx file in SharePoint;
At 10pm, BI/Dev app;
Auto download the spreadsheet
Auto import the data into SQL server
At 8:30am
Auto check a Download is available
Auto Lookup data with TIS People Bulk Upload Template to align
Auto create and generate into individual specialty spreadsheet needed for each school/specialty
Auto upload template into relevant school/specialty SharePoint Folders.
...
Reports are ran based on National timeline
...
Reports are ran based on National timeline
...
Download Records with relevant documents:
Any Document downloaded with Report?
...
No document downloaded with report
...
No document downloaded with report
But the TPD do download the following which differs from school to school and documents are saved to the trainee file
an application pack
references submittedevidence of qualifications overseas
ALS certificate (speaking English to a sufficient standard).
Any additional documentation that the national recruitment team add to a particular application
...
No document downloaded with report
Data Officer import template to TIS Stage environment to test
...
No document downloaded with report
...
No document downloaded with report
...
No document downloaded with report
...
Application Pack
...
No document
...
Reports Type:
Type of reports?
...
One full report generated; Oriel Custom Report which contains information around equality, diversity, ethnicity, gender, disability
...
4 reports generated without Foundation:
Medical Report which contains most of the data
Public Health Report
Dental Foundation Report
Dental Core and specialty report
3 Foundation Reports:
YHFS TIS Export – FPP 2023 V2 (Priority Programme)
YHFS TIS Export – SFP 2023 V2 (Specialised/Academic Programme)
YHFS 2023 TIS Report FP Programme V4 (standard YH Foundation Scheme trainees)
...
One Full report is generated excluding Foundation
...
One Full report is generated excluding the foundation doctor
...
Different reports; Core, Higher, academic, Foundation, Dentist depending on the national timeline. Academic Round- Academics trainees are first pulled then Core trainees in round 1(Academic could be a bit of Round 1 because of clinical interviews), round 2 will be higher trainees and so on depending on the timeline.
...
Different reports; Core, Higher and academic. No Foundation and GP in the report
...
One full report generated. Reports generated by the recruitment team includes Foundation Doctors Records which follows the same process.
...
GP: Extract a Report without sensitive data and get sensitive data later
Foundation and other specialties: One full Report
Reports Type:
What format?
...
CSV
...
CSV
CSV
...
CSV
...
CSV
CSV files but save as XIsx file in SharePoint
...
CSV
...
CSV
...
Reports Type:
Where is the report saved?
...
SharePoint
...
SharePoint
...
SharePoint
...
SharePoint
...
SharePoint
...
SharePoint
...
SharePoint
...
SharePoint
Mapping of reports:
Who does the mapping?
...
The LO Admin
...
Data Analyst - The recruitment Team notify the Data analyst moment the reports are downloaded from Oriel
...
Data Officer
...
HR/Lead Employer Team
...
Programme Team
...
HET Team.
Programme/Data Focus Admins for each School/Specialty
...
Education Programme Teams (GP, Foundation and other specialties
...
Education Programme Teams (Foundation and other specialties
Mapping of reports:
Link to process
...
See link to process
...
See link to process
...
See link to process
...
See link to process
...
See link to process
...
See link to process
...
See link to process
...
See link to process
Mapping of reports
What template is use?
...
Oriel Custom Report/ Recruitment Import Template
...
Export Data file>Import Data file
Recruitment Spreadsheet
...
New Starter Template (Mirror of TIS people Bulk Template)
...
Other Programmes:
CSV Report template
Foundation:
Downloaded TIS People Bulk Template
...
Specialty Spreadsheet template (Mirror of TIS people Bulk Template)
...
Foundation
Downloaded TIS People Bulk Template
GP:
GP Template (Not TIS template
Other Programme
Downloaded TIS People Bulk Template / Own Template: Manually create Record using CSV Report
...
Own Template: Manually create Record using CSV Report
Mapping of reports:
What software/app is used?
...
No identified software
Note:
LO split those without GMC/GDC number from Oriel and check on TIS to ensure any duplicate record is not created. Foundation and Dental, International Medical Graduates (IMGs) foundation are most likely not to have these numbers.
TISid are added to keep track when manually adding.For the second round of upload, already upload/created records are removed before new upload is done
...
Software - VBA/sql script /snag to identify anomalies
...
Uses a series of formulas and look up tables, which should hopefully make things a little easier to understand for them
Note
Please note that some schools in the region do the people bulk upload into TIS themselves but the Data/Information Team will be informed.The Data/Information Team will still have to upload the sensitive data using the People Bulk Update Template in TIS
...
No app/software
No app/software
...
There is an Auto App
...
No app/software
...
No app/software
...
Bulk Upload data Person Record to TIS:
Is TIS People Bulk Template used?
...
Yes
...
Yes
...
Yes
...
Yes
...
Yes
...
Yes
...
Yes and No
Most Programme manually create record on TIS
...
No
Manually create Record in TIS
...
Bulk Upload data Person Record to TIS:
Who Upload to TIS
...
Admin
...
Data Analyst
...
Data Officer
...
Lead Employer Team/HR
...
Programme Team
...
Data Focus Admins for each School/Specialty in HET
...
Education Programme Teams (GP, Foundation and other specialties)
...
Education Programme Teams (Foundation and other specialties)
...
Bulk Upload data Person Record to TIS:
What Template?
...
Use the People Bulk Upload Template/recruitment Import template
...
Use the People Bulk Upload Template/Export Sheet/Import Sheets
...
Use the People Bulk Upload Template/Recruitment Template
...
Use the People Bulk Upload Template
...
Use the People Bulk Upload Template
...
Use the People Bulk Upload Template
...
Foundation:
TIS Bulk Upload
GP:
Own Template
Other Programmes:
TIS Bulk Upload and Own spreadsheet (Most use)
...
Foundation
Own Template
Other specialties
Own Template
...
Bulk Upload/Upload Placement:
Is TIS template in use to add Placement?
...
Yes
...
Yes and No
Some manually add to TIS
...
Yes
...
Yes
...
Yes
...
Yes and No
No for GP
...
Yes and No
Most Programme manually create record on TIS
...
No
Manually create Record in TIS
...
Bulk Upload/Upload Placement:
Who add placement
...
Programme Admin
...
Data Analyst
...
Programme teams/Schools
Lead Employer Team/HR
...
Programme Admin/ Specialties manager
...
Education Programme Teams
...
Education Programme Teams
...
Bulk Upload/Upload Placement:
What Template?
...
TIS Bulk placement template
...
TIS Bulk placement template
Manually Add placement when trainee records are created
...
TIS Bulk placement template
...
TIS Bulk placement template
...
Bulk placement template
...
TIS Bulk placement template
GP manually upload placement
...
Foundation:
TIS Placement Template
GP
Own Template
Other Programme
Most uses own template
...
Own Template
...
Is it the same process for Foundation and other specialties?
...
Yes with few difference
UKFPO and GPNRO do provide some reports for foundation and GP recruitment directly to local teams, but it is often not enough for them to work off of, so they typically wait for our process even if the national recruitment offices sent in information earlier.
No.
Foundation Process is mapped
See Foundation Process
...
No
To confirm the process for Foundation. Contact for Foundation doctor: julian.shepherd3@nhs.net
See Foundation Process
...
Yes, follow the same process
...
No
Foundation; Spoken to MADDOCK, Suzanne (NHS ENGLAND - T1510) suzanne.maddock2@nhs.net
See Foundation Process
To speak to GP; SLOMAN, Richard (NHS ENGLAND - T1510) richard.sloman@nhs.net, Helena Horton helena.horton1@nhs.net and Elizabeth Alden elizabeth.alden2@nhs.net
...
Yes
Reports generated by the recruitment team includes Foundation Doctors Records which follows the same process
...
No
See Foundation swimlane in the Process
...
No?
To contact George Fahey
...
Pains/Challenges
...
Lot of manual mapping of data to suit TIS Language
...
In 2022 the Foundation team confirmed that the ‘Disability’ field in Oriel had been replaced by ‘Day to day activities limited?’ this should be uploaded to the Disability field on TIS as part of the import.
TIS and Oriel not speaking the same Language
...
Pain Point form Other Specialties:
Data Fields alignment between Oriel and TIS
Column structure do change all the time from Oriel
Pain Points from Foundation
In-house management of TIS People Bulk Upload and moving data from Oriel into TIS is exhaustive. Last year with FP and SFP we had 964 people to set up in the NW, with six posts each = 5784 manual entries. As we usually only have one person looking after this, particularly with staffing resources pressures ongoing this is a pretty big task, given they are managing other workstreams at the same time and has often meant we have uploaded late or been working uncomfortably close to the ESR pull though deadline in July
Long time to align correctly programme details for each to on two-year programme. In adding of programme detail, each doctor is on the two-year programme rotates through six different posts. Each post/location has an assigned post number which links to the agreed contract with NHSE. When a doctor is matched to a programme on Oriel they are matched to a ‘track’ which is numbered and it dictates the specialty rotation order they will undertake
Due to the historic nature and randomised fashion of how post numbers have been utilised and linked to posts on TIS this means that we currently have to manually enter on the programme detail upload template which particular post number the doctor is going into
Currently we are not able to add the track number from Oriel onto the contract, to enable us to simplify the linking of the specific post number on the contract (potentially allowing use of a vlook up) to an individual and a dated position within TIS.
...
Pain Point
Not all of the data are pulled from Oriel because they are missing in Oriel
Fields not matching with TIS fields
Programme and Curriculum details depends on Team experience and TIS as it’s not clearly stated in Oriel.
...
Pain Point
Oriel- TIS does not talk the same language around some data fields e.g Ethnic origin, Gender, Sexuality, Religion, Nationality etc. Louise to send list. Programme team have to amend these fields before uploading to TIS . This impact the deadline dates in the code of practice
One person per region has access to pull data from Oriel
90% work on the full report file but some specialties create their own files to work on separately
...
Pain Points
Meeting CoP deadlines
Manually adding Programme, rotation and curriculum
Changing Oriel language to suit TIS languages
...
Pain Points
Single point of failure to download reports
TIS template not user friendly
TIS and Oriel not speaking the same Language
...
Single point of failure to download reports
TIS template not user friendly
TIS and Oriel not speaking the same Language
...
Other Comments
...
See body of the process
...
See body of the process
...
See body of the process
...
See body of the process
...
See body of the process
...
See body of the process
...
See body of the process
...
See body of the process
(Current Work) Pain Point Summary
...
Theme
...
Pain Point
...
Note
...
Oriel - TIS fields Mapping
...
Lot of manual mapping of data to suit TIS Language; TIS and Oriel not speaking the same Language;e.g Ethnic origin, Gender, Sexuality, Religion, Nationality etc. Louise to send list. Programme team have to amend these fields before uploading to TIS . This impact the deadline dates in the code of practice.
Management of TIS People Bulk Upload and moving data from Oriel into TIS is exhausting.
Some LOs split/separate Records without GMC/GDC from the report and some LO keep them in the same report and complete the Field as ‘UNKNOWN’ or ‘0’ before the template is uploaded to TIS. Foundation and Dental, International Medical Graduates (IMGs) foundation are most likely not to have these numbers.
...
Visa and Qualification
...
For Foundation- The Sponsorship team do ask LOs to remove all Work Permit & Visa details because the information from Oriel is out of date by the time trainees are starting and the Sponsorship team have more up to date information. This is added later either by the team or LOs will add via an import sheet sent to them by the team.
For Foundation- Qualification details are also added at a later date (using another import). Y&H do have an SQL script that takes Qualification from the GMC but only currently works for trainees with a TIS record. This is because qualifications on Oriel don’t match those held by the GMC and any subsequent reporting looks messy.
...
Oriel Fields and data
...
Column structure do change all the time from Oriel e.g In 2022 the Foundation team confirmed that the ‘Disability’ field in Oriel had been replaced by ‘Day to day activities limited?’
Not all of the data are pulled from Oriel because they are missing in Oriel.
Oriel contains data that are irrelevant to TIS.
No clear fields for programme details and curriculum in Oriel.
...
Rotation; Programme and Curriculum
...
For Foundation specifically: Long time to align correctly programme details for each to on two-year programme. In adding of programme detail, each doctor is on the two-year programme rotates through six different posts. Each post/location has an assigned post number which links to the agreed contract with NHSE. When a doctor is matched to a programme on Oriel they are matched to a ‘track’ which is numbered and it dictates the specialty rotation order they will undertake, Due to the historic nature and randomised fashion of how post numbers have been utilised and linked to posts on TIS this means that we currently have to manually enter on the programme detail upload template which particular post number the doctor is going into
Programme and Curriculum details depends on Team experience and TIS as it’s not clearly stated in Oriel.
Manually adding Programme, rotation and curriculum
Difficult to find programme and curriculum details in Oriel
...
Post Number and Oriel Reference Number
...
Currently we are not able to add the track number from Oriel onto the contract, to enable us to simplify the linking of the specific post number on the contract (potentially allowing use of a vlook up) to an individual and a dated position within TIS.
...
Access to Oriel
...
One person (Super user) per region has access to pull full data from Oriel (Full data includes sensitive data)
Other users of Oriel have limited access to information.
90% of specialties work on the full report file but some specialties create their own files to work on separately and then get the sensitive data from the super user later to add to TIS
...
Reports creation
...
In some LO two reports are created; one for the Admin/Data team and the other for the programme Team
Some LOs generate one full report for upload and some generate more two or more reports; full report which include Foundation, Full report which exclude Foundation, Foundation report, Different reports; Core, Higher, academic, Foundation etc
...
TIS and LO Spreadsheet Templates
...
TIS templates not user friendly, Spelling errors and other warnings makes them frustrating to use
Each LO have it’s own template to mane work e.g to keep record of those already on TIS , other issues with the upload and trainees
...
CoP Deadline
...
Because of the above pain points meeting CoP deadlines is very challenging.
(Current Work) Options
...
#
...
Option
...
Description
...
Impact
...
Possible Dependency
...
Decisions/Recommendations
...
Comments
...
1
...
Do Nothing
...
All existing pain points remains
Minimum resource required by TIS Admin
...
2
...
Filter and Enrich Oriel Data with Timestamps
...
The Data Service pulls the Oriel TIS Extract from Hicom’s Data Repository on a regular basis. This data includes all faculties and nations. Proposal would be for the Data Service Team to create a subset of that data for only that relevant to TIS and to enrich the data with a timestamp, for example, and other elements to track changes.
...
Currently Oriel data is highly restricted due to the amount of records not pertinent to TIS. Creating a subset would allow for giving greater access to the data in the NDW. Enriching the data would allow for the tracking and filtering of the data based on its creation, update, delete, etc.
...
NDW team availability
...
Development Done by NDW
...
3
...
Make Oriel Data Available in Tableau in Two Locations with Different User Group Permissions
...
Local Offices currently have one person who has access to the Oriel data with the EDI fields. This Oriel download is split and shared with different audiences. A folder in the Tableau TIS PID site would be created where only a certain list of users could access. A Tableau workbook would be created with all pertinent Oriel data including the restricted EDI fields. A second Tableau workbook would be created with a restricted list of fields in the main TIS PID site giving wider access.
...
This would remove the dependency on the one person in each LO running the data from Oriel but still meeting IG requirements. Having the two workbooks would automate the need for the data to be sliced for different audiences.
...
Fields Standardisation- restricted and Non-restricted fields
Standardising the audiences e.g TPDs . Might depend on audiences these reports goes to
...
Reporting only development
...
4
...
Oriel-TIS Data Comparison Report
...
Given both Oriel data and TIS data are in the NDW, that data can be compared to show both which records have and have not been uploaded to TIS as well as any Data Quality issues in the uploads.
...
Currently Local Offices have to use various spreadsheets, look-ups, SSIS packages, etc, to keep track of which records have been uploaded and which have not, or local offices wait until all Oriel deadlines have passed before uploading them to TIS. This would automate this tracker. It would also improve data quality by flagging any anomalies.
...
Options 2, 3 and 7
possible when GMC/GDC/PH number is known
Reference Data align with Oriel and TIS
...
Reporting only development
...
5
...
Create Tableau Report which Mirrors Person Bulk Upload and Maps Data accordingly.
...
Mapping data from Oriel to the TIS Bulk Upload is the most complicated process Local Offices do for on-boarding. The report would do this mapping in the correct format where possible. Where TIS fields cannot be automatically mapped from Oriel data this will be presented to the admin in a consumable way.
...
This would greatly reduce the time admins spend manually mapping data into the TIS Person Upload
...
Option 7? To decide when we define scope
Option 3 - Need a space in Tableau, EDI fields etc
Define scope of this piece of work- Define the minimum and Option 5 e.g exclude programme and placement details.
...
Reporting only development
Given the current nature of Oriel data structure this would be very complicated to get right and will still require manual input in some/many instances.
...
6
...
Ensure TIS ProgrammeName and TIS ProgrammeNumber is added to Oriel VacancyOffers
...
TIS ProgrammeName and ProgrammeNumber fields were added to TIS in 2020 to allow Oriel data to be mapped automatically to TIS. Without access to Oriel data in the format required until 2023 that data has not been maintained. The proposal would be to ensure future vacancies have that information populated.
...
This would in theory allow for the majority if not all Oriel data to be mapped to the TIS Person Upload automatically. This would potentially allow for a fully automated process in future.
...
Make Mandatory in Oriel
Oriel BAU agreement
Time delay when data are added to Oriel- to start doing development like May for Aug. 2025
...
Given the timescales around Oriel vacancies, this would take over a year to implement. For August 2025 new starters this would need to be added on to Oriel by October 2024. If this is missed, it would not have an impact to August 2026.
...
7
...
Ensure LoV alignment between Oriel and TIS
...
This would be to ensure the TIS reference table lists of values match those used by applicants on Oriel while applying.
...
If these values do not align it adds a considerable amount of manual work for admins to do when uploading data from Oriel to TIS.
...
Meeting with Oriel Project Manager - Alice set
...
This process has already been discussed and partially actioned.
...
8
...
Create an interface within TIS for Oriel data to be mapped and uploaded
...
Rather than using Tableau and bulk upload spreadsheets an interface in TIS would be created where Oriel data was surfaced and Local Offices added any missing data required before the equivalent records being created on TIS.
...
This would make the whole this an in-app process without the need to use multiple systems. Having a TIS based interface could improve the UX and potential errors.
...
Large resource required
...
This would require considerable development resource. Oriel data is currently held in Hicom’s Data Repository rather than being accessible via an API.
...
9
...
Use Oriel Procurement to Develop an Integrated Process/System
...
A 3rd Party Discovery for the procurement of a Recruitment System is concluding (as of January 2024). It was flagged as a requirement in this discovery to smooth the flow of data between TIS and Oriel or its equivalent. A more integrated solution could be created as part of this process.
...
This could potentially allow for more, improved options around the transfer of data between these two systems.
...
Use Oriel procurement - TIS don’t have control over
...
Timescales are unknown.
...
10
...
Use Oriel Data to Automatically Insert or Update TIS Person Record Only
...
Accurate mapping of Oriel data to TIS ProgrammeMemberships, CurriculumMemberships and Placements can be quite complex and often involves experience of administrators to do. These areas could be ignored and instead a person record could be automatically created in TIS where there is not existing matched record transferring all available person fields from Oriel. Where a matched record does exist relevant fields could be updated.
...
This would save time creating or updating Person records where Oriel to TIS data can be mapped accurately. It then save administrators the time to focus on areas where their expertise is required, e.g. ProgrammeMemberships, CurriculumMemberships and Placements. These could then be added on the frontend as some administrators prefer or through bulk uploaded with far fewer fields required.
Option 7
Fields Alignment
...
Would require TIS development. Would still leave a lot of ‘manual’ work to the administrators.
(Current Work) Discussion and Assumptions
...
#
...
Question
...
Comment
...
Owner
...
Jira Ticket
...
1
...
Mapping and Analysing LOs process
...
This ticket was raised by the TIS data team. Mapping sessions was carried out by the BA and the TIS data Team with each LO
...
Data Lead
...
| Jira Legacy | ||||||
|---|---|---|---|---|---|---|
|
Page Content
| Table of Contents |
|---|
Items Definition
Items | Definition | Comment |
|---|---|---|
Trainee Records | ||
Programme | ||
Curriculum | ||
Placement | ||
Rotation | ||
Specialty | ||
Core Specialty | ||
Academic Specialty | ||
Higher Specialty | ||
Foundation |
Background
The Oriel Workforce Extract is a file based (XML) export from Oriel of Trainee details, the programmes they've been accepted on along with documents submitted with their Oriel application. There is currently an ETL that receives those files and documents from Oriel and stores them on Azure. (check if this is still running). This extract includes Trainees that are new to TIS (e.g. Foundation trainees) and those applying for Higher Specialties. The former result in creation of new trainee records on TIS whilst the latter result in additional Programme memberships and curriculum memberships being added onto their existing TIS record.
Previously, the process on Intrepid has been been to input the curriculum and programme details via Intrepid at the point of importing them into Intrepid to create their records. The reason for this is because post and programme details are not captured on Oriel and therefore unable to do a direct mapping to the Intrepid system when transferring the Offer Accepted Oriel Applicants.
A piece of work started to look at how to automatically ingest trainees into TIS when Intrepid is retired as a system. For MVP, it was initially proposed to have a semi-automated ingestion of some of the Oriel trainee details whilst HEE Admins perform a bulk upload of trainees to TIS. We went live in April/May 2018 with a bulk upload solution as an MVP, but this did not include the semi-automated transfer of Oriel details from the Oriel Workforce Extract files. We are now reviving this piece of work in order to deliver a potential full automation of extracting and creating trainees records from Oriel to TIS. In order to achieve this there are potential changes to be done on both TIS and Oriel sides.
In December 2023, a piece of work was started by the Business Analyst and The data team to Understand the process followed in each local office for onboarding new PGDiTs from Oriel to TIS. This piece of work is to achieve an understanding of the processes followed by each local office so that all perspectives can be considered when deciding solution options for the semi-automated ingestion method for Oriel trainee details into TIS and bulk upload by HEE Admins, ensuring alignment with local office practices.
(Previous discovery) Oriel Workforce Extract on Azure
/wiki/spaces/TISDEV/pages/59051715
N3 Location
TIS uses the following share location for Oriel:
Service | Server | Share |
|---|---|---|
Oriel | 10.196.16.50 | C:\WorkforceExtract |
(Previous discovery) MVP - Proposed Business Process to Import Trainees from Oriel into TIS
26/08/2020: The below process was not fully achieved and excluded the semi-auto ingestion of Oriel trainee details from Azure extracted Oriel Files. A bulk upload tool was developed for MVP for uploading Oriel trainees which requires pre-population from Oriel Reporting tool or in some Local Office’s what is known as the Rotor tool to transform/map the Oriel data along with Reference data (e.g. Nationality, sexual Orientation etc.) to what TIS can successfully upload.
...
| Attachments | ||
|---|---|---|
|
| View file | ||
|---|---|---|
|
Current Work Contacts
TIS Contacts
Kavitha Shankar- Data Analyst - kavitha.shankar1@nhs.net
Stan Ewenike - Data Analyst - stan.ewenike@nhs.net
James Harris - Data Lead - james.harris39@nhs.net
Adewale Adekoya - Business Analyst - adewale.adekoya@nhs.net
Ashley Ransoo - Business Analyst - ashley.ransoo@nhs.net
East and West Midlands
Lauren Squires - Regional Programme Information Coordinator - Lauren.squires1@nhs.net
Yorkshire and Humber
Kay Appleyard (Main contact) - Information Manager - kay.appleyard2@nhs.net
Philip Balcomb - Data Officer - philip.balcomb@nhs.net
Sarah Hill - sarah.hill131@nhs.net
North West
Liam Lofthouse (Main contact)- Information Manger - liam.lofthouse1@nhs.net
Jane Lovelady - jane.lovelady1@nhs.net
North East
Leane Lowe - Lead Employer Trust (Senior People Services)- leanne.lowe@nhs.net
South West
Louise Peckham - Education Programme Manager – Secondary Care Medicine, IMT, & Medical Ophthalmology- louise.peckham@nhs.net
London
Stuart Morris - Service Delivery Manager- stuart.morris@nhs.net
Sarah Krause - Senior BI Analyst - sarah.krause@nhs.net
Thames Valley
Maxine Grout - Education Programme Manager - Maxine.Grout@nhs.net
Holly Gannon - Education Programme Manager - holly.gannon@nhs.net
Wessex
Jemma Martell - Education Programme Manager - Jemma.martell@nhs.net
Anna Parsons - Education Programme Manager - anna.parsons9@nhs.net
Lucy Wyatt - Education Programme Manager - anna.parsons9@nhs.net
EOE
(Current Work) Draft Presentation Slide
| View file | ||
|---|---|---|
|
(Current Work) High Level as-is Activity Flow
| Drawio sketch | ||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
(Current Work) Analysis of the current Process
Activity | E&W Midlands | Y&H | NW | NE | SW | London | Wessex | TV | EOE | |
Download Records with relevant documents: Who performs this role? |
|
|
|
|
|
|
|
| ||
Download Records with relevant documents: How many people in the LO has got access to Oriel to the full report- Super User? |
|
|
|
|
|
|
|
| ||
Download Records with relevant documents: How often is the download made? |
|
|
|
|
|
After the recruitment team saved the CSV file as XIsx file in SharePoint; At 10pm, BI/Dev app;
At 8:30am
|
| Reports are ran based on National timeline | ||
Download Records with relevant documents: Any Document downloaded with Report? |
|
|
|
|
|
|
|
| ||
Reports Type: Type of reports? |
| 4 reports generated without Foundation:
3 Foundation Reports:
|
|
|
|
|
|
| ||
Reports Type: What format? |
|
|
|
|
|
|
|
| ||
Reports Type: Where is the report saved? |
|
|
|
|
|
|
|
| ||
Mapping of reports: Who does the mapping? |
|
|
|
|
|
|
|
| ||
Mapping of reports: Link to process |
|
|
|
|
|
|
|
| ||
Mapping of reports What template is use? |
|
|
|
| Other Programmes:
Foundation:
|
| Foundation
GP:
Other Programme
|
| ||
Mapping of reports: What software/app is used? |
Note:
|
|
Note
|
|
|
|
|
| ||
Bulk Upload data Person Record to TIS: Is TIS People Bulk Template used? |
|
|
|
|
|
|
|
| ||
Bulk Upload data Person Record to TIS: Who Upload to TIS |
|
|
|
|
|
|
|
| ||
Bulk Upload data Person Record to TIS: What Template? |
|
|
|
|
|
| Foundation:
GP:
Other Programmes:
| Foundation
Other specialties
| ||
Bulk Upload/Upload Placement: Is TIS template in use to add Placement? |
|
|
|
|
|
|
|
| ||
Bulk Upload/Upload Placement: Who add placement |
|
|
|
|
|
|
|
| ||
Bulk Upload/Upload Placement: What Template? |
|
|
|
| Bulk placement template |
| Foundation:
GP
Other Programme
|
| ||
Is it the same process for Foundation and other specialties? |
|
|
|
|
|
|
|
| ||
Pains/Challenges |
|
| Pain Point form Other Specialties:
Pain Points from Foundation
| Pain Point
| Pain Point
| Pain Points
| Pain Points
|
| ||
Other Comments | See body of the process | See body of the process | See body of the process | See body of the process | See body of the process | See body of the process | See body of the process | See body of the process |
(Current Work) Pain Point Summary
Theme | Pain Point | Note |
|---|---|---|
Oriel - TIS fields Mapping |
| |
Visa and Qualification |
| |
Oriel Fields and data |
| |
Rotation; Programme and Curriculum |
| |
Post Number and Oriel Reference Number |
| |
Access to Oriel |
| |
Reports creation |
| |
TIS and LO Spreadsheet Templates |
| |
CoP Deadline |
|
(Current Work) Options
# | Option | Description | Impact | Possible Dependency | Decisions/Recommendations | Comments |
|---|---|---|---|---|---|---|
Filter and Enrich Oriel Data with Timestamps | The Data Service pulls the Oriel TIS Extract from Hicom’s Data Repository on a regular basis. This data includes all faculties and nations. Proposal would be for the Data Service Team to create a subset of that data for only that relevant to TIS and to enrich the data with a timestamp, for example, and other elements to track changes. | Currently Oriel data is highly restricted due to the amount of records not pertinent to TIS. Creating a subset would allow for giving greater access to the data in the NDW. Enriching the data would allow for the tracking and filtering of the data based on its creation, update, delete, etc. |
| Priority 1 - In progress | Development Done by NDW | |
Make Oriel Data Available in Tableau in Two Locations with Different User Group Permissions | Local Offices currently have one person who has access to the Oriel data with the EDI fields. This Oriel download is split and shared with different audiences. A folder in the Tableau TIS PID site would be created where only a certain list of users could access. A Tableau workbook would be created with all pertinent Oriel data including the restricted EDI fields. A second Tableau workbook would be created with a restricted list of fields in the main TIS PID site giving wider access. | This would remove the dependency on the one person in each LO running the data from Oriel but still meeting IG requirements. Having the two workbooks would automate the need for the data to be sliced for different audiences. |
| Priority 2 | Reporting only development | |
Oriel-TIS Data Comparison Report | Given both Oriel data and TIS data are in the NDW, that data can be compared to show both which records have and have not been uploaded to TIS as well as any Data Quality issues in the uploads. | Currently Local Offices have to use various spreadsheets, look-ups, SSIS packages, etc, to keep track of which records have been uploaded and which have not, or local offices wait until all Oriel deadlines have passed before uploading them to TIS. This would automate this tracker. It would also improve data quality by flagging any anomalies. |
| Priority 4 | Reporting only development | |
Create Tableau Report which Mirrors Person Bulk Upload and Maps Data accordingly. | Mapping data from Oriel to the TIS Bulk Upload is the most complicated process Local Offices do for on-boarding. The report would do this mapping in the correct format where possible. Where TIS fields cannot be automatically mapped from Oriel data this will be presented to the admin in a consumable way. | This would greatly reduce the time admins spend manually mapping data into the TIS Person Upload |
| Priority 3 | Reporting only development Given the current nature of Oriel data structure this would be very complicated to get right and will still require manual input in some/many instances. | |
Ensure TIS ProgrammeName and TIS ProgrammeNumber is added to Oriel VacancyOffers | TIS ProgrammeName and ProgrammeNumber fields were added to TIS in 2020 to allow Oriel data to be mapped automatically to TIS. Without access to Oriel data in the format required until 2023 that data has not been maintained. The proposal would be to ensure future vacancies have that information populated. | This would in theory allow for the majority if not all Oriel data to be mapped to the TIS Person Upload automatically. This would potentially allow for a fully automated process in future. |
| Priority 6 | Given the timescales around Oriel vacancies, this would take over a year to implement. For August 2025 new starters this would need to be added on to Oriel by October 2024. If this is missed, it would not have an impact to August 2026. | |
Ensure LoV alignment between Oriel and TIS | This would be to ensure the TIS reference table lists of values match those used by applicants on Oriel while applying. | If these values do not align it adds a considerable amount of manual work for admins to do when uploading data from Oriel to TIS. | Meeting with Oriel Project Manager - Alice set | Priority 5 | This process has already been discussed and partially actioned. | |
Create an interface within TIS for Oriel data to be mapped and uploaded | Rather than using Tableau and bulk upload spreadsheets an interface in TIS would be created where Oriel data was surfaced and Local Offices added any missing data required before the equivalent records being created on TIS. | This would make the whole this an in-app process without the need to use multiple systems. Having a TIS based interface could improve the UX and potential errors. |
| This would require considerable development resource. Oriel data is currently held in Hicom’s Data Repository rather than being accessible via an API. | ||
Use Oriel Procurement to Develop an Integrated Process/System | A 3rd Party Discovery for the procurement of a Recruitment System is concluding (as of January 2024). It was flagged as a requirement in this discovery to smooth the flow of data between TIS and Oriel or its equivalent. A more integrated solution could be created as part of this process. | This could potentially allow for more, improved options around the transfer of data between these two systems. |
| Timescales are unknown. | ||
Use Oriel Data to Automatically Insert or Update TIS Person Record Only | Accurate mapping of Oriel data to TIS ProgrammeMemberships, CurriculumMemberships and Placements can be quite complex and often involves experience of administrators to do. These areas could be ignored and instead a person record could be automatically created in TIS where there is not existing matched record transferring all available person fields from Oriel. Where a matched record does exist relevant fields could be updated. | This would save time creating or updating Person records where Oriel to TIS data can be mapped accurately. It then save administrators the time to focus on areas where their expertise is required, e.g. ProgrammeMemberships, CurriculumMemberships and Placements. These could then be added on the frontend as some administrators prefer or through bulk uploaded with far fewer fields required. |
| Would require TIS development. Would still leave a lot of ‘manual’ work to the administrators. | ||
(Current Work) Issues/Questions/Feedback/Discussion and Assumptions
# | Question/Feedback/Issue | Description | Who raised it? | Owner | Jira Ticket | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Mapping and Analysing LOs process | This ticket was raised by the TIS data team. Mapping sessions was carried out by the BA and the TIS data Team with each LO 20/05/2024: Closed | Data Lead |
| |||||||||
2 | ‘Offer Accepted’ in more than one region | Investigate why PersonApplicationIDs have multiple rows of OfferAccepted in different regions. For PersonApplicationID 16167B26-EF31-44B8-AD64-6001EC9D0DCA there are two records with ApplicationStatus of OfferAccepted for the VacancyID GPNRO/24-25/3864/800/1-R1A, one is in the Northwest and the other in the West Midlands. 20/05/2024: Oriel Team raised with Hicom and no update from Hicom. Action: To be part of discussion around minimum dataset 23/05/2024: Met with Oriel Team. They will get an update from Hicom on this issue. | Lauren Squires | ||||||||||
3 | Address lines 2 and 3 having validation in Bulk Upload template | This is not the case in the UI yet 20/05/2024: Option1 - Oriel to make a change Option 2 : James to write an sql change Option 3: Do something on TIS bulk upload? Action: James Harris James to raise with Oriel Team for option 1 and if no appetite, then options 2 or 3 23/05/2024: Met with Oriel Team. Option 1 is not currently possible but may be in the future. Will action Option 2 and investigate Option 3 in the medium term. | |||||||||||
Request for the following fields to be added
Should Public health applicant type be added to the list? | OfferPreference is different to OfferDescription. This field allows for users to know the difference between rotation zones, e.g. GP Programmes in the Midlands if they are North/South/etc. Lauren: Only relevant for PH, but it determines whether they've been appointed to the Medical or Non-Medical route. James Response: We can but I'll wait to here an update on the above first as I am concerned giving a series of new requests before the others are completed will slow the above down. Lauren: That's fine, I'm not sure if other regions split these out by programme so it might just be the Midlands that cares about that information, I already have the information for this first intake and there's not likely to be much change in applicants so not urgent for being able to use the data for Round 2 like some of the other requested fields 20/05/2024: Oriel Team raised with Hicom and no update from Hicom. Action: To be part of discussion around minimum dataset 23/05/2024: Met with Oriel Team. They will get an update on those already requested and submit those not as an additional request. | James Harris Lauren Squires |
| ||||||||||
Oriel-TIS Data Mapping to person upload template: Some records having Visitor, visa / PLAB visa / Business Visitor visa' in the permit to work column. Does anyone know what the suitable TIS value is? | Mike Richardson Response: None of the values on the dropdown seem appropriate for that. James Response: I think Skilled worker is probably correct but I can check with Gemma Walker who is the lead for OST. Is that the only value in question? Andrew’s Response: That's the only one on my particular download that didn't upload and on checking have an obvious other value. Having a google over lunch it appears most people can visit the UK for 6 months without requiring a VISA, and that includes allowing them to do to things like the PLAB exam. So they probably don't actually have or have a need for a VISA, at the point of application anyway. They will likely need one now I imagine as they've been appointed, but that will be different to that value on their application form. James Response: That doesn't really seem like a 'Permit to work' as it's a visitor visa Gemma has confirmed that leaving it blank would be the best option as they will not be on a visitor visa when they actually start. James: Made PersonalImmigrationStatus out as blank for 'Visitor visa / PLAB visa / Business Visitor visa' in the SQL 20/05/2024: Closed | Andrew Horton | |||||||||||
Are the values on TIS going to be updated, to be in line with Oriel (assuming they are using the up to date list)? | James Harris Response : Oriel is not actually the best list. I went through this with the lead for the Overseas Sponsorship Team and the TIS list is accurate. The misalignment on this specific table/value keeps happening and is quite frustrating 20/05/2024: Already written in the script. To also be part of the minimum data set discussion. | Jenny Albon | |||||||||||
Nationality issue | James Harris Response :That is definitely in the works.I have raised the question with Gemma. 20/05/2024: A bug ticket is raised. Action: Admin team to prioritise ticket | Lauren Squires |
| ||||||||||
Person Upload Template: add a column for programme membership between programme number and programme end date. Even if it's just a placeholder column, it aligns everything up better then to allow a copy and paste across all the columns | James: Yes will do! Missed that column as the intention was to have at least a placeholder for all columns. For adding the Programme Membership Type field, is there any harm in putting that in as Substantive as doesn't Military come through a different recruitment route? James Added 'Programme Membership Type' placeholder column to match the Person Upload Template in the SQL 20/05/2024: Closed | Andrew Horton | |||||||||||
Offers made offline which do not end up showing as 'offer accepted' on Oriel. Not sure how that's going to work when it comes to being able to rely on this data, but hopefully is only small numbers? | 20/05/2024: Is it not mandatory to use Oriel? Action: To be discussed with Claire Wright 23/05/2024: Met with Oriel Team. There is no functionality in Oriel to handle individual appointments outside the main recruitment process due to errors and specific circumstance. This means there will always be a few in the current Oriel system. This has been noted as a requirement for future Oriel systems. | Lauren Squires |
(Previous discovery) Discussion and Assumptions
# | Question | Comment | Owner | Jira Ticket | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | In the workbook on sub heading 3, there are fields that are reference data from Oriel and require further data mapping to be done. | Agreed that wherever possible we will adopt Oriel ref tables to avoid having differences and having to map the data. Ashley to check impact on ESR interface to determine which tables we can do this for. Agreed that nationality will have to align with ESR as data passes through interface so this must be mapped. | Data Leads |
| ||||||||
2 | In the workbook on sub heading 3, there are fields that are not necessarily mandatory on Oriel and would therefore only pull through if the applicant/trainee has provided the information on their application form, e.g. National Insurance number, Religious belief, Sexual Orientation etc. | Recommendation is that if completed in Oriel that it is pulled through into TIS. | Data Leads |
| ||||||||
3 | In the workbook on sub heading 3, there are fields that are on the Recruitment Import template but not Oriel. Which of these fields are required in the template and where would these be sourced from? Which of these should be mandatory? | Feedback received from Claire and Alice as to which one to keep on the template and which one to remove. Draft version consolidated and added to dev story. Concern over mandatory fields in the template. E.g. DOB may not be available for non-trainees. | Data Leads |
| ||||||||
4 | Workbook on sub heading 4 was put together with input provided by Claire Lehoux. Although separate from Reference data mapping, there still need to have legacy data tidied up in the DR. Not related to the Oriel integration but for data quality in general. | Data Leads |
| |||||||||
5 | Workbook on sub heading 5 has a list of Immigration statuses discrepancies that requires mapping. Oriel reference data for 2017 and 2018 have different list of values that will be passed on to TIS. | Agreed not to map and just take Oriel values as Intrepid data is not accurate and up to date. Regarding the 2 new questions added for immigration on 2018 application form in Oriel, the recommendation is to record these as 2 new fields on TIS.
Oriel to fix their extract to send those values. | Data Leads |
| ||||||||
6 | Reference data changes on Oriel will need to be coordinated with TIS going forward. | Data Leads |
| |||||||||
7 | Custom report can be built on Oriel to extract a list of trainees for specific programme/s where they have accepted an offer or withdrawn after accepting. This task is to:
| Ashley and Claire met to discuss and Recruitment lead is currently working on this. Concern raised over Dental trainees. They are expected to use the agreed approach in order to import trainees from Oriel into TIS. Ashley - To speak to Joanne Watson (Unlicensed) Alistair Pringle (Unlicensed), to add channel on Microsoft Teams to get feedback over: - Done
| Data Leads |
| ||||||||
8 | Documents will be be extracted from Oriel and stored on TIS in a data structure on Azure to be consumed by the Bulk Upload function during trainee records creation. | For info only. | Data Leads | |||||||||
9 | The Recruitment Import template may need to be changed to have new columns or some columns removed if not needed. For e.g. ApplicantID may need to be added to accomodate for the above Process. Some of the columns in the current Recruitment import template in its current format, will overlap with the fields that are received from Oriel. Do we want to keep them in the template? Which of the overlapped fields would be best to be manually filled? | ApplicantID will be added. - Done Next steps are: SImilar action to #3. ApplicantID = ORIEL PIN | Data Leads |
| ||||||||
10 | The Recruitment Import template will be used both for creating and updating records on TIS. In a scenarion where records already eixist on TIS, what should be the best course of action by the Upload function? For e.g. should all or a selection of fields on TIS be updated/overwritten? | The template will be used with the relevant fields populated so that existing person record on TIS can be matched with to update the record. The rules for finding a matching person record are If an existing person exists on TIS, then update with populated values from the spreadsheet and pull Oriel stored data from Azure storage to update the person record with programme and curriculum details. | Data Leads |
| ||||||||
11 | Oriel XML file format |
...
Summary | Jira Ticket | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
1 | Understanding process followed in local offices for onboarding new PGDiTs from Oriel to TIS |
|
...