QI SymposiumExtending CMS Baseline with Data Warehouse

1
(No Transcript)
2
Part 1

• Data Warehouse for Newcomers
• (me!)
• Linda HoranCMS Data WarehouseInformation
  Coordinator

3
Data Warehouse Background-None

• December 2000
• Joined CMS Training/ Doc department
• March 2005
• Joined Data Warehouse group
• No Computer Science or Data Warehouse background
• Did the only thing I knew to do . . .

4
Searched for Context . . .

• Armed with instructional technology toolkit, the
  archaeological dig begins

5
First Artifact - a Map
Strategic
Tactical
Operational
6
A Working Definition - MW

• Data Warehousing 101
• A data warehouse is a large analytical database
  which derives its data from a variety of
  production systems and is structured for
  querying, reporting, and analysis
• by Arshad Khan

7
Production (Operational) Systems

• Online Transaction Processing (OLTP)
• Flexible, fast transaction data capture
• Normalized databases
• Relational tables

8
Normal, compared to what?

• Five Stages of Normal Forms
• Third Normal Form (3NF)
• No fields may depend on other non-key fields
• You dont need to know anything more unless you
  are a data architect

9
Its all Relative

• Method of structuring tables
• Associated to each other by shared attributes
• Tables are joined by Key(s)
• Primary
• Foreign
• Natural / Artificial

10
Normalizing a Table
11
Primary Key (PK) / Foreign Key (FK)
12
Artificial Key / Surrogate Key
13
Transaction System Benefits

• OLTP good for
• Rapid single record update
• Rapid single record retrieval
• Little risk of introducing inconsistencies
• Whats good for OLTP is not good for reporting

14
Job Report Example
15
Report with JOB and EMPLOYEE Data
16
Reporting from Production Systems

• Most reports require data from many tables
• Ad-hoc reports are difficult to create
• Programming tools required
• Data is stored in multiple applications
• Performance degrades!

17
Reporting / Analytical Systems

• Reporting needs are opposite of OLTP
• Denormalized
• Optimized for reporting and analytics
• Speeds data retrieval
• Not intended for data entryand modification

18
Denormalized

• Redundancy purposely introduced in favor of query
  performance

19
Extract, Transform, Load (ETL)
20
Dimensional Model

• The Dimensional model is based on a structure
  organized by dimensions
• Fact Table
• Contains measurements or metrics of facts of
  business processes
• Dimension Table
• Who, what, where, when, and how of a measurement
  (Fact)

21
Synonym Star Schema
22
Star Schema

• Fact Student Career Term
• Dim Institution
• Dim Term
• Dim Academic Program
• Dim Person
• And others . . .

23
Conformed Dimensions

• A dimension that has exactly the same meaning and
  content when it is referred from different fact
  tables
• PS_D_SA_PERSON
• Admissions
• Student Financials
• Etc.

24
Metadata

• Data about data
• Structural metadata refers to information about
  table structure
• Other uses of metadata
• Data definitions
• Transformations
• Date of last update

25
Online Analytic Processing (OLAP)

• OLAP Processing that supports the analysis of
  business trends and projections
• OLAP CUBE Dataset organized in a hierarchical,
  multidimensional arrangement

26
Data Mining - Top of the Pyramid

• Data mining finds patterns and subtle
  relationships in data and infers rules that allow
  the prediction of future results.
• Generally implemented using OLAP cubes

27
Other Terms of Confusion . . .
Operational Data Store
Data Mart
BUSINESS INTELLIGENCE
HistoricalReporting
Slowly Changing Dimensions(all three types!)
28
X Marks the Spot
This is all BI!
Exec
VP Level
Operational Mgt.
X
Line Mgt HR Mgt Benefit Mgt
Daily Transactions
HR Recruitment Benefits Payroll TL
29
Its Show Time!

• "In theory there is not much difference between
  theory and practice. In practice, there is."
• Yogi Berra

30
Part 2
Introduction

• The CMS Data Warehouse Project
• Donna Furon
• CMS Data WarehouseProject Director

31
Goals
Introduction

• Why is there a CMS Warehouse?
• What is provided?
• How is it working?
• What is next?
• Questions and Answers

32
Goals
Goals

• Why is there a CMS Warehouse?
• What is provided?
• How is it working?
• What is next?
• Questions and Answers

33
History
Why is there a data warehouse?

• Data Warehouse pulled from CMS scope
• Campus Efforts (SLO, Northridge, RDS)
• Data Warehouse Consortium
• CMS EC calls the question

34
Need CMS Project Directors
Why is there a data warehouse?

• Immediate
• Operational reporting
• Integrated reporting
• Long Term
• Historical reporting
• Analytics

35
Need Operational Reporting
Why is there a data warehouse?

• Transaction system complexity
• Many tables and lookups
• Cryptic names
• Cryptic codes
• CPU contention
• Tuning methodologies

36
History Approach 6/2003 11/2003
Why is there a data warehouse?

• CMS Central
• License Software Tools and Train Campuses
• Develop, Support and Maintain Baseline
• Campuses
• Acquire, Support and Maintain Hardware
• Incorporate and Support Local Modifications

37
Approach Assumptions
Why is there a data warehouse?

• Economies of scale
• Purchasing software
• Developing baseline
• Campus needs
• Hardware flexibility
• Customization ability

38
History Feasibility Study 11/2003 5/2004
Why is there a data warehouse?

• Recommended Approach versus Individual Campus
  Efforts
• 101 Savings
• Sunk Costs Considered
• EC Authorized RFP

39
Goals
Goals

• Why is there a CMS Warehouse?
• What is provided?
• How is it working?
• What is next?
• Questions and Answers

40
History RFP
What is provided?

• Complete Solution
• Tools
• Data Models for PeopleSoft Source
• Include all three PS Apps
• Train CMS Central to Distribute
• Bundle as a Single Solution
• Multi-vendor bids OK

41
History Oracle/PeopleSoft Bid
What is provided?

• EPM with JumpStarts
• Extended IBM (formerly Ascential) DataStage
  Licensing
• Authorization to Distribute Code Outside of EPM
• Multiple Platforms
• Windows/Oracle Added

42
The Data Warehouse Team
What is provided?

• Project Director
• Information Coordinator
• Lead Developer
• Two Developers
• SOSS application and HOSS technical team support
• Campuses and Chancellors Office

43
Consultant Deliverables
What is provided?

• Oracle (PeopleSoft) vanilla warehouse
• Content for all modules in
• Human Resources 8.0
• Student Administration 8.0
• Finance 8.4

44
Consultant Deliverables
What is provided?

• CMS DW team training/mentoring
• Worked side-by-side
• A lot of questions
• Modeled
• HR Time and Labor
• Finance FNAT, AAT, and Fund

45
Campus Receives
What is provided?

• Tools, Training, and Technology
• IBM DataStage Training
• Run and monitor jobs
• Install baseline updates/upgrades
• Customize
• Delivered project installed by DW team
• Report writer overview/workshop

46
Deliverables - Implementation
What is provided?

• Kickoff meeting
• Environment validation
• Installation by DW Team
• Jobs to create warehouse tables
• Jobs to refresh warehouse tables

47
Deliverables - Implementation
What is provided?

• Training IBM DataStage
• Run and monitor jobs
• Install baseline updates/upgrades
• Customize

48
Deliverables - Implementation
What is provided?

• Workshop / orientation for report writers
• Documentation
• Data warehouse specific
• Star schema design
• Surrogate IDs (SIDs)
• Dimension and Fact Tables
• Current dimensions

49
Deliverables - Implementation
What is provided?

• Workshop / orientation for report writers
• PeopleSoft specific (e.g. EFFDTs)
• Create sample report (class exercise)
• Workshop assumes
• Campus application knowledge
• Campus BI tool knowledge

50
Deliverables Ongoing Support
What is provided?

• Updates for application updates
• Projects and code for new fields
• Upgrades

51
Campus Receives
What is provided?

• Timely updates
• Evaluating patches and fixes
• Upgrades
• Access to repositories
• Help Desk support

52
Deliverables Repositories
What is provided?

• Reports
• Views
• Code

53
Campus Responsibilities
Campus Responsibilities

• Hardware support
• Customization / change control
• Updates and upgrades from baseline
• Reporting

54
Goals
Goals

• Why is there a CMS Warehouse?
• What is provided?
• How is it working?
• What is next?
• Questions and Answers

55
Pilot Campus Implementations
How is it working?

• San Luis Obispo (HR) 11/2005
• Chancellors Office (Fin) 12/2005
• Chico (Fin) 1/2006
• Pomona (SA) 2/2006
• Sonoma (SA) 2/2006

56
Lessons Learned
How is it working?

• Process validated
• Isolate training
• Improve performance
• Add CSU functionality

57
Goals
Goals

• Why is there a CMS Warehouse?
• What is provided?
• How is it working?
• What is next?
• Questions and answers

58
Scope Near Term
What is next?

• Add CSU Functionality
• 8.9 Upgrades

59
Scope Long Term
What is next?

• Historical Reporting
• Analytics
• Fusion

60
CSU Functionality
What is next?

• Data Warehouse Advisory Group
• Representatives User Groups, Systemwide, IRDs,
  HOSS, SOSS
• Prioritize CSU functionality additions
• Recommend future functionality

61
Training
What is next?

• Sessions scheduled at the CO
• Training documentation updated
• Web-FAQs in progress

62
Performance Tuning
What is next?

• Activities underway by staff
• IBM technical support calls
• Use of hash files
• IBM Consulting
• Look at code
• Recommendations software and architecture

63
Campus Implementations
What is next?

• CMS Data Warehouse is core 10/1/2008
• Training week at the CO
• Campuses being scheduled now
• One week campus implementation 1st
• 2nd and 3rd implementations shorter?

64
8.9 Upgrades
What is next?

• Human Resources 3/2006 6/2006
• Student Administration to begin no later than
  7/2006
• Finance 9/2006 12/2006
• Consulting being used

65
Summary
Summary

• CMS Data Warehouse provides
• Systemwide, supported baseline
• For operational reporting
• With campus flexibility

66
Summary
Summary

• CMS Data Warehouse is
• A supported basis for campus operational
  reporting
• An opportunity to leverage knowledge across
  campuses
• In a cost-effective model

67
Goals
Questions and Answers

• Why is there a CMS warehouse?
• What is provided?
• How is it working?
• What is next?
• Questions and Answers

68
For more information
Information

• CMS Webhttp//cms.calstate.edu
• Applications / CMS Data Warehouse
• Donna Furon dfuron_at_calstate.edu562.951.4319
• Linda Horan lhoran_at_calstate.edu562.951.4241

69
(No Transcript)