Overview

Executive Summary

NB. This project is being transferred to a new code (EST109). Please refer to that brief for the current version. This is retained here for audit purposes.   

This project is concerned with the provision of a strategic reporting capability for the Estates Dept, to include the actual delivery of an agreed set of strategic reports.

In order to achieve a strategic reporting capability, we need to create a number of pre-requisite, foundation building blocks that come together to form the Estates Data Warehouse (ESDW). Further explanation is provided in the approach section.

The formation of ESDW will not only serve the requirements of this project, but act as a key enabler for all future Estates Management Information, Business Intelligence and strategic reporting projects.

Definition of Strategic Reporting

The table below compares the principle characteristics between Strategic Reporting (this project) and Operational reporting (project EST092).

 

Strategic Reporting (EST093)

Operational reporting (EST092)

1

Report contains historical data, and is concerned with aggregates, trends and patterns.

Report contains current data and is typically concerned with events that happened today or yesterday.

2

The underlying data is rarely required to be close to 'real-time'. Yesterday’s, last week’s or last month’s data is sufficient.

The underlying data needs to be up-to-date e.g. it needs to be 'real-time', or close to it. It should be comparable to the contents of the operational system 

3

Reports supports strategic and ad-hoc business decision making processes within an organisation.

Report supports a set of defined, regular and repeated operational processes.

4

Report is presented at an aggregated and summarised level, but frequently with drill-down access to transactional level data.

Report relates primarily to some form of transactional activity, and as a result is quite granular in detail.

5

The information presented can be extracted from multiple, diverse operational systems. Reports will commonly cross departmental boundaries, and may contain information with different data owners.

The information presented is extracted from a single operational system.

6

The information presented may not correlate closely with any single source of data. E.g. the terminology, column headings etc. may be quite different.

The information presented is closely correlated to its source operational system. E.g. the terminology, columns headings etc. will match operational system.

7

Report can be presented in a tabular, matrix format, but also in a number of graphical and visually helpful ways e.g. Dashboards, Graphs etc. 

Report is normally presented in a tabular, matrix format.  

8

Reporting is commonly produced using a set of Data Warehouse (DW), Business Intelligence (BI) and Management Information (MI) tools and techniques. See below.

 

 

Estates Data Warehouse

The cornerstone of this project will be the establishment of the Estates Data Warehouse (ESDW). This encompasses the key building blocks required to provide a strategic reporting capability for estates. 

The ESDW can be viewed as consisting of the following key building blocks:- 

 

Building Block

Details

1

Operational data extract and load processes.

A set of ETL processes to extract, transform and load data in to the Estates Subject Area.  

2

Estates Subject Area

A central repository designed to be able to store and manage key estates data over many years.   

3

Estates Data Mart(s)

A dynamic data storage area, designed to support fast and efficient retrieval of data.    

4

Estates Business Object Universe(s)

A tool designed to enable the easy access of Estates information using business terminology.

5

Estates Strategic Reports  

 

 

These building blocks are a pre-requisite for the creation of Strategic reporting, as indicated in the following landscape diagram:-

(See K:\ISAPPS\csg\projects\EST093 Strategic Reporting\Presentations\EST093-Estates DW V0104.pptx)

NB. The Enterprise Data Warehouse (EDW) infrastructure required for establishing ESDW does not exist and will need to be created as pre-requisite for this project. A project to look at BI Architecture (COM025) across the university is underway, and expected to deliver recommendations and standards that this project will adopt. In addition, subsequent delivery projects will be required in order to build EDW. These are also pre-requisites to this project.

Scope

 

Scope No

Description

MoSCoW

1

The definition of a set of strategic reports required by the Estates dept., including a priority order of delivery. An agreement on what will constitute the initial delivery; defined as ‘Phase 1’. This will become the scope of this project.

MUST

2

The provision of a set of Phase 1 strategic reports

MUST

3

The delivery of a self-service capability, to enable a limited number of Estates specialist users to create their own reports, based upon Business Objects universes made available as part of Phase 1

SHOULD

4

The provision of one or more Business objects universes in support of Phase 1 reporting.

MUST

5

The provision of one or more Estates Data Marts in support of the Phase 1 Business Objects Universe(s).

MUST

6

The design and creation of a partial Estates Data Warehouse Subject area (ESSA) in support of the Estates Data Mart(s) created as part of Phase 1.

MUST

7

The design and creation of a fuller Estates Data Warehouse Subject area (ESSA) We should take the opportunity to create the best ESSA building block that we can, as a clear enabler for future Estates MI/BI projects. This will save time and effort tin the long term     

SHOULD

8

The provision of various ETL (extract-transform-load) processes that can populate ESSA with appropriate operational data.

As a minimum this must encompass the data requirements for Phase 1.

MUST

9

The establishment of a set of guiding principles and processes (namely the above) that provide a clear foundation and template for utilisation by all subsequent Estates BI / MI / Strategic reporting projects.

SHOULD

10

The successful combination of Estates data with data from other subject areas, for example Finance. A key driver behind Estates strategic reporting is the ability to combine data from across the University.

SHOULD

The following diagram is a pictorial representation of the scope of EST093; and in particular it highlights the in-scope, existing and pre-requisite elements.

(See K:\ISAPPS\csg\projects\EST093 Strategic Reporting\Presentations\EST093-Estates DW V0104.pptx)

Out of Scope

 

 

What

Details

1

Provision of an Infrastructure Platform for ESDW.  

The working assumption in this brief is that an enterprise DW platform is a pre-requisite for this EST093.  

2

Operational Reporting

Operational reporting will be provided as part of project EST092.  

3

Provision of MI Dashboards

Estates Dashboards will not be provided as part of EST093. However ESDW will support such constructs should a future project wish to exploit them.  

4

Solving data quality issues within Estates operational systems. 

The project is likely to highlight data quality issues within Archibus and other source systems. The rectification of these data quality issues is outside the scope of EST093.  

 

Pre-Requisites

 

What

Details

1

Estates must provide strong and clear sponsorship and business leadership.  

Due to the nature of BI / MI style projects, success requires strong collaboration between IS and the business.   

The appointed Sponsor must engaged and available to assist the business lead and project manager when issues and escalations occur.   Will act as the ultimate decision maker within Estates.   

The appointed Business lead must also be engaged and able to broker agreement and take decisions on behalf of the whole of Estates. The business lead must have a direct line to the sponsor.  

2

There must be strong and detailed involvement from various subject matter experts within the Estates dept.  

Due to the nature of BI / MI style projects, success requires strong involvement from Estates subject matter experts.  

3

Deliverables from the BI architecture project COM025.  

The BI architecture project is due to deliver its recommendations regarding DW strategy at the beginning of July 2016*. These are likely to have impact on this project, especially on the technical design deliverables. *date as at 26/04/2016   

4

Delivery of Enterprise Data Warehouse infrastructure

COM025 will deliver a set of recommendations and blueprints; it will not deliver a working enterprise level DW solution.  

Therefore there must be follow-on project(s) to deliver key infrastructure components, for example:-

- Server Stack(s). - Infrastructure software e.g. Database

The delivery of this project is dependent upon the delivery of these (as yet undefined) infrastructure project(s).

 

5

Delivery of Enterprise Data Warehouse Tooling

 

Software tooling e.g. ETL, modelling tools

 

6

Delivery of an Enterprise Data Warehouse Support Organisation

 

Project COM025 will make recommendations regarding the required support organisation. These will be implemented by a follow up project to COM025 (not EST093).

7

Delivery of Enterprise Data Warehouse Standards & Policies

 

- Business Objects Universe design guides, - Report design guides, common look & feel. - ETL process design guide. - Enterprise Data dictionary

 

8

The availability of specialist skills required to successfully execute a DW / MI / BI project.

 

Specialist skills required :-

1. Data Modelling skills, able to work with the business to create an Estates Entity relationship model (ERM).  

2. ETL analyst / development skills, with good knowledge of data held within operational systems. In addition, the ability to analyse and understand existing Estates solutions that require re-engineering and /or replacement.

3. Enterprise Information Architect. Design of ESSA and downstream DataMart(s), based upon the existing EDW, and incorporating the Estates entity relationship model.

4. DW DBA. Creation of efficient physical databases based upon enterprise data model.

5. DW development skills, comfortable with building the transformation / load processes within the DW.

6. Business Objects designers, able to create efficient and business orientated universes.

7. Business Objects report writers. Able to work with Estates users to create sophisticated, drillable reports.

8. Business Objects trainers and support, capable of establishing a specialist-user role with Estates.

 

 

Assumptions

 

 

What

Details

1

Project will use the standard university BI/MI toolset e.g. BI Suite  

This has been confirmed.

2

Estates are permitted to access and use data not owned by Estates (e.g. finance data in held eFinancials).  

 

3

Estates will establish a BO specialist-user function to enable self-service reporting capability.   

 

 

Constraints

 

 

What

Details

1

Data Frequency

The ETL process can be computer resource intensive and may be constrained by operational system service availability or performance considerations.   It is “usual” to extract operational data no more frequently than daily, normally during non-service hours.

 

2

Data Latency

All reporting will be constrained by the frequency of the ETL process. For example, if the ETL process for an operational system runs daily, then any resultant reports will show data that is up to a day old. 

 

3

Data Granularity

All reporting will be constrained by the granularity present within the extracted data.

For example, if data is extracted at a summarised level, then reporting can only be conducted at a summarised level.

 

It is therefore usual to extract operational data at the most detailed level possible. Summaries and totals can then be created within ESDW.  

4

Data Quality

The accuracy and quality of the strategic reporting provided can only be as good as that of the source operational systems. A project of this nature is likely to uncover and highlight any data quality issues with its source operational systems, or with the way data is loaded into the DW. This may require operational business processes to be adapted, or one-off data clean-up activities to be undertaken.      

 

Success Criteria

 

 

What

Details

1

The delivery of the agreed phase 1 strategic reports.  

 

2

The establishment of the basic building blocks for ESDW, acting as a key enabler for future Estates BI/MI projects.  

 

3

Establishment of a BO specialist-user function within Estates.  

 

 

Approach

 

 

Steps

Details

Deliverables

1

Project Initiation

- Conduct business kick off sessions - Conduct technical kick-off sessions. - Create project brief  

- Project Brief

2

Define & Agree Phase 1 project scope

- Choose Subject areas to focus on.

- Map out in detail what resources (infrastructure, people) in IS and Estates will be required.  

- Scope document

- Modified estimates, plans  

3

Conduct detailed business analysis  

- Conduct detailed business analysis on chosen scope.

 

- Detailed Business Analysis. - High level Estates entity relationship model.  

4 Conduct Business Gate Review  

Business Gate review @ end of Analysis to determine whether project is in a good place to proceed.

- Review Analysis

- Review updated plan

- Review IS Estimates & skills

- Review Business resource estimates

- Review COM025 outputs, and plan for further deliverables, pre-requisities.    

- Decision to proceed

5

Conduct detailed systems analysis & design.

 

- Data Delivery Design documents (ETL, Subject Area, Data Marts) - BO delivery Design documents (Universe, Reports)  

6

Build Extract Transform Load (ETL) processes.  

 

- TBA

7

Build & Populate Estates Subject Area (ESSA)  

 

- TBA

8

Build & Populate Estates Data Mart(s)  

 

- TBA

9

Build Estates Business Objects Universe(s)  

 

- TBA

10

Build Estates Strategic Reports  

 

- TBA

11

Provide Estates with an ad-hoc, self-service reporting capability  

 

- BO Training

- BO User  Documentation  

 

Potential Candidates for Phase 1

This section is included as an indication of potential candidates for inclusion in Phase 1 ESDW.

 

Subject Area

Prty

Details

1

Space Mgmt

HIGH

Upgrade and/or re-engineer existing Space Mart solution, in support of :-  

- Estates space mgmt.

- Resource Allocation Model (RAM) in development by Governance And Strategic Planning (GASP) - Enabler for Estates Mgmt Record (EMR) process. - Use timetable data for more accurate RA2018 reporting.

 

Report RAM2018 is required for the 2017/2018 resource allocation process. Therefore needs to be available prior to this in live; for shakedown, preparation and familiarisation by 1st May 2017  

2

Maintenance Mgmt

HIGH

Create a data mart to support performance mgmt. and decision making of the new helpdesk and maintenance workflows.

 

It may be that part of this requirement will be satisfied as part of EST092 Operational reporting.  

3

Finance Mgmt  

MED

Financial data is already available as part of the enterprise DW.

For estates, summary data is extracted, and then manipulated for reporting and control purposes.

 

However, the underpinning detailed financial data is in Archibus, and there needs to be a way of cross referencing one to the other.

NB. There is a danger that 2 different (but related) flows of data could show different results e.g.

a) Summary:  Archibus -> eFin -> ESSA

b) Detail: Archibus -> ESSA?  

4

Energy Mgmt  

LOW

Monitoring & Targeting (M&T) Operational System is Optima, currently under review.

 

Optima can provide extensive reporting (operational, strategic) out of the box.

It’s envisaged that UofE energy stakeholders will use Optima directly to access detailed reporting.

 

This will satisfy any short term requirements for

However, could be some significant benefits in bringing a summarised set of energy figures into EDW to allow energy consumption etc. to be used in conjunction with other figures to calculate e.g. energy consumption per room.

At this stage, this benefit is ‘imagined’, rather than based upon any empirical requirement. 

 

HESA (Higher Education Statistics Agency) annual reporting is currently created from Optima extracts, with manual manipulation, correlation, data cleansing and validation.

There is not a strong requirement at present to automate the production of HESA energy figures.  

5

Capital Planning  

LOW

No specific operational system in place to handle Capital planning /projects. Currently data is pulled together manually from multiple sources into one complex XLS.

 

Current audit /study underway to review how Estates manage their Capital projects, and to recommend ways of working, and suitable underpinning operational systems.  

Overall, it would seem sensible for any plans to move Capital Planning data into ESDW to wait for the outputs of the audit / study.  

 

6

Asset Mgmt

LOW

Estates are in the process of starting up a project to create an Asset Register.

One for the future.

7

Supplier Mgmt  

LOW

Estates use several different procurement systems (PECOS, Sciquest, LPM part of eFin)).

Intention is to consolidate procurement into SciQuest.

 

Issue with sheer volume of different supplier – e.g. same supplier has different billing / ship-to in different systems. Monitoring spend / buying limits is a challenge.

8

Security Mgmt

LOW

Need to know how estate will grow, to forecast growth in security assets (people / technology).

 

Operational security systems in use :-

 - Incident mgmt. – Perspective (supplier = resolver)

 Provides a level of its own mgmt. reporting. Ok for the time being.

 - Access Mgmt – C*Cure (supplier = Tyco)

 

Would be interested in stats e.g. volume of alarm calls per building / analysis of CCTV breakdowns.

9

Strategic Mgmt  

LOW

Dashboards / KPIs

Possible future development.

 

These are the KPIs produced by Estates on an annual basis. They are currently collated manually from a variety of sources.

  1. Income per square metre
  2. Investment in Buildings 5% of IRV
  3. Capital spend as a % of total University income
  4. Building condition at grades A and B
  5. Cost to upgrade to condition B is 5% of IRV
  6. Maintenance Statutory Compliance
  7. Response to Helpdesk Calls
  8. Health and Safety
  9. Cleaning Services
  10. Servitorial Services
  11. Overall Department customer satisfaction level
  12. Waste Services (landfill avoidance)
  13. Teaching Utilisation measures
  14. Absence
  15. Carbon Emissions

 

Glossary of Terms

 

Term

Description  

BI

Business intelligence (BI) can be described as “a set of techniques and tools for the acquisition and transformation of raw data into meaningful and useful information for business analysis purposes”.

Business Objects (BO)

Part of SAP BI Suite. Toolset used to creates the semantic layer (universe) and reports.  

Dashboard

In management information systems, a dashboard is “an easy to read, often single page, real-time user interface, showing a graphical presentation of the current status (snapshot) and historical trends of an organization’s or computer appliances key performance indicators to enable instantaneous and informed decisions to be made at a glance.

 

Data Mart

A data mart is the access layer of the data warehouse environment that is used to get data out to the users. The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts are small slices of the data warehouse. A popular form of Data Mart is a Star Schema.

 

Data Model

A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to properties of the real world.

 

Data Warehouse (DW)

A data warehouse (DW or DWH) is a system used for reporting and data analysis. DWs are central repositories of integrated data from one or more disparate sources. They store current and historical data and are used for creating analytical reports for knowledge workers throughout the enterprise.  

DW

See Data Warehouse  

Dimensional Data Mart  

See Star Schema

Entity Relationship Model

ERM. A diagram that describes how entities are related, for example the relationship between room, floors and buildings, and the attributes of each entity e.g. ID, Size, type etc.

These diagrams are crucial in creating an efficient and effective data warehouse design. Ultimately, an enterprise entity relationship model (EERM) can describe the entire organisation in terms of data and its relationships.       

ESDM

Estates Data Mart Data marts designed to support the reporting needs of Estates.

 

EDW

Enterprise Data Warehouse   

Generic term for the Corporate Data Warehouse, storing data from across the University.   

ESDW

Estates Data Warehouse

 

A term for all the building blocks that come together to form the Data warehouse solution, centred on Estates information and strategic reporting requirements.  

 

ESSA

Estates Subject Area The subject area within EDW that organises and stores Estates related data.  

ETL

Extract, Transform and Load (ETL) refers to a process in database usage and especially in data warehousing that:

- Extracts data from operational systems

- Transforms the data for storage in a proper, long-term format

- Loads it into the final target subject area.  

MI

A management information system (MI or MIS) focuses on the management of information systems to provide efficiency and effectiveness of strategic decision making. The concept may include systems termed decision support system, expert system, or executive information system.  

Normalisation / De-normalisation

Database normalisation is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy.

Normalization involves decomposing a table into less redundant (and smaller) tables without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys. Typically used in OLTP systems, not designed for efficient reporting. Implies that one piece of data (e.g. Building name) is only stored in 1 place. 

 

De-normalisation is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. This is a technique typically used in Dimensional (Star Schema) Data marts and are designed for efficient reporting. Implies that one piece of data (e.g. Building name) could be stored in many places.  

OLAP

On line analytical processing, or OLAP is an approach to answering multi-dimensional analytical queries swiftly. OLAP is part of the broader category of business intelligence, which also encompasses relational database, report writing and data mining.

 

OLTP

On line transaction processing, or OLTP, is a class of information systems that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing. E.g. Archibus, eFinancials.

 

Semantic Layer

A semantic layer is a business representation of corporate data that helps end users access data autonomously using common business terms. It maps complex data into familiar business terms such as product, customer, or revenue to offer a unified, consolidated view of data across the organization.

 

This is the purpose of a Business objects universe.

Star Schema

Star schema is the simplest style of data mart schema and is the approach most widely used to develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing any number of dimension tables.

 

Also known as dimensional data marts.

 

Subject Area  

EDW is made up of independent storage containers known as subject areas. These subject areas can be linked together with common keys, to give access to data across the enterprise.    

Universe  

A semantic layer provided by Business Objects

 

Project Info

Project
Estates Strategic Reporting
Code
EST093
Programme
Estates Systems & Technology Maintenance (EST)
Management Office
ISG PMO
Project Manager
Chris Lawford
Project Sponsor
Karen Adamson
Current Stage
Close
Status
Closed
Start Date
17-Feb-2016
Planning Date
n/a
Delivery Date
n/a
Close Date
29-Sep-2017
Programme Priority
2
Overall Priority
Normal
Category
Discretionary