Intrepid Leave Manager ETL

1.

Data flow via NDW

• We don't need live feeds going across for Leave manager

• Separate data that flows externally rather than everything

• Little to no benefit of amending to flow directly from TIS

• Would need to switch data model to denormalised NDW to normalised TIS

• Large amount of development work from both sides

James: API created and link with e-portfolio? - TIS endpoints/ API to exchange data externally - Future development

• SB: Need to live query TIS person table in order to ensure inactivated users were inactivated in TIS

  • JH: Not a realistic scenario as TIS Admin accounts are not included in the TIS person table and so not included in dataset sent to Hicom. Leave approvers, ESs, CSs, etc do not have access to TIS. Trainees will only have access to Trainee UI which is a separate app to TIS.

  • KW: For Leave Manager user accounts, receive notification if a user leaves, logs into Intrepid and disables account.

  • ALL: Above process is adequate.

• SB: TIS needs connection with Leave Manager placement table to warn users if a placement is about to be deleted that have leave records associated with it.

  • JH/ALL: Above can be managed with a business process as needs to link in with Code of Practice and the ESR Interface. Above scenario is an edge case that covid-19 and the cancelling of rotations made more poignant. Even prior to TIS this edge case was never addressed is Leave Manager.

  • JH: Potential to revisit issue after TIS migration to AWS

• Maintain current flow via NDW

• When new TIS endpoints developed will reassess the above.

• There is a TIS constraint as will require the migration to AWS to take place first.

2.

Data Refresh Timescales and Scope

• To review timescales though happy with current

• SQL01 (1.20AM TIS-NDW-POD ETL) -> SQL06 (2.30AM NDW merge ETL) -> SQL07 (5.30AM NDW-HICOM ETL) -> Ends up on Intrepid by 7:00AM

• SB: As per above

As per above

3.

Which records do we need to send to Leave Manager?

• No current way of identifying which leave manager belong to which region

• To be discussed with Hicom - the incremental change as we don't want to delete any record from Leave Manager- benefit is that we don't have to be sending the historical data anymore.

• NDW does not do incremental

• Currently, Hicom delete and rebuild record everyday

•  No current way of identifying which Leave approver belong to which region

•  HEE does not have the ability to delete anything on Hicom's side

• BF: Agreed focus should be on receiving Current records only wherever possible

• Ideally send Current Person records with the roles of Leave.Applicant or Leave.Approver.

• Records not sent listed as Inactive

• Ideally send current, near future and near past placements in interface with exact time frames to be decided but to align to COP and other leave policy timescales

• Placements not sent but previously sent to be recorded as inactive

4.

What fields do we really need from TIS to make Leave Manager Work? we give the fields we have on TIS

• We don't know the fields Hicom uses - Question for Hicom

• We need Person, Post and placement (There might be lot of dependencies Hicom need to clarify)

• minimum set of fields required?need to considers info stuck in intrepid in inactive form state, person not been able to be leave manager approval because of intrepid inactive,,,

• BF: Potential to nullify more fields than previously listed

• BF: Work could be iterative

Switch ETL to only fields that exist in TIS.

Fields not included in the above but populated in Intrepid Leave Manager will need to be nullified by Hicom

Send Hicom the minimum number of records and fields required for Leave Manager

5.

Populating and Mapping Leave Manager fields from TIS

• Presently give roles manually

• Local offces drop down issues

• Programme membership issue

• Post owner issue - managing deanery in post field

• Placement in the future  - trainee requesting leave - retaining leaving manager when switching LOs- To discuss prefered LO with Hicom

• Trainee account activation on leave manager - manual /auto? Lot of time doing this. Trainee not sure which email address to reset Password.

• BF: Roles can be populated but then will need to be mapped to the correct data objects and data profiles

• BF: Local Office field can be mapped or potentially start using the TIS values. Also potential to stop using the field entirely.

• BF: Activation could become automatic

• BF: Can switch on automatic email for non-admins with account credentials when account becomes active

• Populate Roles field in Intrepid Leave Manager from Roles field in TIS and have those records gain the roles security permissions

• Map TIS Post Owner/ManagingDeaneryLETB to Local Office in Intrepid Leave Manager or potentially stop using that field in Intrepid althogether.

• Need to populate with all future placement Post Owner/ManagingDeaneryLETB records to allow Preferred Local Office with necessary dropdowns (detail to be worked through in future)

• Automatically activate Leave Manager trainees accounts

• Sync Leave Manager user emails with TIS for those never logged in to allow forgot password function rather than sending out credentials

6.

Scenarios to work through

• To be discuss with Hicom: We deleted rotations during covid  and Hicom delete was soft delete.

• BF: Need to work through scenarios for every record type

• Placement delete scenario can be worked through using a business process to be determined in HEE

• Other record scenarios to be worked through with Hicom

• As per above, will be reassessed upon creation of addition TIS data end points

7.

DR2 to NDW transfer Leave Manager Data

• We should be able to report on Every field reported in intrepid FE and personal ids/Primary keys 

• Hicom aware of the additional fields required to be added to the ETL. e.g. provider,

•  Whatever HEE records on Intrepid should be made reportable on DR2, having views to those is not a problem as long as they are viewable in their front end and repotable, to the last the primary keys

•  Intrepid_TIS database to be turned into views

•  Intrepid Archive and Intrepid?

• BF: Potentially will need to move onto DR3 in order to facilitate requirement

• All fields viewable in Intrepid Leave Manager Front End as well as Primary Keys to be included in DR2 data set.

8.

Documentation

• Documentation of the entire data transfer including a data dictionary and standards of fields utilised by both systems.

9.

Change Control

• Change control management group and process needs to be developed in order to ensure transparency and documentation of any changes to either system and agree any changes to the interface as required