1 Background
The context of the initiation of the BOLT CaP project was the recognition that a lot of effort is put into capturing data for statutory return purposes. The data that is captured is sometimes lacking in robustness. The staffing and estates costs within HE organisations are often the largest areas of expenditure and yet for the purposes of statutory returns they are sometimes the areas where the data is least robust due, in part, to the methods adopted for data collection.
If the data had greater validity it could be made used for a variety BI purposes such as Costing and Pricing of the component parts of the University’s business.
At the University of Bolton, most data sources are stored within monolithic information systems and the means of interrogation and reporting often takes the form of:
-
standard static reports generated by central specialists,
-
ad hoc reports which are generated by central specialist and take time and effort to specify and then prepare, and
-
ad hoc reports which are generated within local units often with limited understanding of the data sets that they are using and the potential for significant errors and misleading conclusions being drawn.
1.1 Aims and Objectives
The primary aims and objectives of the project therefore have been:
-
defining a set of principles which include the responsibility that goes with data ownership
-
determining standard data definitions and coding structures
-
defining a standard means of warehousing which facilitates reporting at different levels of granularity to aid decision making at different levels of the organisation and recognises the needs of organisational change
A further objective of the project team relates to the way in which the project should achieve its objectives in terms of in house or bought in expertise. Whilst there are many occasions when the solution to a system development is best achieved through an externally produced and procured product as can be the case with the production of BI dashboards, the project team held a conviction that in order to develop a suite of tools that are developable, expandable and sustainable into the future, the approach that was of greatest value to this organisation was to develop the capability in house and to utilise external expertise when this was needed to help grow internal knowledge and experience. The project aim was to develop the tools using low cost interventions, such as Reporting Services, rather than produce specifications to external consultants who would undertake this development work for us and therefore in the longer term result in the University having to invest more financial resources as developments and changes are required in the future. The University has seen many structural changes in the past and the team recognised that in the volatile external environment the likelihood of further structural change is high, therefore solutions need to be able to be flexible enough to cope with the consequent coding changes that inevitably result from structural change.
1.2 The Project Approach
The project was split into four distinct phases;
Phase 1 focused on developing the means by which statutory return data is captured, this encapsulated data location and definition and where necessary the development of data capture tools or mechanisms, in particular the creation of a Workload Allocation Tool.
Phase 2 considers the issues surrounding the warehousing of data and in particular determining the way in which data is to be warehoused which will allow expandability into the future. Phase 3 focused on the development of a Costing and Pricing Tool which uses the data captured and/or created in Phases 1 and 2.
Phase 4 was only briefly explored within the project and is to be developed post project however there is a real opportunity for the data being captured to be used within ‘review’ tools which will be developed. For example, review of Workload Allocation to gauge how staffing resource is being managed across the various academic units, review of actual compared to planned module and programme costs that can be interrogated to better understand the variances and therefore make informed decisions on resource allocation where appropriate.
1.3 Scope
During the development of Phase 1 it became apparent that the scope of the project needed to be significantly wider than initially planned. There were some issues in terms of lack of standardisation across the University that needed to be addressed if the creation of data capture tools were to be achievable. The agreement of a Workload Allocation Framework was negotiated with the University and College Union (UCU) as part of the package of change initiatives during May-July 2012 which has resulted in the University implementing the use of the Workload Allocation Tool from July/August 2012 in planning and managing the academic workload for the 2012/13 session.
1.4 Governance
The developments associated with the project highlighted the need for improved governance. From August 2011 the Marketing and Corporate Intelligence Committee (MCIC) was introduced into the Universities Committee Structure. The MCIC reports directly to the University Corporate Management Group. MCIC has a number of working groups that are focusing on different aspects of corporate data including the Management Information Working Group (See Appendix 2 University Committee Structure). Amongst other issues the importance of data guardianship is being explored through this working group and control mechanisms have been put in place to better regulate coding changes. Following on from the initial data cataloguing and data definition work of the BoltCaP team, who are also members of the MI Working Group are continuing to establish coding structures that facilitate the needs of the users whilst fitting into the constraints of the various applications in terms of coding levels that are available.
The project governance attempted to follow the principals established within Prince project management methodology in that a project board was established with a steering group and project management group. The project management group met far more frequently than had been planned, whilst the responsibility of the Project Board was moved to the University wide IT Committee. The changes in the project management and governance structure which worked more effectively are shown below:
1.5 Technologies & Standards Used
1.5.1 Software Development Approach
The project and development team have followed an ‘agile’ software development approach to allow the team to cope with, and overcome, continuous data structure and organisational changes. This way of working has enabled a more flexible approach for delivering the required work packages. It is hoped that this way of working will be fully adopted in the future to enable more efficient and robust development of software applications within the University. The software has undergone full project team testing before delivery to nominated key users to undergo User Acceptance Testing (UAT).
1.5.2 Technologies Used:
i) Web Server / Application / Database Server – Microsoft Windows Server 2008
-
We are using VMware virtual technology to host all applications and databases.
-
There are currently two environments for the WorkLoad Allocation Tool (WLAT), namely a Test/Development environment and a LIVE environment. The Test/Development environment consists of one virtual server hosting both the database and web application.
-
With the LIVE environment the database and application are both hosted on separate production servers. This enables better security and server performance.
ii) Databases and Data Warehousing
iii) Software Prototyping
iv) Software Development
-
WLAT solution system developed using Microsoft ASP.Net MVC and Entity Framework
-
Software developed using agile development and project management methodologies.
-
In partnership with Collabco we used scrum as the back bone of the development process which is itself an agile approach to project management and software development:
The key aspects of Agile methodology we applied were:
-
Regular demonstration of proto-types to key stakeholders through the life cycle of the development (via skype meetings both daily and end of week progress demonstration)
-
Empowered customer representatives continually input into the requirements and evolution of the product.
v) Reporting
vi) Enterprise Architecture
1.6 Senior Management Support
An issue that was not foreseen but had significant impact on the project was the lack of buy in from a senior sponsor from the earliest stages. Due to retirement, the sponsor changed and for some months the project was somewhat adrift. This had an adverse impact both in terms of moving the project forward and on the morale of the project team. A number of positive things happened in October/November 2011 which helped to turn the project around:
-
discussions with the senior management of the university began to have an impact and the value of the project was better understood;
-
a new Project Director was agreed (Executive Dean - Market and Corporate Intelligence, member of the Corporate Management Group);
-
the remit of the Information Technology Committee was redefined with greater emphasis on the strategic and operational priorities in terms of systems and IS&T Development. The ITC membership includes the Project Director and the Project Manager which has raised the profile of the project.
The key lesson that has been learned is that the sponsor at the highest level is not just a figure head and whilst a key sponsor may not appear to the active, the lack of a sponsor with buy in can significantly affect the success of the project.
2 Outcomes
2.1 Six Key elements of BI
2.1.1 Process Improvement
The different components of the project have resulted in a variety of Process Improvements:
-
WorkLoad Allocation Framework (WLAF) and WorkLoad Allocation Tool (WLAT) – the framework has resulted in transparency in Academic Workload across the organisation whilst the WLAT has standardised the means by which the Academic Staffing Resource is allocated and the associated data is captured by Academic Managers;
-
The data captured in (i) above produces a validated Time Allocation Survey for each member of academic staff that has greater accuracy than was the case before the introduction of the WLAT;
-
I and ii enable greater accuracy in the generation of the HEFCE TRAC return which leads to greater accuracy in the generation of the FEC;
-
The process for capturing and reporting space data both for TRAC and EMS purposes has been standardised using MS Reporting Services collecting information from an in-house developed Space Management Database. This has been informed by the methods used by other Universities which became apparent during dissemination events ie North West TRAC Group. Whilst previously there was tension between the TRAC guardian and the space manager because of the perceived complexity of reporting requirements, the development has resulted in a simpler process that involves stakeholders across the university and therefore has created greater transparency;
-
By developing the data warehouse we are able to reuse data much more efficiently for purposes such as timetabling which means that the processes for producing published data are much more timely, efficient and reliable with data not requiring multiple audit points and checks;
-
The components that are used in the costing and pricing tool include data that has been taken via the data warehouse which was generated from the WLAT, the Module Database and the Space Database. When producing proposed academic products, the automated calculations then mean that the amount of data input and calculation required from the Academic Manager is reduced significantly.
2.1.2 System Implementation
Section 2.4 provides the detail of the project specification together with the means by which the various components have been or are to be implemented. The project has impacted on a range of both developers and users and therefore the implementation has taken different forms, for example the implementation of the warehouse component has involved an external organisation (Solstone) providing advice and guidance on the development and the implementation of the warehouse together with an insight into visualisation techniques to be adopted to assist end users. The implementation of the Workload Allocation Tool and the Costing and Pricing Tool has involved staff development for the relevant Academic Managers who are involved in resource management and/or programme development. The use Workload Allocation Tool which in turn creates the Time Allocation Survey has required training sessions with groups of staff. All of these implementation steps are to continue with assistance from the University’s IS&T team including the training officers and the Staff Development Officers from the Human Resources and Organisational Development Unit.
2.1.3 Change Management
Initially the project appeared to require little in terms of change management. Whilst the project team put in place, from an early stage, a stakeholder group to inform developments, it was felt by the project team and stakeholders that the aims of the project provided tangible and real benefits in terms of better data and tools to make life easier for those managing resources when they have had little resource and finance management experience. However, as the project unfolded it became very apparent that the cultural impact that the project was spearheading required sensitive handling and careful management techniques needed to be deployed to manage the changes that colleagues were experiencing.
The University went through a major structural reorganisation which began three months into the project and had an impact on colleagues who had been in the project’s major stakeholder groups. In some instances colleagues have changed roles including moving to posts abroad and some leaving the organisation. The University has also in parallel gone through an academic review over the last 12 months which has required all undergraduate programmes to be refreshed and revalidated and, in some case, redesigned. Set against this backdrop the approach of the project team has been to introduce a wider team of stakeholders to the benefits of the various tools and to obtain feedback to inform further development.
2.1.4 Data Usage
The premise of the project was that data can be used and reused for a number of different purposes by different levels of staff. The process of integrating data sources in order to create common data definitions has led to changes in the opportunities for data usage which, albeit spear-headed by the project, has had ramifications across the university. Bringing together coding structures means that statutory returns will require less manual intervention and recoding. Trend analysis over time has been hampered in the past. Work load allocation, time allocation costing and pricing have been particularly difficult to analyse because the data structures have been patchy and inconsistent. There is also a positive impact in data usage in other areas of the University as a result of the approaches that are being put in place by the project for example timetabling, student progression/achievement/attrition.
2.1.5 Data definitions
Three groups of data have been used: Staff, University Structure and Module. Data is sourced from Student Records System and HR and Payroll and also has to be consistent with our Finance System. The requirement for university reporting in parallel to the project has underlined a need for a central group ensuring consistency of data across all systems. University Structure is a prime example of this, especially as the University is reviewing its structure. Examples of where standardisation will benefit include:
-
the name of one particular faculty uses a variety of different formatting of its title the implementation of standard data definitions will enable us to be sure that there is one common format which in turn will help in our ability to provide the users with sensible and meaningful data reports.
-
a second example is how we refer to staff by name. We have developed a standardised <surname>, <preferred forename>format which is used across the board.
-
a final example is that of a module being taught in a particular year <module code>-<year part>-<occurrence> for example ABC1234-12-A is the first occurrence (A) of a module (ABC1234) in the year 2012/13.
-
Data definitions and the data usages are being recorded in a data dictionary to enable future impact analysis of changes in data value or definition.
2.1.5 BI Maturity
During the bidding process an evaluation of the University was undertaken using the JISC Business Intelligence Infokit BI and there was somewhat of a dilemma regarding where on the maturity model the University of Bolton was positioned. On one hand the data within the university is located in the main within a number of monolithic core applications such as Tribal SITS system for the Student Data, Trent for the Human Resources Data, and these are recognised as the central “single sources of truth” for the various elements that they relate to. This means that, normally, duplicate data records are very rare. Many of the systems have been in place a substantial number of years and the acceptance of the use of central data collection and storage has been achieved by organic change management involving changing the culture of the organisation and technological advances. However, there remains a suspicion as to the robustness of the data, in particular because whilst the data collected meets the needs of the data owners, it is sometimes not fit for purpose when used for wider university application. The problems are compounded by the fact that the reporting mechanisms are devolved to Faculties, Institutes and Professional Service Units often requiring the creation of Access Queries and SQL Queries that are constructed by people who may not understand the very complex data framework upon which the reports are structured. This therefore means that whilst the data is in the main accurate it may not always be complete. The means of interrogating the data is not robust and there are few standard approaches to data mining and report generation. Also data coding within one application often does not match that within another. So from the perspective of the BI Maturity Model, the University of Bolton was deemed to be at Stage 2 in terms of having centrally managed and locally operated systems, but there remained a mistrust of the data that is retrieved from the systems which would indicate that the organisation had not fully left Stage 1.
The university has considered vendor supplied BI solutions in the past but found the development and/or licence costs to be particularly prohibitive. The maturity model suggests that at Stage 3 a vendor should be selected and indeed it was felt that the Infokit leads one to believe that the best means of developing the BI that will benefit an organisation is by securing the services and products of an external vendor. As a small organisation we find that we sometimes do not have the means to invest in the type of application that meets the needs of the complexity of the organisation, small does not necessarily mean simple. We have developed the applications therefore, sometimes with external developer contribution, using Microsoft technologies such as MS Reporting, and MS InfoPath which are more freely available across the University and are affordable under the existing Campus Agreement.
Having re-evaluated the BI Maturity Model at the end of the project we feel that we are now at Level 4 moving towards Level 5. We have not completely solved some of data definition issues but the data cataloguing and the strengthening governance means that data owners have a greater understanding of the responsibility that comes with data guardianship and there is a drive to achieve compatibility between applications.
2.2 Skills / knowledge development
The component parts of the project have had a positive impact on skills and knowledge development from a variety of perspectives:
-
Use of agile application development with external development company (Colabco) has been a very positive experience for UoB’s internal development team;
-
External expertise (Solstone) has been used to inform the development of the data warehouse and visualisation techniques to better support users;
-
Wider discussion of the data governance and usage has brought data owners and users together and broadened their knowledge of the use of data beyond their own area of responsibility.
-
Moving to a “service oriented” approach for data users has provided a different emphasis to the relationships between service providers and internal clients with providers seeking an understanding of what the internal clients need to achieve from the data rather than asking them what coding structures they need, which in the past has lead to structures that are not fit for purpose across different systems or uses;
-
The WLAT has involved skills development for resource managers. The tool together with the standardisation of allocations has resulted in this process being a simpler one to administer albeit that this has required significant negotiation across the University.
2.3 Evaluation of Incorporating Enterprise Architecture
The University of Bolton has been involved in developing expertise in Enterprise Architecture(EA) over the last two years and an output of the project was to explore the applicability of EA to the project. The project team went through a number of workshops at which the “as is” and “to be” scenarios were mapped using EA techniques and the Archimate modelling tool. An evaluation by the project team of the process and the maps that were generated, concluded that this method of mapping during the development of a complex set of applications was frustrating and was not bringing any benefits to the project. Archimate enabled the linkage between systems and service to be mapped however the complexity of the data flow in this particular project proved very difficult to articulate in a sensible way that added any real value to the project. Due to time constraints the team determined that continuing to use EA to inform the project development would create unnecessary time delays.
EA “As Is” diagram
EA “To Be” diagram
2.4 Project Specific Outputs
2.4.1 BOLT-CaP Project Development
The development of the BOLT CaP BI System was split into a number of components or phases that are expandable post project. A description of each phase can be found below.
Project Overview Diagram
2.4.1.1 Phase 1 – develop the means by which statutory return data is captured to: i. increase its accuracy and validity;
ii ensure data is used for appropriate purpose and intended output; iii. increase reuse opportunities.
A) Transparent Approach to Costing (TRAC)
Analysis of the data that is captured to complete the HEFCE TRAC statutory data return identifies that approximately 50% of the data relates to staff time and space both of which were based on data collection techniques that were not robust which was a concern across the HE sector. The TRAC data in particular is then used for benchmarking purposes but more importantly is used to create the Full Economic Costing overhead calculation which is incorporated into costing models for a wide range of University activity including Research Council Awards, academic offerings, overseas delivery (albeit adjusted to reflect different service usage). Data that is not robust potentially leads to costing models that are skewed and inaccurate. The diagram below identifies the input and manipulation of data collected for TRAC purposes at the project initiation.
Data that is collected for TRAC purposes
BoltCaP has focused on Staff and Space data which is only a small part of the whole but accounts for approximately 50% of the influence that the data has on TRAC and FEC as shown in red above
B) Development of the Work Load Allocation Framework and Tool
A significant element of the TRAC data collection requires all Universities to undertake a Time Allocation Survey (TAS) which quantifies the time and effort of full time and fractional academic staff by activity category. This data is often not validated and is based on subjective interpretations. A large part of Phase 1 focused on the understanding and development of a Work Load Allocation Tool (WLAT) which provides Academic Managers with the means to manage staff workloads and allocations in an open and transparent way. Data is taken from various primary and secondary sources to populate the tool and the Academic Managers, through a process of management planning and discussion, determine the workload of staff that is then available for a number of purposes including providing an accurate data return for TAS purposes. The creation of the TAS is automated and validated whilst achieving TRAC compliance requirements. The data that is captured within the WLAT is then reusable for use in statutory returns, Professional Development Planning process (PDP) and appraisal, academic planning and review. Further, the data captured is warehoused to be interrogated for the Bolton Costing and Pricing Tool (Bolt-CaP).
WLA Tool Screen shots
(Academic Manager Views for Work Units and Staff Allocations)
Work Units
Fig 1.0 Work Unit - Index Page
Fig 1.1 Work Unit - Details Page
Fig 1.2 Work Unit - Edit Page
Fig 1.3 Work Unit – Work Unit – Create New Page
Staff Allocations:
Fig 1.4 Staff Allocations - Index Page
Fig 1.5 Staff Allocations - Edit Page
Fig 1.6 Staff Allocations – Create New Page
Fig 1.7 Time Allocation Survey – automated collection of Workload Allocation data
C) Space and Estates Data
TRAC and Estates Management Statistics (EMS) statutory returns require the collection and collation of Space and Estates information. Analysis of the data collection methods for 2010/11 indicated that there was a lack of robustness and understanding at the operational and management level of what space and estates data were required to ensure compliance with these statutory returns. A Space Management Database has been developed to ensure that data is captured in a standard way and that changes are reflected in a timely and auditable manner. As the number of people who need to have access to the application is relatively small and therefore security/firewall/access issues have been controllable, the use of MS InfoPath screens has enabled an input and updating set of screens to be developed very cost effectively. Working with the Costing and Pricing North West Networking Group, which was initiated as part of the Jisc BoltCaP dissemination requirements, has lead to further developments in establishing an annual work flow that provides greater transparency to space and estates costs whilst maintaining compliance for TRAC and EMS purposes.
D) Timetable Planner
The University moved to central timetabling some 10 years ago and uses Celcat for this purpose. The workflow around the data being made available for timetabling purposes has been cumbersome and has involved the generation of various spreadsheets that Academic Managers have designed themselves and use for planning purposes. Different versions of the data are used in communication between the Academic Managers, the Timetabler and the Student Data Management section which is responsible for course, module and student data.
As part of the Workload Allocation Tool, the means by which the modules were validated as being available for the timetabled period needed to be addressed to try to achieve a more standard and controlled method of data generation in terms of quality, accuracy and timeliness.
To this end, a set of Timetable Planning (forms/tools/applications) have been developed from which the Central Timetable is generated. The Workload Allocation Tool is able to draw upon information from the teaching components that the Academic Managers are allocating to staff members.
The Timetable Planning Tools will be further developed post project to establish a set of web forms for ease of data input and capture.
Figure Example of the Timetable Planning Tool
E) Revalidation of UG Programmes and the Module Database
As a separate set of projects the University has embarked over the last 10 months in a process of revalidation of its entire suite of undergraduate programmes. As part of the revalidation process, modules have been reconfigured often significantly and in line with the requirements of the University’s Core Curriculum Agenda and the Key Information Sets (KIS). The modules will then be present on the newly developed Module Database from which the KIS data will be made publically available and the delivery pattern will inform the WLAT by ultimately providing the Academic Managers with a schedule of delivery into which they will need to allocate staff time.
The development of the Module Database also allows the BOLT-CaP Tool to pull costing data from the Module Database which is informed by the delivery pattern of live modules and provides the framework that surrounds the costing of new modules for planning purposes.
The development of the Module Database is of primary concern to the University because the data has operational significance ie programme deliveries are dependent on the completion of the database and the publishing of KIS is a statutory requirement. As such the completion of this project has organisational priority.
In order to provide the data requirements for WLAT, it has been necessary to create data tables within the data warehouse which provide the required data rather than extract this data from the Module Database however the WLAT web form will be updated to utilise the Module Database data when it become available and is robust.
2.4.2 Phase 2 – develop the means by which the data is warehoused to:
i. facilitate future growth and development of the data capture and BI tools;
ii. standardise terminology and create organisation wide data catalogue/dictionary;
iii. In terms of BOLTCaP project - enable the various data sources to be analysed to create and then review the costing model of a module/programme.
The warehousing of data is a new concept at Bolton. Previously data from different sources has been queried via SQL and Access queries. The project has become the launch pad for the development of a warehouse that will ultimately meet the MI and BI reporting needs of the University. This has meant that a wider range of considerations have had an impact on the development of the warehouse than those anticipated by the project’s original goals.
The warehousing of the data has two functions:
-
To provide a common data source independent of the originating application, allowing the data to be provided in a common interpretation and data type. Thus changes in the primary data source do not affect the data provided, and the primary data source can be managed for its primary role, and replaced if necessary.
-
To provide additional information over and above what is stored in a primary data source. For example tracking the full history of Status Changes in students’ enrolment records is not feasible in the Student records System, but by suitable data extraction this history is recorded. Also additional tables able to support groupings of status codes will provide an enhancement to reporting data.
Over and above these two functions the construction of the warehouse will provide a flexible cross system reporting provision for the University. This has brought a focus on the project and the reporting tools for information, for example on applications, which will feed through to the projects planning tools, for example timetable planner.
Whilst the data capture from primary data sources (SITS and TRENT in particular) has progressed well, albeit that there have been real complexities in terms of creating a common understanding of data definition and the responsibilities of data owners in an environment of integrated data usage, the really significant issues for the project have been around the need for some (secondary) data to be available from other projects that are running in parallel, in particular the Module Database from which the Key Information Sets will be taken which articulate the delivery pattern which then drives the Workload Allocation Tool. Cross membership within project teams in particular the Systems Team Leader has ensured compatibility and the maintenance of the data dictionary standards.
2.4.3 Phase 3 – Creation of a Costing Tool
Using data from all primary and secondary data sources the Costing and Pricing Tool brings together the costs for existing modules and average costs for typical modules of different categories eg Lab Based, Lecture Based, Workshop Based that are planned to be developed. The costing tool then incorporates overhead costs generated where appropriate by the TRAC data which informs the FEC overhead rate. The university operates both on and off campus which a range of delivery methods. The costing tool differentiates between the various delivery methods and generates costs including the overhead rate that reflect the delivery methods that are required, eg on campus, off campus franchise, off campus flying faculty etc.
INSERT PDF 1 = Off Campus Costing Form (NB for commercial reasons figures are test only)
INSERT PDF 2 - On Campus New Course Proposal
2.4.4 Phase 4 – Creation of a Costing Review Tool
An opportunity that has become more evident and is taking on greater importance, is that in the creation of the costing tool we are gathering the data that can then be used for monitoring and to determine variance in actual versus planned, comparability of programme costs which enables drilling down to determine reasons for variance and to inform programme redesign and resource planning. Phase 4 of the programme is discussed further in Section 3 Looking Ahead.
INSERT PDF 3 - Course Review Form
2.5 Intangible Benefits
i clearer understanding of the structures of primary and secondary data sources;
ii having to gain senior management buy-in and the change in the senior sponsor has moved the organisation forward in its appreciation of the long term value of the project and BI in its broader sense;
iii discussions have led to the acknowledgement of the need for and addressing of standard issues in the broader organisational operations ie agreeing what a working day is to be, in order to determine the work-load allocation model parameters, which is leading to greater transparency;
iv a development methodology that works across projects and ensures cross over membership so that the benefits of each project work in harmony rather than in conflict with each other;
|