Closure Report
Project Summary
Objective 1
Due to its sensitive nature, there was a need to move the following defined list of “reportable characteristics,” data items to a new, more secured place (MCI) on the SITS database as the golden copy, so that EUCLID remains supported.
MCI is also used as a data source for HESA Statutory Returns.
Some processes such as online registration had already been updated to store this data on MCI, but other processes (like admissions, reporting etc.) were still reading data from the STU table and needed to be reviewed. Historic student data also needed to be migrated, including the GDPR review of data “not needed.”
Objective 2
We also needed to address the SITS database performance issues that occur on large tables so that EUCLID downtime is prevented as the increased number of users, processes and therefore data being stored on the SITS database is causing performance issues and impacting users (staff and students). This involved identifying data for deletion.
Previous analysis work done on the Assessment APT tool showed that low level components assessments could be deleted and the performance would be boosted by 30%.
This occurred with the Engagement tables growing at a rate of 2 million a year. New processes were also added during the Covid period to record students' LEARN logins as required for Immigration Tier 4 students and is still being used by CAHSS. This was implemented without a data retention policy and therefore, the creation of a new data retention policy was required.
Objective 3
Objective 3 refers to other technical legacy work (VUE upgrades) taking place as part of Continuous Service Improvement (CSI) that was later absorbed into this project.
Analysis of Resource Usage:
Staff Usage Estimate: 338 days ISG APPs (Originally 204 days), 123 days SSP
Staff Usage Actual: 330.8 days ISG APPs, 88.5 days SSP
Explanation for Variance
Throughout the project, particularly during the work on Objective 1, several unforeseen technical issues were encountered which caused delays. This resulted in the need for Change Controls to be submitted; both moving milestones and increasing budget due to the extra work required in rectifying the issues.
Objective 2 encountered issues upon its initial deployment (see below) resulting in extra work and delays being necessary.
Outcome
Objective 1's LIVE deployment took place on Monday, 23 September 2024. EUGEX and EUCLID reads were moved over to the new MCI tables while the BI universes were updated. No errors or faults were raised to the Project Team thereafter by any users and therefore the deployment was successful.
Regarding Objective 2, the Business Analyst agreed new data retention periods with the different schools for the necessary data and these could therefore be applied to the deletion process.
The original intention had been to deploy and run some of the data deletions in the LIVE environment prior to the end of January 2025 however, further technical issues were discovered in the TRN environment meaning that this milestone was no longer achievable as the operational representative/SME had limited availability. There was also a reluctance to make any amendments to APT data until after the post-winter exam Board of Examiners had completed and results had been published in mid-February.
A Change Control was therefore submitted and the LIVE deployment took place on 13 March 2025 with the first deletion run on 8 April 2025. However, an issue was encountered whereby incorrect data was deleted, effecting current applicants, and therefore had to be restored. Since then, the deletion code has been updated to ensure this doesn't happen again.
The project was then rearranged to deploy the fixed code into the SITS LIVE environment before closure, with the deletions being run by the Student Systems Operations Team in December 2025. However, on 15 July 2025, that team confirmed that they could not facilitate any further work on this project until at least September 2025 due to conflicting priorities. The decision to was therefore made to move the project to Closure with the remaining work being transferred into the Continuous Service Improvement (CSI) area.
Most of the work on Objective 3 is complete with two outstanding JIRAs (see Outstanding Issues below). These are already being managed by CSI.
Objectives, Deliverables and Benefits
| ID | Description |
Priority (MoSCoW) |
Benefit(s) | Success Criteria | Achieved (if applicable)? | Notes (if applicable) |
|---|---|---|---|---|---|---|
| O1 | Move the student characteristics from STU table to MCI table | Must | Sensitive data will be more securely stored. We will also be aligned with Tribal support agreement/future version upgrades. | Sensitive data stored in MCI tables |
Yes |
|
| O1D1 |
Analyse the processes/integrations (WS02, EUGEX, EUGEX file generator) storing student details on STU tables |
Must | Will allow the update of the processes to move to MCI tables | All processes/integrations identified and documented | Yes | |
| O1D2 |
Analyse the BI reports using STU tables |
Must | Will allow for the update of the BI universes and reports | All BI reports using STU tables identified and documented | Yes | |
| O1D3 | Opportunity for GDPR review of data not needed | Should | Opportunity to check whether we are adhering to GDPR | GDPR review complete | No | |
| O1D4 | Analyse the staff access to MCI | Should | Will allow for the implementation of the improved access control to the sensitive data. | All staff access to MCI identified and documented | No | |
| O1D5 | Update the processes to move to MCI tables | Must | Will allow for the migration of the student characteristics from STU table to MCI table | All processes to move to MCI tables documented | Yes | |
| O1D6 | Migrate data from STU to MCI | Must | Avoid for Data Futures Reporting errors | Data migrated from STU to MCI. Any and all errors resolved | Yes | |
| O1D7 | Update the BI universes and reports, EUGEX and WS02 | Must | BI universes and reports will continue to function by referencing the MCI tables | All BI universes and reports updated | Yes | |
| O1D8 | Implement the improved access control to the sensitive data. | Should | Ensures sensitive data is securely accessed by the appropriate people | Improved access control implemented and documented | No | |
| O2 | Address SITS database issues that occur on large tables | Must |
Prevent or mitigate downtime, improve performance and contribute towards being GDPR compliant |
SITS database no longer suffers from performance issues caused by large data tables | Yes | |
| O2D1 |
Review the SITS database to identify the large tables, review processes creating large data volume. Analyse the access/permissions.
The following have already been identified as causing issues:
Engagement/Learn logins added during Covid (not used by Immigration any longer, but by CAHSS) , Men_doc (5 TB size of db) has compliance data (see dependency from SAC097) Work done last year: low level assessments components: not compliance but would improve performance by 30% during peak period All this work can be completed iteratively |
Must | Will allow the build, test and implementation of the deletion process | Large tables and processes creating large data volume identified and documented | Yes | |
| O2D2 | Prioritise actions: how we address the size of the tables/data retention policy required | Should | Will allow the build, test and implementation of the deletion process | Actions identified, documented and prioritised | Yes | |
| O2D3 | Engage with stakeholders to agree data retention policy/process. Establish whether a DPIA is required | Must | Will allow the build, test and implementation of the deletion process | Data retention policy agreed and signed off. DPIA complete if required | Yes | |
| O2D4 | Measure performance expected: take baselines, then review | Should | Will allow successful tracking/review of the build, test and implementation of the deletion process | Baselines established and documented | Yes | |
| O2D5 | Build, test and implement the deletion process as per agreed retention policy, ensuring batches feasible to run in live | Must | Will remove large amounts of data and therefore reduce the risk of system downtime | Deletion process implemented in LIVE environment | Yes | |
| O2D6 |
Delete Course Block Occurrences for previous years. |
Should | Will remove large amounts of data and therefore reduce the risk of system downtime | Course Block Occurrences for previous years deleted | No | |
| O2D8 | Handover the deletion process to student systems so that they are run as BAU | Must | Will allow for the resolution of SITS database performance issues that occur on large tables | Deletion process formally handed over to student systems. No errors identified | Partial | Fixed code complete - handed over to Student Systems to be deployed into SITS LIVE environment |
Overall Benefits
- O1 - Sensitive data will be more securely stored. We will also be aligned with Tribal support agreement/future version upgrades
- O1 - Avoid for Data Futures Reporting errors
- O1 - From an operational perspective, having all student characteristic data in one place (MCI tables) has been useful.
- O2 - Will remove large amounts of data and therefore reduce the risk of system downtime
- O2 - Will allow for the resolution of SITS database performance issues that occur on large tables
- O2 - Operations are expecting the main benefit of the applicant data to be in reducing the number of manual applicant matches performed by the team which will be a huge help in saving time and effort.
O2- There are some basic facts that we know about what effects the overall efficiency of a database when it comes to table size:
- The more data you have to read to return your result the more work the database engine has to do.
- Well indexed tables mitigate the size of the data set.
- If you no longer need old data, you're wasting database capacity by keeping it.
- Generally, the larger the tables the more resource is used especially if we are not able to use an appropriate index.
- Table scans/joins on a non indexed column of a very large table will affect performance, especially if it is frequently run.
O2 - The carbon footprint of storing one terabyte of data for a year is roughly 10 kilograms of carbon dioxide equivalent (kg CO2e). This is equivelant to 25.5 miles driven by an average (petrol) car. In terms of emissions, this is equivelant to:
- 11.1 pounds of coal burned
- 1.1 gallon of petrol consumed
- 808 smartphones charged
O2 - There have been other, unquantifiable benefits to this project including, but not limited to:
- Less exeperience Developers and Business Analysts, as well as a Project Manager new to the university, using the project as a learning exercise and having the opportunity to develop skills related to their respective roles
- Receiving agreement on data retention policies across different colleges and schools to ensure a cohesive approach
Milestones
As you can see, due to numerous unforeseen challenges, issues and learnings, many milestones had to be adjusted throughout the duration of the project.
Key Learning Points
| Ref | Title | Impact | Theme | Stage |
|---|---|---|---|---|
| 1 | Details contained in JIRAs should not assume prior knowledge | Should implement as good practice | Change Management | Deliver |
| 2 | Should have dedicated resource from each required team | Was detrimental | Resourcing | Deliver |
| 3 | Challenges faced getting retention periods agreed for some of the data | Was detrimental | Business Analysis | Analyse |
| 4 | Be prepared for multiple deployments | Should implement as good practice | Management of a Project | Deliver |
| 5 | Ensure conditions within procedures have the correct operator(s) | Should implement as good practice | Technology | Build |
| 6 | Operational team(s) should be more involved in the creation of a testing plan | Should implement as good practice | Support | Accept |
| 7 | Communication between teams/individuals | Was detrimental | Management of a Project | Deliver |
| 8 | Incorrect Data Deleted | Was detrimental | Technology | Deliver |
| 9 | Change Controls for BAU Processes | Should implement as good practice | Change Management | Deliver |
Outstanding Issues
Sync to STU tables to be switched off
As part of the Student Application Modelling ( SBI001 ) project, Enterprise Architecture were working on loading applicant and application information into the EDW data warehouse.
The source table being used for extracting applicant information was STU and therefore expected to change. To ensure a smooth transition and avoid any disruptions, we agreed to not switch off this sync until Enterprise Architecture confirmed their work is complete, which they did on 23 July.
The job for switching this off has been tested by our Lead Developer and can be moved into CSI.
There are several tasks related to Objective 2 outstanding:
- The updated code has been deployed to the DUST environment but the updated process hasn't been run.
- Operations will write some test scenarios.
- An SSP Tester will then have to test in the DUST environment. This will likely take a few days. Actually running the code will be part of this testing in the DUST environment.
- DevTech have already gathered various stats from DUST/the Men_doc table that’s stored in there and will need to do some comparisons thereafter.
- Following the testing, the updated code will need to be deployed through the lower environments: TEST, TRN.
- Assuming all okay, the code will then be deployed into LIVE and sit there until Operations can run it (planned for Dec ’25).
User stories can be found HERE.
Implementation Plans can be found HERE.
There is one release remaining for Objective 3 cotaining the following JIRAs:
SAC098-432 & SAC098-433 - The Direct Admissions Vue JS frontend applications are vulnerable to data security issues. They are currently written in Vue-CLI which is in maintenance mode since 2022.
The Student Systems Operational team has asked that the CSI team wait until after July's change embargo to release this.