Closure Report

Project Summary

Recent initiatives have seen the introduction of enhanced functionality to the Unidesk Service. With these enhancements now embedded the focus has moved onto how best we can utilise the data for management information purposes so that informed business decisions can be made.

There is also a recognition between the UniDesk partners that institutions will have different BI systems they want to use to enhance the standard TopDesk reporting functionality.  This project will focus on the needs of the University of Edinburgh.

For the Edinburgh instance of Unidesk this project will gather requirements for reporting from our different user areas and create SAP BI Universes and Information Spaces.

This will allow standard and ad-hoc reports.  The UniDesk service will facilitate an overnight download of data to a separate database to enable this.

The project will analyse the existing reports and transfer those that are still effective and providing a clear business value over to BI.  It will additionally identify what new reports are required. The new reports will look into what we want answered in terms of support, effectiveness and trend analysis.

Analysis of Resource Usage:

Staff Usage Estimate: 100 days

Staff Usage Actual: 180 days

Staff Usage Variance: 80%

Outcome

Although the project suffered substantial delays and a substantial breach of its budget it has delivered against the mandatory elements.  Although user accounts and security will require more work in service this will take some weeks to complete and will not require any of the project team to assist.  Therefore the project can be closed.

Explanation for variance

The design phase of the project proved difficult.  The design underwent a number of changes.  The reasons for this were.

• At the time, we did not have the appropriate infrastructure to run this service.  Our supported BI infrastructure (news servers) runs on Oracle and Unidesk is SQL.  What we were looking to do was new to the university in terms of using technology to link SQL to Oracle.  Running an SQL only design was not the initial recommendation as SQL was not supported at that time.
• Subsequently as the infrastructure is based around Oracle so are the skills.  Therefore an appropriate SQL development resource to extract the data into Oracle was not readily available.   
• Staff changes and senior stakeholder interaction.  Our senior development technology representative left the university midway through the design process.  One of the tasks he had been completing was to examine whether it was possible to link SQL to Oracle in the manner were looking at.  He reported high risk in this area.
• The team re-evaluated the infrastructure options along with the skillset of the development team (Oracle primarily) and choose to pursue an SQL design throughout removing the complication of linking SQL to Oracle.  It was felt that although there was limited experience of SQL/BI combinations the risks we saw during the design phase in connecting SQL to Oracle were far higher.
• We were above budget at this point using over half of our original number of days.  This was flagged by the team.  In a meeting with IS Applications department senior managers and the team it was decided that we should revert to the SQL to Oracle design.  The reasons for this were that it would be better to use the strengths of the development skills present and available to the project.
• An additional design methodology was added as St Andrews used CSV files extracted from SQL to populate their Oracle Universe.
• At this time the project was investigated/analysed by the then Head of Development (Dave Berry) on the request of the Director of IS Apps to examine whether correct process had been followed.  Correct process had been followed by the team, but it was obvious that the team had struggled to grapple with technological solutions that were uncommon within IS.
• Closing the project was considered and rejected.
• With the design finalised the project continued.  We required a developer and a senior to look at the SQL work including the CSVs.  We also required a developer to work on the Oracle side and the Universe.  With this alongside the standard Infrastructure needs the project team built to the agreed design.
• Linking the SQL CSV extracts into Oracle proved complicated and required several updates to its developments to fix snags.
• As a lower priority project, we had to wait until our development resource was available for snagging. As we were over budget and over time this placed considerable pressure onto the project.
• One of our main requirements through analysis was access to an SQL copy of the Unidesk database for IT consultancy to run reports.  We delivered this.
• Our first deployment to LIVE was completed in June 2016.  Testing of the LIVE instance would be required as there was limited data in TEST.  Due to other higher priority work the testing of the LIVE instance could not take place for 3 months.
• After testing in LIVE took place several updates to the system were identified, including a major defect causing the data transfer between SQL and Oracle to stop working.  These required both SQL and Oracle work.  This was done.
• Further testing took place with updated data in the system and further updates were identified.  The project team met and agreed that the best way to approach this was to identify a 2-week period that all necessary stakeholders were available.  We would then use this time to do the development work.  We identified the last week in Jan and the first week in Feb as the best time.  The benefit was that we would all be co-located within Argyle House.
• Our Business Lead returned after a prolonged absence and along with IT Consultancy analysed the Universe for reporting purposes.  4 iterations of the universe were completed with the assistance of Software Development and Applications Management. 
• Reporting was created within the universe and a comparison was done with IT consultancy reports.
• Successful verification of the universe was the result of this and a final iteration of the Universe was promoted to the LIVE environment at the end of May.  The development work has been completed and the project can close.

Although the above explanation of variance is long it is important that the order of events are detailed.   

Fundamentally the troubled design phase led to later difficulties with a complicated resourcing profile, which in turn led to substantial delays.

The team did look to cease the project on 2 occasions but this was not desirable.

This project is very late, but has broadly delivered on its scope. 

Key Learning Points

• Our initiation, planning and analysis phases did not sufficiently identify the infrastructure risks within the project.  This design would be new to IS.  Not having the infrastructure risks highlighted at an early stage meant we began the design phase and immediately hit barriers.  A strategic overview in terms of architecture would have been highly beneficial at this point.  This is something that happens as part of the project process now.
• When the design stage was failing multiple initiatives to assist occurred.  Senior Managers including our sponsor assisted.  Alongside this the project was investigated on behalf of the Director of IS Applications as detailed in the above section.  Additionally, staff new to the University recommended a ‘simpler’ solution of using CSV files.  As a project, it was clear trust in the group to make design judgements was lost.   After the initial difficulties the project team had chosen the SQL only design method.  This was abandoned at the end of the design process as Senior Management looked at the resource available and realigned our design to the development skills we had.  Additionally, CSV file transfer was added to the design of the system as it would be ‘simpler’.  The team were under enormous pressure to push on with the project and accepted these changes.  In hindsight, this further CSV design change had large consequences in terms of resource.  A PM in similar circumstances should suspend the project pending a detailed review when multiple design changes have taken place, no matter how unpalatable this would seem.
• Projects that are delivering technology solutions that are new to IS should be automatically marked as a high priority and reviewed  by Enterprise Architecture at initiation.  The priority can be lowered as the project moves through the phases.
• In the last year, this project has utilised under 30 IS Applications resource days.  It has been necessary to wait until higher priority work was complete.  It also meant that when individuals were free they may be working in isolation. To combat this the team decided to dedicate two weeks to the project in late winter.  Excellent progress was made at that time and although time has passed since then it has been used only for testing and refinement of the reports.  Should other projects have similar resource and budget issues perhaps a similar boot camp style approach may be as beneficial as it was for us.

Outstanding Issues

There are no issues with the Universe and all development/testing work is complete.  The service does have to be released to end users which will happen in the coming month.  The Service Manager has already arranged a meeting with the IS Consultancy team to go through the possibilities within the reporting universe.

It was agreed that this work would happen in service and that the project should be closed.

It is hoped this report is as detailed as is required.  If more detail is needed or further examination of aspects of the project (such as lessons learned) is required may I ask that due to other work commitments this is scheduled for a date after the closure has been approved.  

Lastly thanks to all who have been involved in this project.  Everyone can take solace in that it has delivered on much of its scope.  This will be a useful and as testing has shown, a highly accurate service.