From Data Warehouse to Business Intelligence: The Michigan Journey

From Data Warehouse to Business Intelligence: The Michigan Journey Presenters: John Gohsman Sean Mallin University of Michigan iStrategy Solutions

Michigan Facts 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”

iStrategy Facts Sean to complete

Presentation Outline The Data Warehouse Foundation Moving to Business Intelligence Demonstrations What’s Next

The 80s

E x e c I N F O R M A T I O N M A T U R I T Y Source: OTLP D ec isi on S u pp or t Tools: PL/1, ASI/Inquiry M an ag e m en t O p er at io n al FIN FIN DEV DEV L L HR HR STU STU PUR PUR Users: Programmers, Power (10’s) 1980s

The 90s

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 Source: Oracle Data Warehouse Tools: GQL Users: Power (100’s) Early 1990s 1980s

Strategic Data Plan published – Replace all legacy systems – Need new technical platform Bought PeopleSoft ERP Commit to replace each data set Source: Oracle Data Warehouse Tools: GQL Users: Power ( 1,000) 1980s 1995

1998 – Developed DW principles and vision – Student Recruiting and Admissions – Financials Financials delivers majority of reports via DW 2000 – Rest of Student Administration 2001 – Human Resource Management System Source: ODS and Oracle Data Warehouse Tools: PS Query and Business Objects Users: Power and Casual ( 2000) 1998-2001 1980s

2000 and Beyond

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 Source: ODS and Oracle Data Warehouse Tools: PS Query and BusinessObjects Users: Power and Casual ( 2000) 1980s 2005

Presentation Outline The Data Warehouse Foundation Moving to Business Intelligence Demonstrations What’s Next

AIMS delivers BI Strategy – Leverage BI Framework – (use framework slide?) – Issues Lack of campus readiness or awareness Silo approach Lack of applications Complicated data structures Limited tools Missing infrastructure Source: ODS and Oracle Data Warehouse Tools: PS Query and BusinessObjects Users: Power and Casual ( 2000) 1980s 2006

UM BI Framework Adapted from Gartner Users Strategy Process Organization Skills, BI Competency Community Performance mgt, methodology, education Applications and Functionality Ad hoc query/reporting, standard/canned reporting, statistical analysis, data mining, predictive modeling, presentation/alert/push technology Tools BI end user tools, BI developer tools, XCelsius, OutlookSoft Everest Infrastructure Data Warehouse, ODS, ETL, Data Quality, Metadata, Data Marts Data Sources FIN Student HR Devl Research Unitspecific

AIMS recommendations – Build awareness via BI Community BI Council – BI Community of Experts, Communications, Data, Training/Methods – Address user segments; increase market Power (1500), operational (8000), casual/guided analysis ( 10,000) – Increase tools portfolio, infrastructure Browser-based, solutions for execs, managers, etc. – Improve data structures Aggregate, derive dimensional, OLAP Incorporate into Administrative Systems Strategic Plan Advisors on Information Management Strategy (AIMS) Business Intelligence Council (BIC) Training & Methods Subgroup Communications Subgroup Data Subgroup BI Community of Experts (BICE) 1980s 2006

Web Reporting Operational Data from Multiple Sources Entry Points Power Tools

Vision Three Gateways for Reporting User Group 1 ( 10,000 users) User Group 2 ( 5000 users) Application Specific (e.g. PeopleSoft) Reporting User Group 3 ( 3000 users) Web-Based Reporting for Predefined Reports Business Objects Reporting

Create BI Council and subgroups Created annual BI Awards 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 acquire cubes for Financials and partner to develop HR metrics cube – Research archive/purge, ETL/CDC tools Source: ODS, Oracle DW, SQL Server Tools: PS Query, Business Objects, Proclarity, .Net Users: Power and Casual ( 2000) 2007-2008 1980s

Presentation Outline The Data Warehouse Foundation Moving to Business Intelligence Demonstrations What’s Next

iStrategy: HR Metrics

M-Reports

M-Reports Vision 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 (production, ODS, Data Warehouse, unit data) MAIS and University units can develop and publish content

Presentation Outline The Data Warehouse Foundation Moving to Business Intelligence Demonstrations What’s Next

Creating a BI Organization Increase size of team 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

Deliver More Solutions More cubes, dimensional models Broad content in M-Reports Dashboards (KPIs, personalized thresholds) Push (reports, alerts) Predictive Analytics Workflow Process Management

Build a solid foundation Deliver to campus – Provide different data structures and a portfolio of tools to meet different needs Engage campus – Executive leadership – Community awareness and understanding Make progress at all levels of framework Source: ODS, Oracle DW, SQL Server Tools: PS Query, Business Objects, Proclarity, .Net Users: Power and Casual (15,000) Summary 1980s

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: [email protected] [email protected]

U-M Business Intelligence Overview Enterprise Data Warehouse Oracle HE (PeopleSoft) FIN (PeopleSoft) BI Tools HR Fin ETL GL Developme nt BusinessObje cts Stu Devl PR M-Reports eResearch Microsoft ETL Reporting Copy (PeopleSoft) iStrate gy HR Internal Cubes iStrate gy FIN Proclarit y

Relational Data Warehouse Environment M-Pathways Oracle 10g Data Warehouse HE Oracle 10g PeopleSoft (Ver. 9) BusinessObjects XIR2 WebI and Infoview Legacy data sets FIN PeopleSoft (Ver. 8.8) Extract/ Transform using SQR BI Tools Load MPathways data sets Busines s Objects Predefine d Reports Ad Hoc Queries

U-M Star Schemas/Cubes: MReports Single Internal Controls Sources Purpose Star Schemas Stagin g iStrategy BI Tools Multi Purpose Star Schemas Sources Stagin g BI Tools

M-Reports Design Schema (Temp Pay Example) MPathways Security Layer Web Services MReports Portal M-Reports BLL Security Component External Customer s Business Logic Layer (written in-house) Assumption: No direct access to LCC from outside an application (including UI) Temp Pay BLL Future Temp Pay Non MReports UI Using MS Reporting Services for Grids and Graphs. Need to decide about additional sw. Get Distinct Campus Future Get Temp Pay Get Temp Pay By Funding Dept Web services are very simplistic Get Temp Pay Get Temp Pay By Funding Dept BLL Components contain all business logic Data Access Layer Data Bases M-Reports DAL Build General Parms Select Distinct Campus Temp Pay DAL Select Temp Pay DAL Components build and pass SQL/MDX Commands Cubes vs. Relational