1 / 33

From Data Warehouse to Business Intelligence: The Michigan Journey

From Data Warehouse to Business Intelligence: The Michigan Journey. John Gohsman University of Michigan. Presenters:. Sean Mallin iStrategy Solutions. Michigan Facts. Three campuses Ann Arbor (40,000 students, 23,000 faculty/staff)

armine
Download Presentation

From Data Warehouse to Business Intelligence: The Michigan Journey

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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 and120 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. iStrategy Facts • Sean to complete…

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

  5. The 80s

  6. I N F O R M A T I O N M A T U R I T Y Source: OTLP Tools: PL/1, ASI/Inquiry Users: Programmers, Power (10’s) FIN PUR STU DEVL HR 1980s

  7. The 90s

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

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

  10. Source: ODS and Oracle Data Warehouse • 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 Tools: PS Query and Business Objects Users: Power and Casual (~2000) 1998-2001 1980s

  11. 2000 andBeyond

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

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

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

  15. Users Strategy Process Performance mgt, methodology, education Organization Skills, BI Competency Community 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 Research Unit- specific FIN Devl Student HR UM BI Framework Adapted from Gartner

  16. Advisors on Information Management Strategy (AIMS) Business Intelligence Council (BIC) • 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 Training & Methods Subgroup Communi-cationsSubgroup Data Subgroup BI Community of Experts (BICE) 1980s 2006

  17. Web Reporting Operational Data from Multiple Sources Power Tools Entry Points

  18. Vision

  19. Source: ODS, Oracle DW, SQL Server • 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 Tools: PS Query, Business Objects, Proclarity, .Net Users: Power and Casual (~2000) 2007-2008 1980s

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

  21. iStrategy: HR Metrics

  22. M-Reports

  23. 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

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

  25. 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

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

  27. Source: ODS, Oracle DW, SQL Server • 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 Tools: PS Query, Business Objects, Proclarity, .Net Users: Power and Casual (15,000) Summary 1980s

  28. 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 smallin@istrategysolutions.com

  29. U-M Business Intelligence Overview Devl PR Stu GL Fin Enterprise Data Warehouse BI Tools HE(PeopleSoft) Oracle HR FIN(PeopleSoft) BusinessObjects ETL Development eResearch M-Reports Microsoft Reporting Copy (PeopleSoft) ETL iStrategy HR Internal Cubes Proclarity iStrategy FIN

  30. Relational Data Warehouse Environment M-Pathways Oracle 10g HE PeopleSoft (Ver. 9) BI Tools BusinessObjects XIR2WebI and Infoview Data Warehouse Oracle 10g Legacy data sets Business Objects FIN PeopleSoft (Ver. 8.8) Predefined Reports M-Pathways data sets Extract/Transform using SQR Load Ad Hoc Queries U-M Business Intelligence Overview

  31. U-M Star Schemas/Cubes: M-Reports Internal Controls Single Purpose Star Schemas Sources Staging BI Tools iStrategy Multi Purpose Star Schemas Sources Staging BI Tools

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

More Related