Data Warehousing Changing Campus Culture Ora Fish Data

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 o o o “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 o o 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: n Finance n Positions n Human Resources n Student Enrollment n Admissions n Graduate Financial Aid n Undergraduate Financial Aid n Research (pre award, post award) n Institute Advancement (in progress) Copyright 2005 Ora Fish RPI

Data Warehouse group o o 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 o 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. o Academic leadership: Deans and Department Chairpersons, Research Center Directors o Core Administration: Institutional Researcher, Director of Budgets, Director of Enrollment, Registrar, Director of Research Administration, etc. o Core Administration Personal: responsible for carrying out centralized functions such as registration, admissions, payroll, etc. o Campus Administrative Personal - Graduate Coordinator’s Assistant, Business managers across campus, Coaches, etc. o 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: o Penetration takes time o Brings transformational changes to Processes and Culture Copyright 2005 Ora Fish RPI

Successful Data Warehouse implementation o o o o o 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 o o o 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 n n n 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 n n Facilitate a visit to the peer institution Invite peer institution to your campus Be aware of offering temporary solutions n n 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: o Secure funding o Bring in outside help with knowledge transfer o Build Prototype as a joint venture o Engage and Build awareness o Emphasize partnership o Engage Leadership (Business Sponsor) in setting IT priorities Copyright 2005 Ora Fish RPI

Budget is the true indication of sponsorship support and priority Hardware and software for Production, Test, and Training environment o Data base servers o Data base licenses o ETL o Front-end o Personnel o Education and travel o Consulting services o Contingency Copyright 2005 Ora Fish RPI

Dedicated Staff Need dedicated personnel to carry out the following functions n n n n n 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 o Re-enforce common definitions o Set up processes to identify and clean erroneous data o Set up processes to gather relevant data o 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 o o o 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: o A Project o A Product It is a service Copyright 2005 Ora Fish RPI

Implementing Data Warehouse o o 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 o o o 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 n Architecture addressing transformations, meta data, security, delivery n Campus rollout and training n Information Quality n Communication n Support Copyright 2005 Ora Fish RPI

Implementation Methodology Campus Communication Nex t Da ta M ar Build DW Foundation Develop Subject Oriented Data Marts t 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 • operational systems • transactional systems DATA ACQUISITION • extraction • transformation • modeling • loading DATA WAREHOUSE DATA DELIVERY • user-facing repository applications • subject-based data marts • Conformed dimensions • metadata • central Application Servers Source Database • business intelligence • decisionsupport • OLAP • querying • reporting 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 o o o ERP – Banner from SCT ETL – Power Center from Informatica Data Base – Oracle 9 i 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 o o o 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 o o o o Determining Constituency Forming Implementation Group Conducting interviews Defining Scope and Timelines Modeling Extracting, Transforming, and Loading Data Develop Security system Testing Copyright 2005 Ora Fish RPI o Identify information gaps n n o o 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

Catalyst of Change o o o o 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 o o o 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 o o o 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 o Training is mandatory at all levels Copyright 2005 Ora Fish RPI

Communication and Buy-into o Executive briefings: n n n o Campus orientations n n n o 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 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 o o Utilizing Data Mart for internal operations More changes to the Data Mart are expected Information Quality o o Establishing data cleanups queries and procedures Impacting Culture Preparing for Campus release: n Developing campus training program: Developing and publishing Dash Boards, and Brio dynamic documents n Developing operational training Copyright 2005 Ora Fish RPI

Initial Tiered Access – Who will have access to what Lo w Cabinet; Deans; Department Chairs; Center Directors Hi Core Administration Portfolio/Division level gh Tr ain in g Department level Dash Board Copyright 2005 Ora Fish RPI Analysis performed on the pre-published dynamic documents 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 o Brio 101 n o Brio 201 n o Data mart basics, BQYs, and star schemas Operational Training n Focuses on practical applications , delivered by business owners Copyright 2005 Ora Fish RPI Study Halls n o o Informal, open agenda Best Practices n Advanced analytics and reports Data Training n o Basic navigation and mechanics o Demonstration of best practices, delivered by business owners One-on-Ones n 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 Level 1: Portfolio/Dept -Specific Pre-Built Docs Medium Track 3 Low Dashboard & Portal training One-on-one or small group format Copyright 2005 Ora Fish RPI Ongoing Follow-up

Training Philosophy o The goal of the training program goes beyond teaching the mechanics: n n n 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 o o Establishing processes to capture erroneous and inconsistent data n ETL process to identify errors Data n Rejecting data n Load data and clearly label errors Data Audit processes n 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 o o o 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 o o o o 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 o Front-End (Hyperion Performance Suite) Administration o ETL (Power Center) Administration o Desktop Support and Administration o Data Base Administration o Dash Board maintenance o Brio documents development, support, and administration o 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 o o 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: o Forecast current year sponsor research expenditures. o Forecast graduate financial aid commitments o 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 o More accurately forecast research awards o Utilizing historical research ‘success rates’ in projecting cost sharing commitments Monitoring and compliance: o Daily monitoring of budgets and expenditures from higher levels down to the specifics o Monitor and review project to date budgets o Monitoring positions budgets vs. actuals and in conjunction with estimated future earnings are accurately projecting balances o Monitoring the allocation of graduate financial aid Operations o Financial information is used in preparing and analyzing the financial statements, reconciling between the sub-ledger and general ledger, reviewing payroll allocations o Credit card reconciliation Copyright 2005 Ora Fish RPI

Cultural Changes o o o o 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 o Number of users trained and their role in organization o Number of distinct users connected monthly o Number of monthly connection o Requests for changes and enhancements o Satisfaction surveys Value o Shifting IT resources from reporting to other value added activities o Productivity savings on the business side o Savings realized by better more informed access to information Copyright 2005 Ora Fish RPI

The Dreaded Return on Investment Calculating ROI o Savings in personnel and processing o More Effective Financial Aid packaging o Effective recruitment strategies o Identification of retention issues to target o More fiscal responsiveness Copyright 2005 Ora Fish RPI

Benefits Fosters data integrity and conformity o One version of the truth o Helps to identify erroneous and inconsistent data o 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 o Cultural shifts towards developing Goals, Objectives, measuring outcomes o KPI, Scorecards, Metrics Copyright 2005 Ora Fish RPI

Administrative Academic 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 o The data warehouse toolkit (Ralph Kimball) o The data warehouse lifecycle toolkit (Ralph Kimball) o Data warehouse design solutions (Christopher Adamson & Michael Venerable) Copyright 2005 Ora Fish RPI

Informational Resources o o o Become a member of the data warehouse institute Visit http: //www. datawarehousing. com maintained by Data. Mirror Subscribe to listserv from EDUCAUSE http: //www. educause. edu/memdir/cg/cg. Html o Visit other schools web sites via http: //www. Georgetown. edu/users/al lanr/dwconfig/ Copyright 2005 Ora Fish RPI

Questions ? ? ? Ora Fish fisho 2@rpi. edu ? Copyright 2005 Ora Fish RPI