Closure Report
Caveat
The completion report has been written by the incumbent project manager, appointed in May 2018. Comments have collated from others who have a longer association with the project
Objectives Analysis
|
Objective (as stated in original Brief) |
Objective Status (Achieved / Partially Achieved / Not Achieved). Commentary. |
|
| 001 | Design of the technical environment that will underpin the EDW service. | Achieved |
| 002 | Design of the Data architecture strategy and guidelines for EDW | Achieved (version 0.1 created) and to be completed as part of DTI021. |
| 003 | Design of the Security and Access control for the EDW environment | Achieved. Licenses for Oracle Security to be switched on as part of DTI021. |
| 004 | Creation of the Oracle-based technical environment that will underpin the EDW service. This will encompass DEV / TEST and LIVE | Achieved |
| 005 | Creation (completion) of the Microsoft Server environment to support SSIS (TEST & LIVE environments already exist). | Achieved – a Microsoft server environment was built to support SSIS; however, a decision was made to progress with Open Source Talend in its place, thus this was no longer required. |
| 006 | Reference Data Acquisition | Achieved |
| 007 | Static Dimension Creation | Achieved |
| 008 | Evolve existing stakeholder organisations to support future EDW change and BAU activity. | Achieved. EDW service organisation designed. |
Deliverables Analysis
|
Deliverable (as stated in original Brief) |
MoSCoW |
Deliverable Status (Delivered / Partially Delivered / Not Delivered). Commentary. |
|
| D01 | Technical Architecture Design | MUST | Delivered |
| D02 | Data Partitioning Strategy | SHOULD | Not Delivered – to be picked up in DTI021. |
| D03 | Data Capacity Planning | MUST | Not delivered. It was agreed that capacity planning would be done as part of BAU activity, once data has been acquired. This is built into the service organisation RACI. |
| D04 | Development Guidelines: ETL Naming Standards | MUST | Delivered V0.1 – to be picked up in DTI021. |
| D05 | Development Guidelines: Data Loading | SHOULD | Delivered V0.1 – to be picked up in DTI021. |
| D06 | Development Guidelines: Parallel Processing | SHOULD | Delivered V0.1 – to be picked up in DTI021. |
| D07 | Meta Data Design Strategy | Should | Delivered V0.1 – to be picked up in DTI021. |
| D08 | EDW Security Policy | MUST | Delivered |
| D09 | EDW Security Technical Design | MUST | Delivered |
| D10 | EDW DEV Environment | MUST | Delivered |
| D11 | EDW TEST Environment | MUST | Delivered |
| D12 | EDW LIVE Environment | MUST | Delivered |
| D13 | SSIS DEV Environment (TEST / LIVE already exist). NB. This deliverable may not be needed, depending upon outcome from DTI004 project. | MUST |
Delivered – Decision made to progress with Open Source Talend for data transfer rather than SSIS. Tactical decision made to build SSIS DEV, in case it was needed. |
| D14 | Organisation / Hierarchy Data Design (See Notes) | MUST | Delivered |
| D15 | Organisation / Hierarchy SDS | MUST | Delivered |
| D16 | Organisation / Hierarchy Build / Test / Deploy | MUST | Delivered |
| D17 | Time dimension Data Design | MUST | Delivered |
| D18 | Time SDS | MUST | |
| D19 | Time Build / Test / Deploy | MUST | Delivered |
| D20 | Development Organisation: Adapted Organisation, Processes, Roles & Responsibilities. | SHOULD | Delivered |
| D21 | Service Management Organisation: Adapted, Processes, Roles & Responsibilities. | MUST | Delivered |
| D22 | Production Management Organisation: Adapted, Processes, Roles & Responsibilities. | MUST | Delivered |
Scope Analysis
|
Scope (as stated in original Brief) |
Scope Status (Scope Delivered / Partially Delivered / Not Delivered). Commentary. |
|
| S01 | Creation of the technical environments that will underpin the EDW service. | Delivered |
| S02 | Acquisition of Organisation / Hierarchy data, as a fundamental EDW business data asset. | Delivered – but only current data, not historical data. |
| S03 | Creation of associated technical documentation and guidelines, for use by this project and follow-on business driven projects. | Delivered – some items deferred to DTI021. |
| S04 | The adaptation of existing development, service management and production management organisations to support future change and BAU activity. |
Service model Delivered. Actual service organisation to be created as part of later data acquisition initiatives, outside the scope of the EDW programme. |
Project Schedule
The project commenced in May 2017 and was delivered in July 2018. The overall project schedule from initiation to completion is 14 months.
Key Milestone History
| Key Milestones | Baseline Data | Final Date |
| Planning | 09/06/2017 | 28/07/2017 |
| Design Phase Complete | 29/09/2017 | 27/02/2018 |
| Org Hierarchy ASOR | 24/11/2017 | 30/05/2018 |
| Infrastructure Build Complete | 06/12/2017 | 26/06/2018 |
| Acceptance Sign Off Review* | 13/07/2018 | 18/07/2018 |
| Deployment | 08/12/2017 | 27/07/2018 |
| Deployment Sign Off Review | 03/08/2018 | 02/08/2018 |
| Close | 12/01/2018 | 02/08/2018 |
*New Milestones added in after original planning sign off.
Budget Analysis
Budget Evolution
| Date Changed | Budget (Days) | |
| 1. Original 3 point estimate | 150 | |
| 2. Project re-plan (End of Planning) | 09/06/2017 | 230 |
| 3. Project re-plan | 04/10/2017 | 262 |
| 4. Project re-plan | 17/01/2018 | 294 |
| 5. Project re-plan | 23/03/2018 | 340 |
| 6. Project re-plan | 23/05/2018 | 360 |
| 7. Project re-plan | 20/06/2018 | 397 |
Final spend Analysis by Team
| Team | Sum of Actual Effort/1d |
| Project Services | 114.7 |
| Development Technology (Including S/W and SSP) | 209.35 |
| Production Management | 11.7 |
| Technology Architecture | 31.5 |
| All others | 38.5 |
| Service Management | 0.7 |
| Grand Total (Days) | 406.45 |
Final spend Analysis by phase
| Phase | Sum of Actual Effort/1d |
| Build | 167.75 |
| Closure | 0.8 |
| Deployment | 6.9 |
| Design | 89.1 |
| Initiation | 6.6 |
| Management | 130.7 |
| Planning | 4.6 |
| Grand Total (Days) | 406.45 |
(For more spend analysis refer to the following analysis – K:\ISAPPS\dsg\Projects\DTI006 EDW Implementation\102 Closure Report)
Reasons for Variance
Due to the bespoke nature of the EDW, The initial assumption was that EDW would be built using standard Oracle infrastructure. This has not proved to be the case, principally due to current requirements regarding information security.
This has resulted in EDW acting as a technical “loss leader”, with a lot of discovery and design work done that will benefit other future projects and programmes.
As a consequence EDW is the most secure and up-to-date on premise platform at UoE.
It is worth noting that staff industrial action also had a negative impact on delivery timescales. As a result, we estimate this has increased the cost of the project by at least 150 days.
Key Issues
| Issue | Details | |
| 001 |
Excessive length of project |
The planned project duration was approximately 6 months; however, due to various delays, this ended up lasting 14 months. - A decision was made to build the EDW using emerging technologies and a new security model which resulted in a non-standard design being adopted. This led to a significantly longer build phase. - It was originally estimated that the build on the DEV environment would take 2 days which ended up taking 67 days (an increase of 3250%). |
| 002 |
Lack of business analysis for initial data set |
Due to the small size of the Org Hierarchy dataset used as part of DTI006, along with how it is used, it was decided that no formal business analysis would be required to drive the design and subsequent build. It was identified once the build had been completed that the project has not imported the historical data from the Org Hierarchy. This needs to be addressed in a future project. |
| 003 |
Inability to complete documentation during DTI006 due to time constraints |
Due to constraints on the project as a result of the EDW build taking significantly longer than anticipated along with additional priorities from other projects, some documentation could not be completed within the scope of this project.
All documentation had a working v0.1; however, the final versions were either slipped into DTI021 to be completed or were handed back to the document owner to be completed within business as usual. |
| 004 | Dependency on ITI | During the build, various elements had to be completed by ITI due to the skill set required; however, resourcing had proved to be problematic in some instances due to additional responsibilities/training within the team which caused additional delays. |
| 005 | Organisational Hierarchy | The org hierarchy does not currently pull through any historical data which may impact upon future data sets within the EDW. |
Lessons Learned
| Lesson | Details | |
| 001 | Business Analysis on all data sets (regardless of size) |
It was identified after the Org Hierarchy data set had been successfully loaded onto the EDW that there was a requirement to show all historical data, not just new data going forward. It would be recommended that business analysis would be undertaken for any future data projects to understand the needs to the data set, regardless of size. |
| 002 | Collaboration between Data Architecture and Software Development |
Due to the nature of the work being undertaken, it was important for both the Data Architecture and Software Development teams to work closely together on the design of the EDW. A collaborate approach would be recommended for any future projects of this nature. |
Outstanding Tasks
| Task | Details | |
| 001 | TNS Population. | 'How To’ guidance to be created to support usage post ‘Go Live’ |
| 002 | Deployment Activity | Review of the deployment checklist for any actions to be picked up post Go Live (slipped into DTI021). |
| 003 | Technical Development Documentation |
Working v0.1 has been created and will be used as a basis for updating living documents. Final/next versions to be created for the following: *TNS Population – ‘How to’ Documentation; * ETL Guidelines; * Data Loading Guidelines; * Parallel Processing Guidelines; * Meta Data Strategy. |
| 004 | Strategy Documents | Working v0.1 has been created and will be used as a basis for updating living documents. Final/next versions to be created. |
Acknowledgments
Thanks go to the entire team who have persevered with this project over a long period of time.
| Development Team | Production Management | Project Services | Service Management | Wider UoE |
| Iain Fiddes | Heather Larnach | Chris Lawford | Alex Carter | Dave Berry |
| Geri Grannum | Julieta Pineda | Mark Ritchie | Andrew McFarlane | Wilbert Kraan |
| Gillian Henderson | Chris Cord | Nigel Hedley | Susan Cooke | Jim Galbraith |
| Mark Lang | Adam Wheavil | Alison Armstrong | Craig Middlemass | |
| Bill Lee | Ana Heyn | Stephen Roy | Peter Phillips | |
| Defeng Ma | Suran Perera | Sally Priestly | ||
| Rob Manley | Stefan Kaempf | Garry Scobie | ||
| John Grzinksi |
| Attachment | Size |
|---|---|
 dti006_-_completion_report_v1.1.docx | 39.15 KB |