Transcript Implementing Data Warehouse solution

Data Warehousing: Changing
Campus Culture
Ora Fish, Data Warehouse Program Manager
Rensselaer Polytechnic Institute
Copyright 2005 Ora Fish RPI
Rensselaer Polytechnic
Institute (RPI)
Founded in 1824 by Stephen Van Rensselaer
 “We are the first degree granting technological
university in the English-speaking world”
 Research University with programs in Architecture,
Arts, Engineering, Humanities, Science, and
Social Sciences Rensselaer enrolls over 7,500
undergraduates, graduate, and working
professionals.
 Over 450 Rensselaer faculty members include
National Science Foundation Presidential Faculty
Fellows, members of the National Academy of
Engineering, the National Academy of Sciences, and
other eminent professional organizations.
Copyright 2005 Ora Fish RPI
Fundamental Problem
Operational systems are not designed
for information retrieval and
analytical processing
Copyright 2005 Ora Fish RPI
History of DW at Rensselaer




Fall 1998- Summer 2001: Looking for solution
Fall 2001: Budgets are approved
Fall 2001 - Jan 2002: Building infrastructure
Jan 2002 – today: Delivering Enterprise Wide Warehouse
with the following areas:
 Finance
 Positions
 Human Resources
 Student Enrollment
 Admissions
 Graduate Financial Aid
 Undergraduate Financial Aid
 Research (pre award, post award)
 Institute Advancement (in progress)
Copyright 2005 Ora Fish RPI
Data Warehouse group
 Part of the Administrative Computing within
the Division of Chief Information Office
 Total of eight employees
 Responsible for addressing campus
reporting and analytical needs
 http://www.rpi.edu/datawarehouse/
Copyright 2005 Ora Fish RPI
Our constituency
 Administrative leadership: President, VP of Finance, VP of
Student Life, Provost, Dean for Graduate Admissions, Controller,
Registrar, Dean of Enrollment, VP of Research, AVP of Budgets, etc.
 Academic leadership: Deans and Department Chairpersons,
Research Center Directors
 Core Administration: Institutional Researcher, Director of
Budgets, Director of Enrollment, Registrar, Director of Research
Administration, etc.
 Core Administration Personal: responsible for carrying out
centralized functions such as registration, admissions, payroll, etc.
 Campus Administrative Personal - Graduate Coordinator’s
Assistant, Business managers across campus, Coaches, etc.
 Faculty
Copyright 2005 Ora Fish RPI
Viewpoint
Regardless of how well designed our star
schemas are or how well the dimensions
are conformed, to be effective in
addressing campus decision support and
analytical needs the Data Warehouse
should be viewed as a service
addressing information quality and
campus culture
Copyright 2005 Ora Fish RPI
Viewpoint
The true benefits can be achieved only
when the new technology is adapted
and becomes part of our business
routine:
 Penetration takes time
 Brings transformational changes to
Processes and Culture
Copyright 2005 Ora Fish RPI
Successful Data Warehouse
implementation









Clear set of Goals and Objectives
Sponsorship
Budgeted
Dedicated staff
Strong alliance between IT and Business
Implemented as a Service
Proved implementation methodology
Addresses Information Quality
Serve as a catalyst for change
Copyright 2005 Ora Fish RPI
The Fundamental Goal
The fundamental goal of the Rensselaer
Data Warehouse Initiative is to
integrate administrative data into a
consistent information resource
that supports planning,
forecasting, and decision-making
processes at Rensselaer.
Copyright 2005 Ora Fish RPI
Data Warehouse Objectives
 Serve as an information hub for Administration
as well as the Academic Schools
 Transform Data into Information with
embedded business definitions
 Informative - Meta Data
 Intuitive for end user to perform ad-hoc
queries and analysis
 Adequate response time - Retrieved within
seconds
Copyright 2005 Ora Fish RPI
Business Sponsorship
Lack of Business Sponsorship
Prototype
 Shop around and identify area where it ‘hurts’
 Build a prototype and invite vendors to participate
 Market to the business side
Engage and build awareness
 Facilitate a visit to the peer institution
 Invite peer institution to your campus
Be aware of offering temporary solutions
 Costly in a long run
 Will have dissatisfied customers
Wait for leadership to change
Copyright 2005 Ora Fish RPI
Lack of IT Sponsorship
Typical reasons are: Lacking knowledge and/or
expertise, Do not have necessary resources;
Not enough demand or pressure from the top
Possible steps:
 Secure funding
 Bring in outside help with knowledge transfer
 Build Prototype as a joint venture
 Engage and Build awareness
 Emphasize partnership
 Engage Leadership (Business Sponsor) in
setting IT priorities
Copyright 2005 Ora Fish RPI
Budget
Budget is the true indication of sponsorship support and
priority
Hardware and software for Production, Test, and Training
environment
 Data base servers
 Data base licenses
 ETL
 Front-end
 Personnel
 Education and travel
 Consulting services
 Contingency
Copyright 2005 Ora Fish RPI
Dedicated Staff
Need dedicated personnel to carry out the
following functions










Project Manager/Champion
DBA
Modeler
ETL developers
Front end developers
Software administration and installation
Desktop support
Customer support
Campus training
Business staff and Power user
Copyright 2005 Ora Fish RPI
Alignment between the IT and the
Business in DW implementation
Alignment
Business
Technology
Architecture
Copyright 2005 Ora Fish RPI
Information
Quality
Campus
Culture
Information Quality
Accurate, Reliable, Consistent,
Relevant
 Re-enforce common definitions
 Set up processes to identify and clean
erroneous data
 Set up processes to gather relevant data
 Define policies on who will have access
to what information
Copyright 2005 Ora Fish RPI
Culture
From Transaction Processing Environment to Decision Support
Environment
The goal is to build analytical
culture that values and
promotes usage of information
in decision making
Copyright 2005 Ora Fish RPI
Culture
From Transaction Processing Environment to Decision Support
Environment
 Promotes fact based decisions where
value is placed on decisions made
through usage of information vs. supply
of data
 Lowers the walls across organizational
boundaries and promotes understanding
of the business enterprise across
different functional areas
 Analytical culture requires different set
of skills
Copyright 2005 Ora Fish RPI
Our Approach
The approach to addressing campus
informational needs can not be:
 A Project
 A Product
It is a service
Copyright 2005 Ora Fish RPI
Implementing Data Warehouse
 Build Technical Architecture
 Establish Services in support of campus
community
 Build Processes ensuring Data Quality
 Work with campus Leadership on
addressing campus analytical culture
Copyright 2005 Ora Fish RPI
Methodology
 Addresses long term solution
 Enterprise wide integrated data warehouse vs.
Departmental data mart
 Use methodology with proven success i.e. learn from
others
 Overall long term planning with short time to delivery
 Has to include all aspects of DW implementation
 Architecture addressing transformations, meta data,
security, delivery
 Campus rollout and training
 Information Quality
 Communication
 Support
Copyright 2005 Ora Fish RPI
Implementation Methodology
Campus Communication
Build DW
Foundation
Develop
Subject Oriented
Data Marts
Release Data Mart
To the Core Administration
Data stewards
Training
Release Data Mart
to the Campus
Continuing Adaptation and
Growth……
Maintenance and Support
Copyright 2005 Ora Fish RPI
Technical Architecture
DATA SOURCES
DATA ACQUISITION
systems • extraction
• transactional systems • transformation
• modeling
• loading
• operational
DATA WAREHOUSE
• central
repository
• subject-based data marts
•Conformed dimensions
• metadata
DATA DELIVERY
• user-facing
• business
applications
intelligence
• decision-support
• OLAP
• querying
• reporting
Application
Servers
Source
Database
Data
Warehouse
Web Client
Interfaces
Data
Mart
Source
Database
DATA CONSUMPTION
E
T
L
Decision Support
Servers
Metadata
Desktop
Interfaces
Other
Sources
(e.g. files,
spreadsheets)
Copyright 2005 Ora Fish RPI
Operational
Data Store
Data Cube
Building DW Foundation Technical Architecture Inventory
ERP – Banner from SCT
ETL – Power Center from Informatica
Data Base – Oracle 9i
Models – Star schemas with conformed
dimensions
 Web Front end tools – Hyperion
Performance Management (Brio), Dash
Boards




Copyright 2005 Ora Fish RPI
Building DW Foundation –
Data Security, Privacy and Access Policy
Security
&
Privacy
Access
& Use
Can be defined as striking the “right” balance between
data security/privacy and data access
 Value of data is increased through widespread access
and appropriate use, however, value is severely
compromised by misinterpretation, misuse, or abuse
 Key oversight principle:
Cabinet members, as individuals, are responsible for
overseeing establishment of data management policies,
procedures, and accountability for data governed within
their portfolio(s), subject to cabinet review and CIO
approval

Copyright 2005 Ora Fish RPI
Building Subject Oriented Data Marts
Alignment between the Technology and Information Quality
 Determining Constituency
 Forming Implementation  Identify information gaps
 Identify erroneous data
Group
 Reinforce common definitions
 Conducting interviews
 Establish processes to identify
 Defining Scope and
and clean erroneous data
Timelines
 Establish processes to
 Modeling
capture missing data
 Extracting, Transforming,
 Develop and approve Data
and Loading Data
Security Policy
 Develop Security system
 Record Meta Data – stored in
 Testing
Informatica repository and
accessed with Brio
Copyright 2005 Ora Fish RPI
Catalyst of Change








Requires marketing and PR
Communications
Cheerleading
Support at the Executive levels
Lead by individual respected by all
Offering campus training programs
“Carrots and sticks”
Re-examine existing processes: (monthend reporting)
Copyright 2005 Ora Fish RPI
Rollout
Copyright 2005 Ora Fish RPI
Recognizing Barriers
 People’s resistance to a new tool
 Expectations on information availability
and usability for decision making are low
 Habit of relying on Central Administration
to provide information, or on their own
sources (many versions of the ‘truth’)
 People will need to acquire new job skills
 Job expectations will need to change
Copyright 2005 Ora Fish RPI
Developing Common Vision
 One version of the truth –
Warehoused Information was recognized
as the only official source of data
 Data Experts across campus and across
organizational boundaries
 Partnering with Human Resources –
The DW training was included in Performance
Evaluations and Job Descriptions
 Training is mandatory at all levels
Copyright 2005 Ora Fish RPI
Communication and Buy-into
 Executive briefings:
 Emphasized changes in analytical culture
 Recognized Barriers
 Emphasized that top down approach is needed and ask
for commitment
 Demonstrated new capabilities via Dash Boards
 Demonstrated ad-hoc capabilities people within their
organization have
 Campus orientations
 Demonstrated analytical capabilities
 Introduced training programs and the rollout strategy
 Communicated Data Policies
 Wed site
Copyright 2005 Ora Fish RPI
Data Warehouse Cascaded Rollout Strategy
1. Core Administration
2. Portfolio Level
(Cabinet,
Deans, Portfolio Managers)
3. Department Level
(Directors,
Center Directors, Department Chairs,
Department Financial Managers)
4. Faculty
Copyright 2005 Ora Fish RPI
Data Mart Release to the Core
Administration
 Utilizing Data Mart for internal operations
 More changes to the Data Mart are expected
Information
Quality
 Establishing data cleanups queries and procedures
Impacting
Culture
 Preparing for Campus release:
 Developing campus training program:
Developing and publishing Dash Boards, and
Brio dynamic documents
 Developing operational training
Copyright 2005 Ora Fish RPI
Initial Tiered Access – Who
will have access to what
Cabinet; Deans;
Department Chairs;
Center Directors
Dash
Board
Department level
Analysis performed on the
pre-published dynamic
documents
Core Administration
Portfolio/Division level
Copyright 2005 Ora Fish RPI
Ad-Hoc capabilities retrieving information
from the Data Warehouse
Copyright 2005 Ora Fish RPI
Common Usage
Dash Boards
Simple click away access to the most
common topics for analysis
Pre build dynamic queries
Build to address specific needs
for information
Meta Topics and
published Stars
Ad-Hoc functionality
within specific topic
Ad-Hoc
Copyright 2005 Ora Fish RPI
Training Mix
 Brio 101
 Basic navigation and
mechanics
 Brio 201
 Advanced analytics and
reports
 Data Training
 Data mart basics, BQYs,
and star schemas
 Operational Training
 Focuses on practical
applications , delivered by
business owners
Copyright 2005 Ora Fish RPI
 Study Halls
 Informal, open agenda
 Best Practices
 Demonstration of best
practices, delivered by
business owners
 One-on-Ones
 Used to address
specific
reporting/analytical
needs
Training Program Overview
Track 1
Brio 101
Level 1:
Data Mart
Basics
Level 2:
Advanced Brio
Documents
High
Operational
Training
Track 2
Brio 101
Medium
Track 3
Low
Level 1:
Portfolio/Dept-Specific PreBuilt Docs
Dashboard & Portal training
One-on-one or small group format
Copyright 2005 Ora Fish RPI
Ongoing Follow-up
Training Philosophy
 The goal of the training program goes
beyond teaching the mechanics:
 Need to sell the Brio tool and the project
 Need to educate on the benefits of the DW
 Need to emphasize that Banner and the DW
are complementary systems, i.e.,
Need to continue and inspire!
We are changing our analytical
culture!
Copyright 2005 Ora Fish RPI
Addressing Information Quality
 Establishing processes to capture erroneous and
inconsistent data
 ETL process to identify errors
Data
 Rejecting data
 Load data and clearly label errors
 Data Audit processes
 Ensuring that the loaded data reconciles back to
the operational systems
Copyright 2005 Ora Fish RPI
Addressing Information Quality
Establishing Data Stewards roles and
responsibilities
Data
 The overall data integrity and conformity by instilling business
practices and procedures to identify and correct erroneous and
inconsistent data recorded in ERP systems
 Ensuring that Meta-data is up-to-date
 Operational Training in information applicability and usage
 Establishing processes to capture and maintain data necessary
to support decisions
 Enforcing Common Definitions by facilitating agreement across
organizational boundaries
Copyright 2005 Ora Fish RPI
Establishing services and support
 Assessments of information needs
 Expansion and enhancement of Warehoused
Information
 Expansion and enhancement of Information
Delivery solutions
 Process re-engineering
 Monitoring data quality
 Support Assessment, Planning, and Analysis
 Offering full spectrum of campus training
programs
Copyright 2005 Ora Fish RPI
Establishing services and support
Transitioning from Development to Operations
 Front-End (Hyperion Performance Suite)
Administration
 ETL (Power Center) Administration
 Desktop Support and Administration
 Data Base Administration
 Dash Board maintenance
 Brio documents development, support, and
administration
 Customer Support
Copyright 2005 Ora Fish RPI
Catalyst of Change
Processes and Culture
Copyright 2005 Ora Fish RPI
Changes in our Processes
Some examples on utilization of the warehoused information
in our operations:
Assessment and Planning




Enrollment Planning Committee meeting utilizes the
enrollment and the admission data in setting the
enrollment targets and financial aid goals as they discuss
the incoming class (how we did, quality, numbers,
diversity, etc)
Retention analysis – analyzing the admissions data to
better understand how well the incoming class may be
retained next year
Assessment of Employee retention
Assessment of Faculty renewal program
Copyright 2005 Ora Fish RPI
Changes in our Processes
Forecasting:
 Forecast current year sponsor research expenditures.
 Forecast graduate financial aid commitments
 Utilize past enrollment, retention, and financial aid information to forecast current and
future year financial aid commitments to determine the affordability of various discount
rates
 More accurately forecast research awards
 Utilizing historical research ‘success rates’ in projecting cost sharing commitments
Monitoring and compliance:
 Daily monitoring of budgets and expenditures from higher levels down to the specifics
 Monitor and review project to date budgets
 Monitoring positions budgets vs. actuals and in conjunction with estimated future earnings
are accurately projecting balances
 Monitoring the allocation of graduate financial aid
Operations
 Financial information is used in preparing and analyzing the financial statements, reconciling
between the sub-ledger and general ledger, reviewing payroll allocations
 Credit card reconciliation
Copyright 2005 Ora Fish RPI
Cultural Changes
 Empowers decision-makers: Getting
accustomed to information availability
 Promotes the “no walls” culture: Performing
analysis that could never been done before
 From ‘MY Data’ to ‘Our Information’
 Data Stewards role in improving data quality,
integrity, and conformity
 Fact based decision making
 How do we now redirect these costly
personnel hours
 Enhanced institutional effectiveness
Copyright 2005 Ora Fish RPI
Assessing Data Warehouse
Penetration and Adoption
 Number of users trained and their role in
organization
 Number of distinct users connected monthly
 Number of monthly connection
 Requests for changes and enhancements
 Satisfaction surveys
Value
 Shifting IT resources from reporting to other value
added activities
 Productivity savings on the business side
 Savings realized by better more informed access to
information
Copyright 2005 Ora Fish RPI
The Dreaded Return on Investment
Calculating ROI
 Savings in personnel and processing
 More Effective Financial Aid packaging
 Effective recruitment strategies
 Identification of retention issues to
target
 More fiscal responsiveness
Copyright 2005 Ora Fish RPI
Benefits
Fosters data integrity and conformity
 One version of the truth
 Helps to identify erroneous and
inconsistent data
 Establishing ‘data cleanup’ procedures
Value shifts from data supplier to
analysis
Testimonials
Copyright 2005 Ora Fish RPI
What’s Next
Cultural shifts: Are we Higher
Education and non for profit or
Business?
Performance planning processes and
assessments
 Cultural shifts towards developing Goals,
Objectives, measuring outcomes
 KPI, Scorecards, Metrics
Copyright 2005 Ora Fish RPI
Administrative Academic
Leadership
Leadership
Faculty
Operational
users
Business
Analysts
Pre populated
Generic
Campus Wide
Dash Boards
Pre populated
Specific Dash
Boards
Research
financials
Dash
Board
Pre build
dynamic
queries;
Meta Topics
Ad-Hoc
KPI
Scorecards
Planning and
Assessment
KPI,
Scorecards
Planning and
Assessment
Simple
Exceptions Advance
Budgeting Alerts
Analytics
Planning
Notification Assessment
Visualization
Mining
As a single source with common definitions, the Data
Warehouse is a solid foundation for Scorecards and
KPI
Informational Resources
 The data warehouse toolkit
(Ralph
Kimball)
 The data warehouse lifecycle toolkit
(Ralph Kimball)
 Data warehouse design solutions
(Christopher Adamson & Michael
Venerable)
Copyright 2005 Ora Fish RPI
Informational Resources
 Become a member of the data
warehouse institute
 Visit http://www.datawarehousing.com
maintained by DataMirror
 Subscribe to listserv from EDUCAUSE
http://www.educause.edu/memdir/cg/cg.Html
 Visit other schools web sites via
http://www.Georgetown.edu/users/al
lanr/dwconfig/
Copyright 2005 Ora Fish RPI
Questions ???
Ora Fish
[email protected]
?
Copyright 2005 Ora Fish RPI