successful data warehouse implementation brings changes to campus culture, processes, and people ora...
TRANSCRIPT
Successful Data Warehouse Implementation Brings Changes to Campus Culture, Processes,
and People
Ora Fish
Rensselaer Polytechnic Institute
Agenda
Alignment between the Information Technology and the Business
Development Methodology Lessons LearnedDemonstration Q & A
RensselaerPolytechnic Institute
(RPI)• “We are the first degree granting technological
university in the English-speaking world”• Research University• Total Students 8,265
– Graduates: 1,378– Undergraduates: 5,164– Education for Working Professionals: 1,723
• Faculty - 450
Founded in 1824 by Stephen Van Rensselaer
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/
Fundamental Problem
Operational systems are not designed for information retrieval and analytical processing
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.
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
Implementing Data Warehouse
Technology
Alignment
Business
CampusCulture
Information Quality
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
Culture
• Promotes fact based decisions
• Requires lowering the walls across organizational boundaries
• Understanding the business enterprise across different functional areas
• Analytical culture requires different set of skills
Before, During, and After the implementation
How does the IT leads and effectively aligns Technology, Information Quality, and Campus Culture before, during, and after the Data Warehouse implementation
Implementation Methodology
Build DW Foundation
DevelopSubject Oriented
Data Marts
Release Data Martto the Campus
Next Data MartRelease Data Mart
To the Core AdministrationData stewards
Maintenance and Support
Campus Communication
Adaptation and Growth
Training
DW Program Timeline
Infrastructure Planning/StaffingSoftwareDatabase/HardwareProduction Platform
Policy Data PolicyDatamarts Finance/Research Req
Position Cntrl/LaborHuman ResourcesEnrollmentGrad Financial AidUndergrad Fin AidAdmissionsContracts & GrantsUG Prospect Fin Aid PI/Fund Info AccessAlumni/Advancemnt
Operations SupportSoftware UpgradeDatabase UpgradeHardware Growth
RolloutDev & Test
FY02 FY03 FY04 FY05
Building DW Foundation
• Organizational Structure
• Project framework and high level plan
• Building Technical Infrastructure
• Develop Data Policies and Procedures
Project Organizational Structure
Sponsorship Group(Business & IT)
Steering Committee(Business & IT)
Implementation groups(Business & IT)
Approves Next Areas
Forming Implementation groups; Defining scope and deliverables
Data Warehouse Group(IT)
Implementation issues
Alignment between the IT and the Business
High Level Analysis and Prioritization process
CONSTITUENCIES
BUSINESS PROCESSES
Enrollment Analysis X X X X X X X X
Student Pipeline Analysis X X X X X
Faculty Workload Assessment X X X X X X
Financial Analysis X X X X
Contract and Grants Analysis X X X X X
Proposal Pipeline Analysis X X X X
Financial Analysis - Research X X X X X X
Graduate Financial Aid X X X
Alumni Demographics and Tracking X X X X X X
Alumni Contact Management X X
Human Resources X X X X X
Facilities Management X X X
Prioritization Process
Value toRensselaer(Culture)
Feasibility (determined by Data Quality and availability)
HighLow
High
FM
HR
PP
GF
AC
FW
FR SP
AD
EA
FA
CG
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 – Brio, Dash Boards
• Desktop Front End tools – Brio, Excel
Data Security, Privacy and Access Policy
• 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
Security& Privacy
Access& Use
Building Subject Oriented Data Marts
• Determining Constituency• Forming Implementation
Group• Conducting interviews• Defining Scope and
Timelines• Modeling• Extracting, Transforming,
and Loading Data• Develop Security system• Testing
• Identify information gaps• Identify erroneous data• Reinforce common definitions• Establish processes to identify and
clean erroneous data• Establish processes to capture
missing data• Develop and approve Data Security
Policy• Record Meta Data – stored in
Informatica repository and accessed with Brio
Alignment between the Technology, Information Quality, and Campus Culture
Modeling: Kimball’s Bus Architecture to Subject oriented Data Marts and the Conformed Dimensions
Student Enrollment Model – one row per enrolled student per term
Student EnrollmentSnapshot
Student Dimension
Class Dimension
Student countMatriculated countCredit Hours RegisteredCredit Hours AttemptedCredit Hours EarnedOverall GPATerm GPATuition Amt ChargedTuition Fees ChargedTuition Amount BilledTuition Fees Billed
Student Key
Academic Term KeyPrim Program Major Grp KeySec Program major Grp KeyStudent keyStudent Cohort KeyClass KeyStudent Faculty Advisor Key
Class KeyAcademic ProgramBridge Dimension
Student AcademicProgram Key
Academic TermDimension
Academic Term Key
Student FacultyAdvisor Bridge
Student Faculty AdvisorKey
Student CohortBridge
Student Cohort Key
Faculty Advisor Key
Cohort Key
Student Enrollment Snapshot
Highlights of the Development – ETL Process
In addition to the data staging and development processes:
• Develop Data Quality Assurance Processes• Ensure transformations are captured• Capture data at the lowest level – no one ‘trusts’
statistics only without the supportive details• Initial and Incremental Loads
Development - Securing Data Marts
• Working with each portfolio, the IT role was to ensure that the subject oriented Data Policy is: Defined, Approved, Technically feasible, and Consistent Across The Board
• Build Security Front End application• Security Options:
– Securing schemas– Securing facts only– Securing dimensions only– Securing both facts and dimensions
Securing Facts Only
Financial Transaction FactOrg KeyFund KeyAcct KeyTime Key------------------------$
Time DimTime KeyCalendar YearCalendar monthCalendar dayDateFiscal yearFiscal Period
Account DimAcct KeyAcct codeAcct DescriptionAcct TypeVarious IndicatorsVarious attributes
Organization DimOrg KeyOrg codeOrg DescriptionOrg Financial ManagerTypeVarious IndicatorsVarious attributes
Fund DimFund KeyFund codeFund DescriptionFund Financial ManagerTypeVarious IndicatorsVarious attributes
Securing Only Identifiable Information
Student Enrollment Fact------------------------CountMatriculated CountHours RegisteredHours AttemptedCredit Hours EarnedTerm GPAOverall GPATuition PaidEtc….
Time DimTime KeyCalendar YearCalendar monthCalendar dayDateAcademic TermAcademic YearSnapshot Type
Student DimStd KeyNameDemographicsGeographic InfoMinority IndCitizenship reporting IndVarious IndicatorsVarious attributes
Student Advisor BridgeAdvisor KeyNameWeight FactorTypeRankDiscipline attributesTitleSchoolDepartmentVarious attributes
Academic Program DimAcademic Prg KeyTypeClassificationMajor/Minor/Concentration TypeMajor/Minor/Concentration Desc.CampusSchoolDepartmentOfficial Headcount IndicatorWeight FactorVarious attributes
Class DimClass KeyClass codeClass Descr
Managing Testing Sessions
• Allocating time slots• Focused – aiming to produce existing reports
and Queries• Verifying that the models do address the need• Opportunity to create more definitions,
groupings, and transformations• Great opportunity to bridge diverse groups• Further Enforce Common Definitions • Further Identify Information Gaps
Alignment between the Technology, Information Quality, and Campus Culture
Campus Communications During Testing Period
Meeting one-on-one with Campus executives (Cabinet, Deans, etc.)
– Getting feedback early on
– Engaging
– Marketing
Data Mart Release to the Core Administration/Data Steward
• Utilizing Data Mart for internal operations• More changes to the Data Mart are expected
• Establishing data cleanups queries and procedures
• Preparing for Campus release:– Developing campus training program: Developing and publishing
Dash Boards, and Brio dynamic documents– Developing operational training
Information Quality
Impacting Culture
Campus Rollout
• Developing Roll-Out strategy
• Defining roles and responsibilities
• Defining initial access level
• Recognize barriers and Setting expectations
• Designing Training Programs
• Communicate to the Campus
Alignment between the Technology, Information Quality, and Campus Culture
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. Other
Initial Tiered Access – Who will have access to what
Dash Board
Informationpublished
In Brio documents
Data in the Warehouse
Cabinet; Deans; Department Chairs; Center Directors
Core AdministrationPortfolio/Division level
Department level
Trai
ning
Hig
h
Low
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
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
Communication
• 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
– Demonstration were carried out by the original testing group– Introduced training programs and the rollout strategy– Communicated Data Policies
• Wed site
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
• 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 2
Track 3
Track 1
Brio 101
Brio 101Level 1:
Portfolio/Dept-Specific Pre-Built Docs
Level 1: Data Mart
Basics
Level 2: Advanced
Brio Documents
Dashboard & Portal trainingOne-on-one or small group format
Operational Training
Ongoing Follow-up
High
Medium
Low
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!
Adaptation and Growth
Adaptation and Growth
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
Adaptation and GrowthChanges 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
Adaptation and Growth 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
Adaptation and GrowthCultural Changes
• Empowers decision-makers: getting accustomed to information availability
• Promotes the “no walls” culture• From ‘MY Data’ to ‘Our Information’ - Data
Stewards role in improving data quality, integrity, and conformity
• Fact based decision making• Redirecting costly personnel hours • Enhancing institutional effectiveness
Lessons Learned
• Building Data Warehouse is far more than a technical endeavor: the alignment between the right technology, information quality, and campus culture has to be addressed before, during, and after the data warehouse implementation through planning, development, testing, rollout, training, and adaptation stages
• Business Sponsorship – is a must
Lessons Learned
• Properly designed Organizational Structure helps to navigate political obstacles
• Partnership with the Business users – build it alone and they will never come
• Identify your business ‘Stars’ as early as possible• JAD and RAD approaches are best fitted for the iterative
Data Warehouse development• Dash Boards – unless it is visible it is not there