Enterprise Data Warehouse (EDW) at University of Hertfordshire

Requirement

The University of Hertfordshire (UoH) have a number of different management information systems including Campus IT for Student management, Technology One for Finance, Core International for Human Resources and Payroll and Kinetics for Accommodation. UoH run OBIEE 11g and use Oracle Data Integrator (ODI) for their Extract Transform and Load (ETL).

The University wanted eye catching dashboards for senior management with a mixture of tables and charts (where appropriate). Their first priority was to model the UoH finance system.

UoH engaged Prōject to perform the following:

  • Facilitate project requirements gathering / scope definition and sign off
  • Document a migration strategy for the existing Financials content in OBIEE
  • Complete a high level design for the foundations of an Enterprise Data Warehouse (EDW), based on the above requirements
  • Develop an EDW prototype/pilot to support the above requirements and showcase functionality to the business

Solution

During a typical Data Warehouse project, the high level design for the whole data warehouse is completed upfront. Data entities are catalogued across the enterprise (driven by business process analysis), leading to the identification of a conformed set of dimensions and measures for the warehouse. This approach can last several months, depending on the number of source systems and data scope. Customer constraints did not allow for the above approach, and the UoH required a prototype to be ready within a much shorter timeframe.

Taking a pragmatic workaround to the time constraint, Prōject resources started with a single data source and extracted only the data that was mastered within that data source. Where data was duplicated and/or mastered in another system a temporary feed was introduced, until such time that the other system is brought into the Warehouse.

The main risk with not designing the whole Warehouse up front is that when additional source systems are brought into the design, parts of the existing design may have to be revisited. Prōject mitigated this risk by continually cross-checking with the other data sources, confirming where each data element was mastered and/or duplicated.

Prōject followed a design similar to Oracle’s pre-packaged BI Applications. The latest release of Oracle’s BI Applications leverage OBIEE and ODI as the ETL tool. Following a similar blueprint has helped UoH to ensure that best practices are adhered to, and that Oracle reference materials can be utilised e.g. Warehouse and metadata naming conventions, Warehouse schema and ETL structures.

By initially focusing on a single source system, Prōject was able to rapidly build a prototype for business sign-off, moving quickly into warehouse design and ETL build.