From Data Warehouse to Business Intelligence: The Michigan Journey

(1)

From Data Warehouse to

Business Intelligence:

The Michigan Journey

John Gohsman

University of Michigan

Sean Mallin

iStrategy Solutions

(2)

• Three campuses

– Ann Arbor (40,000 students, 23,000 faculty/staff)

– Dearborn & Flint (12,000 students, 1,800 faculty/staff)

• Health System

– Includes: Medical School, 3 hospitals, 30 health centers and 120 outpatient clinics (13,000 total employees)

• Financial Picture

– Annual Budget >$4 Billion (10% from State of Michigan) – $823M research funding per year (NIH 47%, NSF 9%, DOD

8%)

– Endowment: $6 billion

• Michigan values its highly decentralized nature

– “Coordinated autonomy”

(3)

• Founded in 1999 and headquartered in Maryland; 15+ years experience in data warehousing, analytics and performance measurement across various industries

• In 2003, iStrategy released the PeopleSoft edition of

HigherEd Analytics TM _{Student module, the first packaged}

student “analytic application” for colleges and universities • Focused exclusively on analytics for higher education

• Value Proposition:

– Packaged data models and ETL enable 80+ % out-of-the-box – Extensibility, plus ongoing product lifecycle of new releases – Rapid deployment opportunity

– Leverage collective knowledge of dozens of institutions

• Product Modules: Student, Finance, HR, Advancement • ERP Vendor Integration: PeopleSoft, Datatel, Banner

(4)

Presentation Outline

• The Data Warehouse Foundation

• Moving to Business Intelligence

• Demonstrations

(5)

The

(6)

Report Effectiveness Assessment

1980s

Source:

OLTP

Tools:

PL/1,

ASI/Inquiry

Users:

Programmers,

Power (10’s)

Exec

Decision

Support

Management

Operational

I N F O R M A T I O N M A T U R I T Y FIN

(7)

The

(8)

• Established Data Administration

– Strategic Data Planning

– Policy, Guidelines (Data as an

Asset)

– Governance (Stewards/Managers,

not Owners)

– Data Modeling Services, Naming

Conventions

• Built Data Warehouse

– Financials, Human Resources,

Student, Fundraising

– Relational approach for flexibility

1980s

DA and DW

Early 1990s

Source:

Oracle

Data Warehouse

Tools:

GQL

Users:

Power

(9)

• Strategic Data Plan

published

–Replace all legacy

systems

–Need new technical

platform

• Bought PeopleSoft ERP

• Commitment to replace

each data set

1980s

SDP and ERP

1995

Source:

Oracle

Data Warehouse

Tools:

GQL

Users:

Power

(10)

• 1998

– Developed DW principles and vision – Selected Business Objects

– Implemented Student Recruiting and Admissions

– Implemented Financials

• Financials delivers majority of reports via DW

• 2000

– Implemented Rest of Student Administration

• 2001

– Implemented Human Resource Management System

1980s

ERP/DW Implementation

1998-2001

Source:

ODS

and Oracle Data

Warehouse

Tools:

PS Query

and Business

Objects

Users:

Power

and Casual

(~2000)

(11)

2000

and

(12)

• Execs wonder about ERP ROI?

– Operational efficiency but…

• Are we leveraging data for

improved decision-making

• Units making progress

– M-Dash and M-Stat in Medical

School (Xcelsius)

– UHR defines and delivers metrics;

also pushes key info via

email/Excel

• Establish Advisors on Information

Management Strategy (AIMS)

– Develop strategy

1980s

Return on ERP Investment

2005

Source:

ODS

and Oracle Data

Warehouse

Tools:

PS Query

and Business

Objects

Users:

Power

and Casual

(~2000)

(13)

Presentation Outline

• The Data Warehouse Foundation

• Moving to Business Intelligence

• Demonstrations

• What‟s Next

(14)

Research

Data

Sources FIN Student HR Devl

Unit-specific

Infrastructure

Data Warehouse, ODS, ETL, Data Quality, Metadata, Data Marts

Applications and Functionality

Ad hoc query/reporting, standard/canned reporting, statistical analysis, data mining, predictive modeling, presentation/alert/push technology

Process

Performance mgt, methodology, education

Strategy

Tools

BI end user tools, BI developer tools, XCelsius, OutlookSoft Everest

Users

Organization

Skills, BI Competency Community

•What strategy?

•Lack of

campus

readiness, skills,

or awareness

•“Silo” approach

•Lack of

applications

•Limited tools

•Complicated

data structures

•Missing

infrastructure

(15)

• Build awareness via BI Community

• User segmentation; increase „market‟

– Power (~3000), operational

(8000), casual/guided analysis

(>10,000)

• Increase tools portfolio, infrastructure

– Browser-based solutions for execs,

managers, etc.

• Improve data structures

– Aggregate, derive: add OLAP

dimensional models

• Incorporated into Administrative

Systems Strategic Plan

1980s

2006

Advisors on Information Management Strategy (AIMS) Business Intelligence Council (BIC) Training & Methods Subgroup Communi-cations Subgroup Data Subgroup BI Community of Experts (BICE)

(16)

Operational Power Tools: Business Objects, Proclarity Guided Analysis using data

from multiple sources

Entry Points

(17)

• Increase Awareness; Educate

– Created BI Council and subgroups – Created annual BI Awards

• MAIS makes parallel progress

while campus readiness improves

– Decision to upgrade Business Objects and acquire site license

– Decision to build web reporting

solution for guided analysis (internal controls, PI reports)

– Decision to purchase iStrategy

Financial module, and partner with iStrategy to develop the HR Metrics dimensional model

– Research archive/purge, ETL/CDC tools

1980s

Internal & External Progress

_2007-2008

Source:

ODS,

Oracle DW, SQL

Server

Tools:

PS Query,

Business Objects, Proclarity, .Net

Users:

Power

and Casual

(~2500)

(18)

Presentation Outline

• The Data Warehouse Foundation

• Moving to Business Intelligence

• Demonstrations

(19)

SQL Server Data Warehouse

Build Cubes (SSIS)

MS Analysis Services (OLAP) Cubes

3

Source Staging Tables

1

Bulk Load Process (SSIS)

Relational Warehouse (Star Schema Design)

Edit & Transformation (SP)

2 M-Reports Performance Point (ProClarity) MS Excel/ MS Access Open Access Reporting Tools (e.g., Crystal Reports, Business Objects, Cognos, Hyperion, Reporting Services)

BI / Information Access

Data Warehouse/BI Architecture

3rd_Party

Talent Mgmt. Software

(20)

- Average Years of service - Average Age

- Gender profile - Minority profile

Staff Mobility - Hiring - Movements -Terminations Funding Source Financial FTE Financial Measures

- Average FTE Revenue - Average FTE Expense

- Average FTE Comp Expense

Job Requisitions

- Average Open Requisitions - Average days open

- Average Days to fill

(21)

M-Reports will deliver business intelligence to users

in a customizable user interface

• Alerts, metrics and personalized reports based

on user profile, preferences and role based

security

• Guided analysis through data

• Data sourced from multiple underlying

databases (OLTP; ODS; Data Warehouse:

relational data sets and OLAP; unit data)

• MAIS and University units can develop and

publish content

(22)

Presentation Outline

• The Data Warehouse Foundation

• Moving to Business Intelligence

• Demonstrations

(23)

• Increase size of team: 18 to 27 or more

• Expand mission, increase functions

– Add

Analytical

skills

– Increase

Application Development

– Enhance

BI Community

– Increase

Consulting and Training

– Enhance

Data Administration

– Improve

Data Set Development

– Increase

Tools Support

(24)

• More OLAP dimensional models

• Broad content in M-Reports

• Dashboards (KPIs, personalized thresholds)

• Push (reports, alerts)

• Predictive Analytics

• Business Performance Management

(25)

• Build a solid foundation

– Policies, skills, DW

• Make progress at all levels of

framework

• Engage campus

– Executive leadership

– Networks

– Community awareness and

understanding

• Deliver solutions to all audiences

– Different data structures

– Portfolio of tools

1980s

Source:

ODS,

Oracle DW, SQL

Server

Tools:

PS Query,

Business Objects,

Proclarity, .Net

Users:

Power

and Casual

(15,000-18,000)

(26)

For More Information

Visit:

• www.bi.umich.edu

• http://www.mais.umich.edu/stratplan/index.html

• http://www.mais.umich.edu/reporting/index.html

• http://spg.umich.edu/pdf/601.12.pdf

Or contact:

jgohsman@umich.edu

(27)

Microsoft M-Reports Proclarity BusinessObjects ETL HE (PeopleSoft) FIN (PeopleSoft) Development eResearch Reporting Copy (PeopleSoft)

Enterprise Data Warehouse

Oracle HR Fin GL Stu Devl PR BI Tools ETL iStrategy FIN iStrategy HR Internal Cubes

(28)

Legacy data sets

M-Pathways data sets

Business Objects

M-Pathways

Oracle 10g

Data Warehouse

Oracle 10g

Predefined Reports

Ad Hoc Queries

Extract/Transform using SQR

HE

PeopleSoft (Ver. 9)

FIN

PeopleSoft (Ver. 8.8)

Load

BI Tools

BusinessObjects XIR2 WebI and Infoview

Relational Data Warehouse

Environment

(29)

U-M Star Schemas/Cubes: M-Reports

Single

Purpose Star Schemas

Internal Controls

Staging BI Tools

Sources

Multi Purpose Star Schemas

iStrategy

Staging BI Tools

(30)

M-Pathways

M-Reports Design Schema

(Temp Pay Example)

Using MS Reporting Services for Grids

and Graphs. Need to decide about additional sw.

Assumption: No direct access to LCC

from outside an application (including UI)

Web services are very simplistic

BLL Components contain all business

logic

DAL Components build and pass

SQL/MDX Commands

Cubes vs. Relational

Security Layer Web Services Business Logic Layer Data Access Layer Data Bases M-Reports Portal Non M-Reports UI External Customers Security Component (written in-house) Temp Pay

Get Temp Pay Get Temp Pay

By Funding Dept M-Reports BLL Get Distinct Campus

Temp Pay BLL

Get Temp Pay Get Temp Pay By

Funding Dept M-Reports DAL Build General Parms Select Distinct Campus Temp Pay DAL Select Temp Pay Future Future