Jisc case studies wiki Case studies / Data Warehousing
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!


Data Warehousing

This is a 'model case study' relating to the Business Intelligence Maturity Model.


New Management

BI Maturity Model Stage 1


The senior management team of a Higher or Further Education Institution ('The Institution') became concerned that existing management planning was not based on reliable evidence. They also found discrepancies between data they were given for planning and the Institution's statutory returns.


The senior management team convened a project group, including people from IT, Finance, Planning and Academic (Curriculum) management. They asked this team to deal with data quality and to identify a Business Intelligence system (BI) for the Institution. The senior managers recruited a Project Manager with experience in BI from outside the education sector.


The project was given a tight timetable, and asked to have a working BI system within 12 months.


Postpone data quality issues

BI Maturity Model Stage 2


In their initial discussions with vendors and users, the project team found that a BI system could often identify problems with data quality and might provide a way of monitoring and improving the data quality of source systems.


They therefore postponed data quality work, and included it as a requirement for their proposed BI system.


Business Intelligence (BI) System Selection

BI Maturity Model Stage 3


After reviewing the market place, and talking to several vendors with experience of the Education sector, the project team decided that a Data Warehouse approach would be best for their institution. They invited a shortlist of data warehouse BI providers to demonstrate their systems and found that it would be possible to connect their existing finance, student record, HR and estates systems to a data warehouse with overnight data refreshing done automatically. The system could even take data in from the nested Excel spreadsheets used to manage research projects.


They purchased software (at a cost of less than £45,000 for the licenses they needed, with 20% annual maintenance) and consultancy services (for about £75,000 over 2 years). The BI system was installed and working within 4 months.


Initial BI deployment

BI Maturity Model Stage 4


The software was installed quickly, and included database elements and a data warehouse building tool (specific to the Institution's preferred database).


The initial deployment of the BI system depended heavily on the spread of skills and interests in the Institution's project team and on input from the supplier's consultant (who had prior experience in the Education sector). They agreed that Student data were the priority and staff from the Registrar's office worked with IT staff and the vendor's consultant to identify the relevant data sources and design the required reports and data views.


This initial focus on Student Engagement quickly paid off with improved completion for courses, and improved student satisfaction.


Data Exception Reports

BI Maturity Model Stage 2


Once the data warehouse was built, it could produce exception reports - highlighting data with quality problems (incomplete, badly formatted, wrong dates, etc.). Data problems could then be addressed:


  • At data entry, by improved training for staff; by better drop-down lists or data masks
  • In the source data system, by highlighting errors or preventing them at entry
  • In the data warehouse (if the other routes are not practicable)


The shared dashboard views and the exception reports highlighted the importance of good quality data, and the places where errors were common. This visibility and understanding quickly led to improved data quality.


Tips - Data Quality


  1. Make sure people understand why you collect data and what they are used for

  2. Make sure people understand the varied data definitions

  3. Build in validation

  4. Have good training for data entry and data users


Adding Data Sources

BI Maturity Model Stage 5


Once the Student Records were incorporated the Institution decided to add Finance and Estates records to the data warehouse. Some data sources did not need to be incorporated into the warehouse: they could be accessed directly by the BI system. These were quick to add.


Other data sources did need to be analysed and integrated into the warehouse. The time required varied from a few hours to a few weeks, depending on the complexity and quality of the new data source.

Consultancy time was also used to prepare new reports and dashboards and to train new users.


Forecasting and Planning

BI Maturity Model Stage 6


With time, the Institution's BI system allowed them to create a system for identifying students at risk of non-completion, and to intervene early. It also allowed the costs and popularity of courses to be followed, so that new courses added were profitable from the beginning and served to attract even more students to the Institution.


Planning of Estates issues was also improved - from proactive maintenance to building renovations.