Project Brief

Version No

Contents

Date Created

Author

Comments

V0101

Initial draft

17/07/2017

Chris Lawford

Issued to project team for review.

V0102

Modified draft

18/07/2017

Chris Lawford

Updates from team review.

V0103

Modified draft

26/07/2017

Chris Lawford

Updates from further stakeholder review. Submitted to WIS 27/07.

V01FINL

Approved Brief V01

01/08/2017

Chris Lawford

Approved by WIS 28/07

Name

Project Role

Date Signed Off

Armstrong, Alison

Senior Stakeholder

24/07

Berry, Dave

Sponsor

27/07

Carter, Alex

Programme Owner

27/07

Verbal approval given.

Fiddes, Iain

Senior Resource Manager

26/07

Comments received. Document updated.

Foggo, David

Project Team Member

26/07

Granum, Geir

Project Team Member

26/07

Grzesinski, John

Project Team Member

On holiday. Will pick up on his return.

Henderson, Gillian

Project Team Member

18/07

Comments received. Document updated.

Heyn, Ana

Project Team Member

18/07

Comments received. Documents updated.

Kraan, Wilbert

Project Team Member

19/07

Manley, Rob

Project Team Member

On holiday. Will pick up on his return

O

D

Outputs / Associated Deliverables  

Prty*

Primary Responsible

Also Involved

O01

Design of the technical environment that will underpin the EDW service.

D01

Technical Architecture Design  

M

Dev Tech

D02

Data Partitioning Strategy  

S

Dev Apps

Dev Tech

D03

Data Capacity Planning  

M

Dev Tech

Dev Apps

D04

Development Guidelines: ETL Naming Standards

M

Apps Dev  

D05

Development Guidelines: Data Loading

S

Apps Dev  

Dev Tech

D06

Development Guidelines: Parallel Processing

S

Apps Dev  

Dev Tech

O02

Design of the Data architecture strategy and guidelines for EDW

D07

Meta Data Design Strategy  

S

Info Arch

Dev Tech Tech Mgmt

O03

Design of the Security and Access control for the EDW environment

D08

EDW Security Policy  

M

Info Arch

Dev Tech

D09

EDW Security Technical Design  

M

Dev Tech

O04

Creation of the Oracle-based technical environment that will underpin the EDW service. This will encompass DEV / TEST and LIVE  

D10

EDW DEV Environment  

M

Dev Tech

D11

EDW TEST Environment  

M

Dev Tech

D12

EDW LIVE Environment  

M

Dev Tech

O05

Creation (completion) of the Microsoft Server environment to support SSIS (TEST & LIVE environments already exist).  

D13

SSIS DEV Environment (TEST / LIVE already exist). NB. This deliverable may not be needed, depending upon outcome from DTI004 project.

M

Dev Tech

O06

Reference Data Acquisition  

D14

Organisation / Hierarchy Data Design (See Notes)

M

Info Arch

D15

Organisation / Hierarchy SDS

M

Apps Dev

D16

Organisation / Hierarchy Build / Test / Deploy

M

Apps Dev

O07

Static Dimension Creation  

D17

Time dimension Data Design  

M

Info Arch

D18

Time SDS  

M

Apps Dev

D19

Time Build / Test / Deploy  

M

Apps Dev

O08

Evolve existing stakeholder organisations to support future EDW change and BAU activity.  

D20

Development Organisation: Adapted Organisation, Processes, Roles & Responsibilities.

S

Senior Supplier

D21

Service Management Organisation: Adapted, Processes, Roles & Responsibilities.  

M

Service Mgmt

D22

Production Management Organisation: Adapted, Processes, Roles & Responsibilities.  

M

Prod  Mgmt

What

Details

OS01

EDW Processor Capacity Planning.  

We cannot accurately conduct capacity planning at the processor (“horsepower”) level until we have started to acquire business data and understand in detail the design solution.

OS02

Project Services Organisation: Adapted Project Management Model

This will form part of a separate implementation project.

OS03

Acquisition of Business data (other than Org/Hierarchy).

Actual Business Data Acquisition is outside the scope of this project.

What

Details

SC1

EDW is deemed to be ‘Production Ready’ and the programme milestone is signed-off.    

EDW is capable of supporting a limited service.

SC2

Estates solution for Maintenance Mgmt has been developed in parallel with DTI006, and will be ready for deployment within 3 months of EDW being ‘Production Ready’

Milestone

Date

Details

1. Project Planning

21-Jul-2017

2. Design Phase complete

29-Sep-2017

3. Org Hierarchy ASOR

24-Nov-2017

Org/hierarchy signed off in test.

4. Infrastructure build complete

6-Dec-2017

All infrastructure signed off in dev, test and live.

5. Deployment

8-Dec-2017

“Production Ready” programme milestone reached.

6. Project Close

12-Jan-2018

Phase

Activity

Task

A

Planning 

A01

Project Brief

A02

Planning Sign-off

B

Policies & Strategies

B01

Security Policy

B02

Meta Data Strategy

C

Technical Design

C01

Technical Architecture Design

C02

Security Design

C03

Data Partitioning Strategy

C04

Capacity Planning

C05

Character Sets

D

Environment Build & verification

D01

Oracle

DEV / TEST / LIVE

D02

SSIS

DEV

E

Org / Hierarchy / Time

E01

Design (SDS)

E02

Build ETL

Source -> Foundation Foundation -> Access

E03

Build Universe

E04

Test

System Test Integration Test

E05

Deploy

F

Application Development Guidelines

F01

ETL Naming Standards

F02

Data Loading Guidelines

F03

Parallel processing guidelines

G

Organisation

G01

Development Organisation RACI

G02

Service Management

G03

Production Management

H

Project Close

H01

Closure Report

JIRA

Description

Action

COM029-5

Produce detailed (final) ETL flow

To be closed

COM029-4

Identify existing Direct MI reports

To be closed

COM029-35

Ensure additional security considerations included in future EDW designs

Included in project. Part of Security policy / design

COM029-11

Investigate entity naming convention

Included in project. Part of naming conventions task

COM029-22

Investigate loading from source to staging area including performance review

Included in project Part of capacity planning

COM029-2

Investigate automated testing of SSIS

Not required.

COM029-12

Define SSIS naming conventions / standards

Not required.

COM029-32

Move SSIS to an database server

Not required, unless SSIS chosen as interim tool.

COM029-28

Archiving Strategy

Not part of this project. Will be part of DTI021

COM029-7

Review security documents and update TAD

Included in project. Part of security policy / design

COM029-26

Determine Metadata Strategy

Included in project.

COM029-25

Determine Partitioning Licensing and Strategy

Included in project

COM029-27

Determine Sizing on EDW

Included in project Part of capacity planning.

COM029-23

Define governance of foundation layer

Not part of this project. Will be part of DTI022

Note

Details

N01

Org / Hierarchy

As part of service excellence, a project will be launched to look at the organisation and workflow hierarchies for core systems

This is an extract from the project proposal:

“The organisation hierarchy provides a common core for reporting across the University. In addition, it is common for applications to use the organisation hierarchy for approval purposes in management workflows. The current structure of the organisation hierarchy has significant limitations which will impede the successful rollout of new core systems. A report by the BI/MI programme identified a number of limitations, and others arise from consideration of workflow management.

This collaborative project will analyse the requirements for hierarchies, noting there may be more than one. It will update the existing hierarchy in light of these requirements and provide models for structuring new core systems.”  

This sits within the core system remit (Sally Priestley).  

It therefore makes sense that any work we do to create an initial org/hierarchy dimension fits in with this.

Description

Impact / Probability

Management Approach

R01

The use of a non-strategic ETL tool  

MED / MED

Accept

R02

Insufficient capacity (data / processing)  

LOW / LOW

Reduce by performing capacity planning exercise.

R03

Org / Hierarchy design doesn’t match future strategy  

LOW / LOW

Reduce, by ensuring close collaboration with Service Excellence.

R04

EDW not ready in time for EST093.

HIGH / LOW

Reduce, if necessary by reducing scope to meet must haves.  

R05

Availability of key development staff

HIGH / LOW

Reduce. Project is priority 1. Key resource managers have pre-agreed to required EDW programme resource levels.

R06

Delays due to non-availability of SSIS development environment, should we need it.

MED / LOW

Avoid. We hope to build using ETL tool of choice. If not we’ll need to create an SSI dev env, possibly in a short timeframe.  

R07

Performance issues due to shared SSIS environment

Accept. There is a Test and Live Shared database environment with SSIS installed. However during ETL discussions for DTI004 it was mentioned that SSIS uses a lot of memory and may be better in a dedicated environment. If we do have to build the shared database environment for dev with the aim of using SSIS in this environment we should monitor resources when ETL processes running. Probably acceptable as an interim measure with small dataset

Who

Brief Sign-off required?

Role

Communication Plan

Armstrong, Alison

Y

DTI Portfolio manager

Senior Stakeholder  

Berry, Dave

Y

Sponsor

Senior Stakeholder

Carter, Alex

Y

Programme Owner

Service Owner

Senior Stakeholder

Fiddes, Iain

Y

Senior resource manager

Senior Stakeholder

Foggo, David

Y

Prod Mgmt lead

Project Team

Graham, Nick

N

Project Services

Interested 3^rd party

Inter project dependency

Granum, Geir

Y

Dev apps (BI) lead

Project Team

Grzesinski, John

Y

Service Mgmt Lead 

Project Team

Henderson, Gillian

Y

Dev Tech lead

Project Team

Heyn, Ana

Y

App Mgmt Lead

Project Team

Kraan, Wilbert

Y

IA Lead

Project Team

Lang, Mark

N

Dev Tech Mgr.

Interested 3^rd Party

Larnach,  Heather

N

Tech Mgmt Lead

Senior Stakeholder. Delegated project team responsibility to David Foggo.

Lawford, Chris

Y

Programme / Project Manager

N/A

Ma, Defeng

N

Apps Dev Manager

Interested 3^rd Party

Manley, Rob

Y

Dev Apps lead

Project Team

McFarlane, Andrew

N

Service Mgmt BI Manager

Interested 3^rd Party

Middlemass, Craig

N

MI/BI SME

Interested 3^rd Party

Priestley, Sally

N

Service Excellence Programme Manager

Interested 3^rd Party

TBA

N

Service Operations Manager

Interested 3^rd Party

Communication Plan

Details

01

Senior Stakeholder

- Receives Monthly Project report

- Receives regular updates from Programme Manager  

02

Project Team  

- Attends regular team meetings

03

Interested 3^rd Party  

- Receives Monthly Project report

04

Inter project dependency

- Regular catch-up meetings between PMs

Term

Description  

Access Layer

A conceptual space within EDW.

Data is transferred from the Foundation layer, using ETL tooling.

It is the layer that contains the solution specific data marts and start schemas, and is designed for targeted, quick and efficient access.

The access layer utilises the concept of dimensional modelling.

It is normally considered an externally focussed space, with many users accessing data via tools such as Business objects etc.

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.

Dimensional Data Mart  

See Star Schema

DW

See Data Warehouse

EDW

Enterprise Data Warehouse

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

The Enterprise Data Warehouse service will:

• provide a platform to store University data translated into University terminology for management information and business intelligence purposes
• manage a service for the extraction and translation of data from source systems ensuring that data is consistent with the source system
• provide high quality, performant, access to the data in the EDW for consumption by BI tools
• curate data definitions and purpose for each of the EDW elements
• advise and respond to changes required to support strategic decision making and University reporting needs
• reduce the coupling with source systems and BI tools, thus simplifying the migration from one vendor to another  

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.

Foundation Layer

A conceptual space within EDW.

Data is transferred from the Staging layer, using ETL tooling.

It is the layer that contains the enterprise data model, and is at the very heart of EDW.

The enterprise data model utilises the concept of hyper generalised modelling.

It is considered an internal space, with only very specialist users able to gain access.

Hyper Generalised Modelling (HG)

A technique for modelling data and its relationships.

This is used within the foundation layer.

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.

Slowly Changing Dimension (SCD)

Dimensions that change slowly over time, rather than changing on regular schedule, time-base.

A good example is the universities organisational structure. This data is relatively static, but does change from year to year. However, it is usually important to track these changes, to enable accurate historical reporting.

Staging Layer

A conceptual space within EDW.

The staging layer is used for ETL activities such as loading, validating and transforming data.

It is normally considered an ‘internal’ space, not accessible to users.

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.

Static Dimension

Data that is required within a data Warehouse but doesn’t change (as part of any regular update activity).

Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with status codes — or it can be generated by a procedure, such as a date or time dimension.  

Subject Area  

The foundation layer within EDW will conceptually be made up of business specific containers known as subject areas. These subject areas will be built over time, as and when business driven projects deliver data into EDW.  The subject areas can be linked together in many different ways, to provide enterprise wide information.

Universe  

A semantic layer provided by Business Objects

Version No

Comment / Response

Date

Who

Document Updated?  In Version? When?

V0101

C: From the estimation; we are missing the deployment checklist and also the handover to production; for both task you will need to book Application and Technology Management plus the developers for the handover.

Also in the scoped under Reference, it says “Production Management (Ops)” but I cannot see any deliverable for that; what do we want to achieve there.

R: I will update the estimate sheet with the tasks you mentioned.

For the scope diagram, it is supposed to represent (at a high level) that something ‘new’ is coming into the production environment, even if it is going onto existing infrastructure.  I think it’s reasonable to expect that we may assess some of the production management support processes, particularly between yourselves and service mgmt. I would expect there to be some sort of high level RACI available that explains these support processes. All up for discussion later. I will add a small deliverable into the planning so we don’t forget.  

17/07

Ana Heyn

Yes. V0102 18/07

V0101

C: As discussed as some of these are joint responsibilities spanning teams, might be useful to have main teams/resources listed along with one team for overall responsibility. Although I can see the teams are included in the task breakdown.

D02 – Data Partitioning Strategy is combined work of Development Technology and Development Team. I will feed into and review strategy in conjunction with developer and Dev Tech would implement this. 

D03 – Similarly Data Capacity Planning is combined work of Development Technology and Development Team with some Business Input. I will carry out some investigation in conjunction with developer but need input from someone with detailed understanding of subsets of data being migrated and level of change.

D05 – Development Guidelines: Data Loading – Development Technology will input into this. This is one of key steps where performance may be an issue.

D06 – Development Guidelines: Parallel Processing – Development Technology will input into this and assist in implementation. This is another step where performance may be an issue.

D07 – Dev Tech/Tech Man should input into this for some infrastructure related statistics.

D08 – EDW Security Policy –Dev Tech Input into this

D13 –There is a Test and Live Shared database environment with SSIS installed. However during ETL discussions for DTI004 it was mentioned that SSIS uses a lot of memory and may be better in a dedicated environment. If we do have to build the shared database environment for dev with the aim of using SSIS in this environment we should monitor resources when ETL processes running. Maybe this will be acceptable as interim measure with small dataset – A more detailed risk around this would be wise.

Other than that I’m happy with the content of the brief.

R: Thanks for the feedback. I have split the column in section 3 into ‘primary responsible’ and ‘involved’ to reflect your comments.  I have also added a risk around SSIS.

18/07

Gillian Henderson

Yes. V0102 19/07

V01

Production mgmt Responsibilities. Added David Foggo as Heather’s delegate.  

19/07

Heather Larnach

Yes.

V0102

19/07

V02

Just a couple of silly questions from me. Under COM025 we started an SLA for the EDW Service, is this something that will be completed as part of the Service Management BAU? It would be useful in helping shape the service and the supporting processes (https://uoe.sharepoint.com/sites/com025biarchitecture/_layouts/15/WopiFrame.aspx?sourcedoc=%7BF2CEC126-BAC0-4A98-8085-6DA465577482%7D&file=Service%20Level%20Agreement%20for%20Enterprise%20Data%20Warehouse%20(NEW).docx&action=default). There were some supporting process maps at the back of the Outcomes and recommendations (https://uoe.sharepoint.com/sites/com025biarchitecture/_layouts/15/WopiFrame.aspx?sourcedoc=%7B2EBC2B4A-D5AE-40A0-9725-4AD3C192E51F%7D&file=PoC%20Goal%20and%20outcomes.docx&action=default)

CL: This is interesting. From my perspective I think it’s hard to talk about EDW as a service, where the service is actually supplied end-to-end via a BI service. I think any SLA has to be created from that perspective. Within that, it may be appropriate to create an OLA for EDW, so that we can define what levels of support we can expect from production mgmt /application mgmt / service mgmt.

The process maps looks useful – I will add some references to COM025 in the brief, so we can dig them out when the times comes.

I’ve copied John, Ana, David in case they would like to comment.          

25/07

Craig Middlemass

Yes V0103 26/07

V02

Organisation / Hierarchy data.  I can well understand why you’ve identified this as a fundamental building block of first delivery & readiness.  It’s obviously necessary.  Is it sufficient?   It occurs to me that a Time / Calendar dimension (which presumably you’ll need) might also have the potential to generate costly subsequent reworking if not got right very early in the design & development process. 

CL: Time dimension is included within scope. But I need to make it more explicit. 

26/07

Nick Graham

Yes. V0103. 26/07

V02

P10 – Communication Plan.  As an interested 3rd party, specifically concerned with the dependencies around tool choice, I think we might want to agree & document something closer than receipt of monthly report as formal comms link between DTI004 & DTI006 – say a regular active review of risks and dependencies around the choice of ETL tool? 

CL: Good point. I’ll add something into the brief to cover what we would have done anyway.

26/07

Nick Graham

Yes. V0103. 26/07.

V02

Chris, can I ask how you feel about the milestones for the project? I’m wondering if we have a satisfactory number given the duration of the work and the importance that we track its progress satisfactorily?

Otherwise happy to proceed

Iain

CL: Hi Iain, fair point, I will add some. I think we can usefully track the set-up of the live environment, deployment of org/hierarchy data.

26/07

Iain Fiddes

Yes. V0103 27/07