Closure Review

Project Summary:

Project Review

Staff in the University’s Student Disability Service (SDS), have 2 web-based ColdFusion applications, RADIUM and KELSO, which run against a SQL Server database. These were originally written and supported by SDS in 2005, after they were de-scoped from the EUCLID project, and were subequently agreed as being supported from Production Management on a "best endeavours" basis in 2009. Many of the challenges encountered in this project related to this historical situation.

The KELSO database is the source of the SDS’s 2 waiting lists:

1. Educational Psychologist waiting list
2. Needs Assessment waiting list

The Student Disability Service (SDS) needs to handle over 650 appointments annually. This consists of  +250 Needs Assessments for Advisors to assess a disabled students needs, and over 400 appointments for students to see an Educational Psychologist.

The SDS also helps students to apply for Disabled Student Allowance (DSA). It’s a large part of the work carried out by the service as SDS Advisors carry out over 250 Needs Assessments each year. This brings in about £85,000 of income which is crucial to the running of the service.

This project is concerned with the following enhancements to the Kelso database system:

1. Added functionality and overhaul of the two waiting list systems listed above
2. A more efficient Needs Assessment, repeat DSA process

During the project initiation it was identified that both RADIUM and KELSO would need to be upgraded to a supported version of ColdFusion, and that the SQL Server version 2000 database needed to be upgraded to a supported version. At that time it was not identified that the SQL Server database was also the data source for 3 further Cold Fusion applications, which remained unsupported and on their current ColdFusion version.

Objectives & Deliverables (from Project Terms of Reference)

Although the above has been agreed as delivered with the Business Lead during the Deployment Sign off there is still frustration from SDS and IS Applications that some of the objectives that SDS wanted to realise at a lower level haven't been able to be achieved. For example, it was not possible to show in emails which adjustments were new or which were being removed, and this was due to a limitation with the original design of the system as developed by SDS. The project scope as originally desired by SDS was also reduced due to the significant amount of time that it took to upgrade ColdFusion and SQL server, which impacted the project by reducing the requirements that were defined and agreed for the Radium element (i.e. the project delivered on the requirements agreed, but SDS had desired more requirements in the first place). On a positive note, feedback from SDS included "many of the enhancements we requested were made and have significantly improved working practices".

Scope

The scope of this project as defined in the Project Terms of Reference included the following:

In addition work was undertaken to:

a) Resolve security issues for both Kelso & Radium

b) Add small enhancements to the Kelso application after the initial Kelso changes were deployed

c) Resolve the discrepancies between the LIVE, TEST and DEV environments which impacted the upgrade.

Analysis of Resource Usage:

Staff Usage Estimate: 198 days

Staff Usage Actual: 427 days

Staff Usage Variance: 116%

Other Resource Estimate: 1 days

Other Resource Actual: 1 days

Other Resource Variance: 0%

Explanation for variance:

There have been a number of challenges in this project, which have contributed to the variance.

1. Lack of understanding of the existing SDS systems and their limitations

Although there was a user manual there was no documentation on how the Kelso & Radium systems were put together, which meant that the analysis and design had to be worked out from what could be observed in the user functionality and the code and database itself. The initial estimate for the Kelso & Radium enhancements had made the implicit assumption that the design could include changes to the database where appropriate. It transpired that there were 3 additional applications using the same database, which the business use but which are not supported. This meant that the changes had to be made in a way which did not change the database, which was a constraining factor and meant significant workarounds in the design and build to take this into account. In addition the developer had to write documentation to enable these other out of scope applications to point to the correct area.

Furthermore, while testing the upgrade it emerged that the LIVE environment was different from that in DEV & TEST, an issue which pre-dated this project. This required additional scripts, rework and re-testing to enable the test upgrade to be completed.

2. Estimations were inaccurate, particularly from new team members

Almost every one of the team was completely new to either the University or the applications, and some had not estimated their work before. This meant that the initial estimate was under-estimated, as the scale of the work and potential complexity were not realised at that point.

a) For example the business analysis was initially estimated at just over 11d, and the actual was over 40d. This was due to a low initial estimate significantly under-estimating the complexity of the systems and their inter-relationships, and the high volume of requirements that needed to be carefully documented, reviewed, and updated. It was also due to the subsequent length of the project, as there needed to be a handover to a new Business Analyst for the Radium testing, due to the departure of the original Business Analyst.

b) For example the Phase 2: Waiting List Enhancements (changes for 2 lists), DSA Process Improvements was estimated at 91d, and the actual was over 150d due to the lack of understanding of the systems and their limitations noted above. This was also due to the subsequent length of the project, as a different developer was involved in the Kelso and Radium enhancements.

Furthermore, the estimates for the Cold Fusion and the database upgrade were lower than the actual outcome, in part as security issues were uncovered which had to be resolved to enable the upgrade to be completed effectively and due to the variance in the schema between LIVE, TEST & DEV.

3. The business had read/write access directly to the database

This had to be removed, and took timw to resolve with support from Production Management

4. There was a variation in scope

a) A number of technical issues needed to be solved before the coding work could begin, e.g. 38d were taken resolving issues with the existing systems, including basic authentication, load balancer, cosign, Rsync, SQL mirroring, security, and discussion regarding PACHA security issues (although PACHA was out of scope).

b) There was no allowance for rework after the deployment, and the business hadn't fully appreciated that this would be the case, i.e. the vision at the requirements stage is what would be built, and nothing more. Although this was reflected in the plan, there was an expectation that there would be leeway for changes, which was not the case as it was neither estimated nor in the plan. Once the system had been built, it was realised that there were changes that would be needed to fulfill the business expectations, and additional work was agreed for Kelso to accommodate this.

It is also noted that the annual plan estimate for this work was originally 100d. At the time of estimating the complexities which arose during the project were not known, and it may be that there was an assumption that it would be similar effort to an IS Applications built and supported service that proved to be far from the case. The lack of knowledge and documentation on the system, the fact that the database fed 5 linked applications and could be modified directly by SDS staff and the lack of up to date security all contributed to the estimate increasing both from annual planning to the end of planning, and from the end of planning to the current actual.

Key Learning Points:

Lessons Learned:

1. Bringing applications into Production Management support: applications that have been developed elsewhere should be brought into the state where they can be effectively supported by Production Management as a project funded by the business area at the time that the application is moved to Production Management support. This should take into account current supported versions of the infrastructure and the expected length of time that Production Management would be expected to support the application, and highlight when upgrades would be expected. If this is not done, expectations regarding the amount of effort that would be needed to make subsequent changes to the applications need to be realistically set, based on the actual experiences of this and any other similar projects. At a minimum the following need to be documented at the time that they are taken on by Production Management:

1. Technical Architecture Document (including listing the users needing access to the database - this would have highlighted the business having read/write access to the database),
2. Systems Architecture including interfaces - this would have highlighted the fact that 5 applications shared access to one database, only two of which we were changing,
3. Security Audit - this would have highlighted the security issues with the existing applications and would have enabled these to be resolved much sooner than during this project.
4. In addition to the above 3 mandatory documents ideally a technical description of how the system was designed, and not just a user manual.

2. Query whether the project will realise the benefits if the project estimate increases With hindsight, it may have been more effective to focus on building a replacement for the Kelso and Radium applications, rather than trying to enhance these existing applications. It is hard to see at which point this should have been pushed, but perhaps the project initiation stage would have been most appropriate, when evaluating the benefits against the estimate, given the level of uncertainty with a system developed external to IS Applications.

3. Develop alternative costs when de-scoping work from a major project When de-scoping work from a major project such as this was de-scoped from EUCLID, identify what is the alternative for the business, how long will that alternative last, and what it will cost to a) develop the alternative, b) maintain that alternative and c) what the roadmap is for future development of the functionality. This will enable a more accurate cost/benefit perspective to be realised, as with hindsight it would probably have been better to include this work at the end of the EUCLID project, rather than to build the systems, upgrade and enhance them, and then migrate the functionality to EUCLID in future.

4. Include previous developer in initial estimate and planning process where possible The project high-level estimate during annual planning the previous year was estimated at 100d, and at the end of project planning was estimated at 198d. As Bruce Darby developed the original system, and was still at the University, it would have been very helpful to have him participate in the planning process, to enable him to highlight the risks, and limitations of development which were not realised during the project planning phase. This would potentially have highlighted earlier some of the challenges encountered in the upgrade, and enabled a better estimate to be developed, which would then have enabled the benefit to value question to be raised sooner.

5. Avoid having all-new people in the team and undocumented systems When the project was initiated the project manager, business lead, developer and technical architect were all new and were not familiar with the ways of working and the project methodology. There was no technical documentation explaining how the system was put together. There was some limited understanding from Applications Management, gained through small changes to the application through responding to support calls, but this was nowhere near comprehensive. This meant that the team did not have the documentation to get familiar with the system quickly, and had to work out how it worked from first principles by looking at the code and the database themselves. The team were also not familiar with the methdology, and so the relevant information was not always fully available, e.g. who contributed to the original estimate. In future, Programme Managers should support new Project Managers in identifying when other team members are new, and to request support from Resource Managers in either assigning an alternative or support from a senior, and this to be included in the estimate. This should be during planning, and for new project managers the Programme Manager should specifically sign off planning, checking that the relevant people are all involved and the relevant learnings from other projects have been taken into consideration.

6. When starting a new development copy what is in LIVE to DEV as it turned out that the schema in LIVE was different from that in DEV and TEST, causing a number of issues and rework as noted in PICCL28. It would be beneficial if Production Management could sign off that the TEST & LIVE environments are the same and fit for development before development starts, and that development always starts with the LIVE system as a basis for both application and database work.

7. When peer testing ensure the environment is set up correctly, and highlight any issues at the time as problems with setting up the environment came up in deployment, which could have been identified earlier.

8. Deployment of code between TEST & LIVE should use scp rather than ftp, to avoid permission problems which were encountered during one deployment. Although ftp works for other projects, it has been known not to work before, this may be related to which user the person doing the deployment is logged in as on both machines (ideally it should be the same user).

9. Subsequent Agile projects need to have breaks to enable project work to be completed. Towards the end of the project, when the main development had been completed the developer was assigned to another project, which was using the Agile methodology and so he was not available to do work for this project, despite being assigned to do work for this project at that time. This created a significant limitation for resolving the issues raised as well as for deploying the changes to LIVE. It was subsequently negotiated that the Agile project would have reduced work on each iteration to allow work to continue, and this project was extended due to the limitation of only being able to do some work every two weeks. In future, assignment of resources to Agile projects need to take into account the current expected work from other projects, or propose a solution before assigning a resource to more than one project if one is Agile.

10. New business partners can find the terminology and methodology (project management model) hard to follow, and Project Managers need to work to act as a bridge between the development team and the business. This means explaining what is needed in a way that the business partner can understand and work with, noting that terms within the methodology may mean something different to business partners in a different context. This should start from project initiation, supported by the Programme Manager for new Project Managers in particular, and continue throughout the project.

Outstanding issues:

None, all jiras are closed.