Assignment

Southern State TAFE Business Intelligence Phase 1 Scope and Business Requirements Prepared For: Terence Anderson Prepared By: Roger Williams 15/220 Collins Street,

Melbourne, Victoria, 3000 T (03) 9654 1234 F (03) 9632 4321 E [email protected]

Date: 26 November 2014 Release: 1.00 Copy: 1

CONFIDENTIAL – NOT TO BE DISCLOSED

Release 1.00/1 Page 2 of 13

Acceptance and Release Notice

This is a managed document. All copies of this document preceding this release are obsolete and shall be destroyed. This document has not been released for use until accepted by the Client and authorised by the Business Performance Consulting Manager or Project Manager.

Approved: Angus Chang Date: (Business Performance Consulting Operations Manager)

Accepted: Terence Anderson Date: (Manager, Information Systems and Services, Southern State TAFE)

Authorised: Robert Newsom Date: (Business Performance Consulting Managing Director)

Release History

Release Number

Date Reason for Release

1.00 26 Nov 2014 Release to Southern State TAFE

Distribution

Release Number

Copy Number Issued To

1.00 1 Terence Anderson

Release 1.00/1 Page 3 of 13

Table of Contents Introduction ........................................................................................................................................................... 4 

Purpose ............................................................................................................................................................ 4  Terminology ....................................................................................................................................................... 4  Project Background ............................................................................................................................................. 6  Assumptions ...................................................................................................................................................... 6 

Project Scope ........................................................................................................................................................ 7  In Scope (BPC) ............................................................................................................................................... 7  In Scope (SST) ............................................................................................................................................... 7  Out of Scope .................................................................................................................................................. 7 

Constraints ........................................................................................................................................................ 8  Initial Risks ........................................................................................................................................................ 8 

Business Requirements ............................................................................................................................................ 9  Requirement Summary ......................................................................................................................................... 9  Context Diagram ............................................................................................................................................... 10 

System Requirements ............................................................................................................................................ 11  Related Information ............................................................................................................................................... 12  System Interfaces ................................................................................................................................................. 12  Application Software .............................................................................................................................................. 12  Hardware Requirements ......................................................................................................................................... 13 

Combined SQL Server/PerformancePoint Server ..................................................................................................... 13  Disk Partitions .................................................................................................................................................. 13 

Release 1.00/1 Page 4 of 13

Introduction

Purpose The purpose of this document is to;

 Define the scope and specific high level business systems requirements that will be implemented by this project,  Identify any additional requirements or considerations that must be addressed by this project,  Agree the priority of all identified requirements.

Terminology

Term Meaning

@SST Southern State TAFE’s intranet site. All members of staff have access to the intranet.

A3 Reporting A part of Lean methodology whereby a problem, its analysis, any corrective actions, and an action plan are displayed on a single sheet of large (A3) paper, often with the use of graphics.

AD

Active Directory. A Microsoft technology used to control application and network security. AD provides the functionality needed to centrally configure and administer system, user, and application settings. AD simplifies user and computer management, enables single sign-on (SSO) access to network resources, and helps enhance the privacy and security of stored information and communications.

AQF Australian Qualification Framework

Balanced Scorecard A strategic planning and management system used to align business activities to the vision and strategy of the organisation, improve internal and external communications, and monitor organisational performance against strategic goals.

BIS Business Intelligence System – a data warehouse used by SST that contains data from Oracle Financials and QLS (finance only). The business owner of BIS is Bill Chow.

BPC Business Performance Consulting

BSC Balanced Scorecard

Chris The payroll and staff records system (a source system for the BI Project)

DR Disaster Recovery

e-Works A Group of Departments that provides e-learning strategic advisory and consultancy services

Fee-for-Service Earnings Earnings from contracting with government organisations or private companies to undertake specific work. It also includes short courses, VETASSESS earnings and Work for the Dole contracts

Foundation Level A Skills-Victoria defined level of course: Foundation level courses assist people to develop the skills they need in literacy, numeracy, language, self management and job-readiness.

Goldmine The CRM System (a source system for the BI Project)

Release 1.00/1 Page 5 of 13

IS&S Information Systems and Services, headed up by Terence Anderson.

Kaplan & Norton Perspectives

Kaplan and Norton were the original proposers of the Balanced Scorecard framework. There are four perspectives through which the organisation should be viewed: Learning and Growth, Business Process, Customer, and Financial

SST Southern State TAFE

KPI Key Performance Indicator: a metric that a company's managers have identified as an important variable reflecting operational or organisational performance

Lean

A business system for organizing and managing product development, operations, suppliers, and customer relations. Business and other organisations use lean principles, practices, and tools to create precise customer value—goods and services with higher quality and fewer defects—with less human effort, less space, less capital, and less time than the traditional system of mass production. Lean is used at SST.

LTIFR Lost Time Injury Frequency Rate

MOSS Microsoft Office SharePoint Server: used to facilitate collaboration, provide content management features, and supply access to information that is essential to organisational goals and processes.

PACCT Professional, Administrative, Clerical, Computing and Technical staff. A category of the institute’s employees.

PD Professional Development

PerformancePoint Microsoft Office PerformancePoint Server: allows customers to monitor and analyse their business as well as drive alignment, accountability, and actionable insight across the entire organisation

QLS The Student Management system (a source system for the BI Project). Contains all Student related information such as contact hours, student master data, courses, etc

Scorecard A collection of key performance indicators (KPIs) and objectives, possibly arranged hierarchically. Scorecards are used to measure multiple facets of organisation performance. See Balanced Scorecard

SCH Student Contact Hours

Skills building level A Skills-Victoria defined level of course: Skills building courses, at Certificate III and IV level are for people who want to enter a skilled trade or practise an occupation that calls for skills and knowledge beyond the basic level

Skills creation level A Skills-Victoria defined level of course: Skills creation courses, at Certificate I and II level are for people who wish to undertake basic training in an industry area

Skills deepening level A Skills-Victoria defined level of course: Skills deepening courses are at Diploma and Advanced Diploma level.

SMS The Government’s Student Management System

VETASSESS The Vocational Education and Training and Assessment Services

Release 1.00/1 Page 6 of 13

Project Background Southern State TAFE has embarked on the implementation of a Business Intelligence data warehouse to enable the production of a cross application reporting capability and BI portal. This document outlines the scope of Phase 1 of that data warehouse.

This project is to establish a new data warehouse capability within the SST IT infrastructure, laying the foundation for future reporting activities. Phase 1 (this project) will encompass the provision of the data warehouse, and the production of the dashboards and reports that are deemed the most important to implement. The project will be considered to be an advanced proof of concept, as well as providing an initial BI portal with reporting capability.

This activity will entail the establishment of the required hardware and system software to support BI reporting, extracting data from the defined source systems, processing the data into the data warehouse, and then delivering the reports in both an online and printable format.

The project will act as both a pilot, demonstrating the Business Intelligence capability, and lay the ground work for future BI reporting.

Assumptions The following high level assumptions have been made while defining the scope and the business requirements:

 The Project has endorsement and approval from SST management  Business Performance Consulting will be given timely access to any SME’s during the development  All servers (hardware and software) Infrastructure will be made available by SST according to BPC’s specifications  SST has licensing in place for all of the necessary software components that are identified in the solution architecture.

These components are likely to include Microsoft SQL Server, Microsoft Office SharePoint Services, including PerformancePoint Server

 SST staff will work on the project as determined by the Project Manager  The reporting scope is limited, and will be based on a Balanced Scorecard, plus a limited set of reports based on the same

data that has been identified as required in order to report on an defined KPIs  There will be some degree of ‘dirty’ data in source systems, but all data being loaded into the data warehouse will be

validated: dirty data will not be loaded, but will be reported and then fixed by SST as the project progresses. There will not be an initial ‘all encompassing’ data cleansing exercise done prior to starting the build.

Release 1.00/1 Page 7 of 13

Project Scope This section gives a high level description of the scope covered by this project. The items that are in scope have been allocated to either BPC, or to SST.

In Scope (BPC) The following items will be in the scope of the Business Intelligence Phase 1 project, and will be performed by BPC:

 A data warehouse on a Microsoft 2012 SQL Server Platform, including all required multi-dimensional databases  A SharePoint site that will act as the Business Intelligence Portal  A file transfer and data load mechanism in accordance with SST infrastructure standards. The data that will be loaded will

be from the following systems: o Student Management System (QLS) o Financial System (Oracle Financials) o Payroll System (Chris21) o Various Excel workbooks

 Validation of uploaded files based on rules around expected data types for the data being loaded into the DW and a method of reporting failures

 A security model for the data warehouse and the BI portal that works in conjunction with the Active Directory groups that will be established by SST

 Excel workbooks, and possibly some SharePoint screens, to allow manual data entry (where the data required for KPI reporting may not be automatically accessible from other SST systems)

 A Dashboard for displaying the institute’s revised Balanced Scorecard  PerformancePoint screens providing drill down, based on certain aspects of the KPIs (see the KPI Definition document for

more details)  Some reports based on the data in the Data Warehouse (see the Report Definition document for more details)  Multi-dimensional cubes in the data warehouse that users can connect to and query using Excel to derive their own custom

reports  Train the Trainer on the system for selected SST staff

In Scope (SST) The following items will be in the scope of the Business Intelligence Phase 1 project, and have been allocated to SST:

 Definition of Key Performance Indicators for the institute  Definition of a Balanced Scorecard that will include the KPIs  Establishment of infrastructure and installation of the required system software as specified in the Solution Architecture  Definition and creation of the Active Directory groups to control security for the data warehouse and the BI Portal  Definition of the access rights for the Active Directory groups  Setting up the SharePoint site for the BI Portal  Creating a link on the SST intranet to connect to the BI Portal  Branding and look and feel of the BI Portal SharePoint site  A preliminary data cleansing exercise using a data profiling tool to pre-empt any data issues  Development of data extract processes for the source systems in accordance with supplied specifications where direct

access to SQL tables is not possible  Review of errors generated by the ETL process  Training of SST staff once BPC has provided “train the trainer” training  Training documentation.

Out of Scope The scope of the Business Intelligence Phase 1 project will exclude the following items:

 Direct links to any systems other than those listed as being in scope  Creation of data extracts from systems other than those listed as being in scope  Recreation of the monthly financial report  Changes to the financial system (Oracle Financials) due to be implemented in calendar year 2016.  Replacement of the student management system (QLS) due to be implemented in calendar year 2016  Reconciling of uploaded data: although the data will be rejected if it has the wrong data types, there will be no validation for

example of totals against other systems

Release 1.00/1 Page 8 of 13

 A Strategy Map (which uses shapes to show the relationships between the objectives and KPIs, and uses colour to communicate how each is performing): this is currently out of scope, but could be moved into scope for phase 1 with a change request

 Reporting on individual Salaries  Use of Goldmine as a source of CRM data, although this may be a requirement in a later phase for customer contact

reporting  Use of CISCO Unified Contact Centre Manager as a source of Contact Centre data, although this may be a requirement in

a later phase for customer contact reporting  Storing or reporting on measures that have not been identified as required for this phase  with a change request  Creation of ‘Books of reports’ for information consumers.

Constraints There are both financial and time contraints on this project.

 The project must be completed by 31st December 2016;  Southern State TAFE have approved funding of $189,400 and no contingence.

Initial Risks

Description Impact Likelihood Proximity

Detail requirements are unclear. Inputs and outputs known, but details of processing requirement to be discovered.

Required output deliverables do not match customer expectations.

Moderate Months

Project is introducing system software that is new to the SST infrastructure support staff.

Possible delays in getting work performed or incorrectly implemented.

Moderate Week

Data quality being extracted is unknown. SST may have data cleansing activities to perform.

Reports being produced do not reflect the correct information from source systems

Moderate Weeks

Release 1.00/1 Page 9 of 13

Business Requirements

Requirement Summary Priorities are allocated as follows:

 Must Have (1): subject to there being project resources available, all of these items will be delivered as a part of this phase of the project

 Should Have (2): one or more of these items may be provided as a part of the solution if there is still available project resources left after the Must Have requirements have been completed. If these items are not delivered as a part of this phase of the project, they will be carried over to a later phase

 Could Have (3): If there are still project resources available after all Must Have and Should Have requirements have been completed, one or more of these items may be delivered as a part of this phase of the project.

Reqt No. Description Priority Responsibility Status Change Request Id

1 A SQL Server data warehouse comprising staging tables and multi-dimensional cubes 1 SST, BPC

2 Processes for extracting required data to flat files from SST systems (such as Chris). 1 SST, BPC

3 An ETL process for loading files previously extracted from SST systems (such as Chris), or files that are maintained directly (using Excel). The ETL process will include a data validation step

1 BPC

4 An ETL process for loading data directly from SST systems (such as Oracle Financials, QLS). The ETL process will include a data validation step

1 BPC

5 A SharePoint screen where a system administrator can control the ETL process 1 BPC

6 A SharePoint site that will act as the Business Intelligence Portal 1 BPC

7 A set of Active Directory groups to be used to control user security 1 SST

8 System security at both function level in the BI portal and at data level within the data warehouse based on Active Directory groups as defined by SST

1 SST, BPC

9 Excel workbooks for manual data entry for KPIs where the data may not be easily accessible from other SST systems 1 SST, BPC

10 A Balanced Scorecard containing at least 15 KPIs split between 4 objectives and 4 perspectives. Further detail of the KPIs is provided in the KPI Definition Detail document

1 BPC

11 PerformancePoint screens providing drill down, based on some of the KPIs. Further detail of the KPIs is provided in the KPI Definition Detail document

1 BPC

12 Some simple static reports based on data in the data warehouse. Further detail of the KPIs is provided in the Report Definition Detail document

2 BPC

13 KPI Maintenance screens that will allow administrators to adjust targets and thresholds for the KPIs 2 BPC

14 At least one screen containing static charts, based on some of the KPIs 1 BPC

Statuses are as follows:  Proposed – suggested by stakeholders but yet to be approved by the appropriate authority  Approved – approved by the appropriate authority  Implemented – delivered by the phase or stage of the project  Deleted – captured but later removed due to being rejected or amalgamated with other requirements

Release 1.00/1 Page 10 of 13

Context Diagram

Intranet

Local User 1 Local User 2 Local User 3

QLS

Oracle

Chris21

ETL and Validation

Database Server SQL Server 2012

PPS

SharePoint

Active Directory Server

System Requirements

Detail Requirement

Performance

 The application will be able to support 10 concurrent users reading, adding, deleting and updating data simultaneously

 The hardware will be located in SST’s data centre, and all users will access the application via SST’s intranet, or connect to the cubes via Excel.

 There will be enough storage for 7 years of data with associated history

Usability

 All screens must be fully rendered within 30 seconds of a user request  All reports must be fully rendered within 30 seconds of a user request  The user interface will be consistent with Microsoft User Interface Standards to the maximum

possible extent  Navigation wherever possible is to be by keyboard & mouse.  The system will provide an easy-to-use interface for the user to run any function defined as

being available for that user  The system will provide an easy-to-use interface for the user to run any report defined as

being available for that user  All reports must be exportable in Excel format

Service Standards

 The application will be capable of being available during normal business hours of 8am to 6pm

Security

 Users will be authenticated using Active Directory groups defined by SST. Only authenticated users will be allowed to access the application and the data

 Users will only be able to access functions and data that have been defined as available for their group

Audit and Control

 Yet to be determined

Legal or Corporate

 There are no legal or corporate implications for the system

Data

 Backups will be conducted by the SST IS&S team on a daily basis.  There are system interfaces to other SST systems, as defined elsewhere in this document.

ETL packages will exist to load the data from files previously extracted from those other systems.

Architecture

 The system will have a flexible design that will accommodate changes to the system in the future with as small an impact as is practical.

 Full details of the proposed system architecture will be provided in the System Architecture Document

Privacy  The application will be designed to meet Australian privacy legislation requirements.

Release 1.00/1 Page 12 of 13

Related Information The following information should be read in conjunction with this document:

System Interfaces Several input interfaces exist that provide the data needed to build the relational tables and OLAP cubes.

Maintain Fact and

Dimensional Tables

Verify input and create

staging tables

SQL Staging tables

Fact and Dimension Tables

Process Cube

Targets

Financials Students Salaries

Budgets

SQL Table Maintenance

C

SSAS OLAP Cubes

Error Reporting

As mentioned elsewhere users will need to be able to navigate to the Portal using the KBT Intranet and MS Internet Explorer.

Application Software In addition to the operating system (Microsoft Windows Server 2012) the proposed solution will utilise several core software packages as outlined below.

 Microsoft SQL Server 2012. The principal components that will be used are Reporting Services, Analysis Services, Integration Services and SQL Server.

 Microsoft Sharepoint Server 2013  Oracle Oledb 64bit drivers for Windows

Release 1.00/1 Page 13 of 13

Hardware Requirements

The single server combines both MS SQL Server and PerformancePoint Server on one machine. This server will use Raid1+0

Combined SQL Server/PerformancePoint Server

Microsoft Windows Server 2012 64-bit Microsoft SQL Server 2012 SP1 Standard Edition 2 Dual-Core 64-bit CPUs (x64) or Quad-Core CPU 24 GB RAM 500 GB Disk Space 1GB Network Interface

Disk Partitions The drive shall be partitioned with the C:\ drive having 30Gb of storage and holding the Application Software. The D:\ drive will have the remainder of the disk space and be used for the data and logs.