Data Warehousing: Changing Campus Culture

1. Data Warehousing: Changing Campus Culture Ora Fish, Data Warehouse Program Manager Rensselaer Polytechnic Institute

2. Rensselaer Polytechnic Institute (RPI) • “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. Founded in 1824 by Stephen Van Rensselaer

3. Fundamental Problem Operational systems are not designed for information retrieval and analytical processing

4. 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)

5. 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/

6. 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 DepartmentChairpersons, 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

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

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

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

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

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

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

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

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

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

16. Architecture Information Quality Campus Culture Alignment between the IT and the Business in DW implementation Alignment Technology Business

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

18. CultureFrom Transaction Processing Environment to Decision Support Environment The goal is to build analytical culture that values and promotes usage of information in decision making

19. CultureFrom 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

20. Our Approach The approach to addressing campus informational needs can not be: • A Project • A Product It is a service

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

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

23. Implementation Methodology Campus Communication Next Data Mart Release Data Mart To the Core Administration Data stewards Build DW Foundation Develop Subject Oriented Data Marts Training Release Data Mart to the Campus Continuing Adaptation and Growth…… Maintenance and Support

24. Technical Architecture DATA CONSUMPTION DATA DELIVERY DATA WAREHOUSE DATA ACQUISITION DATA SOURCES • user-facing applications • business intelligence • decision-support • OLAP • querying • reporting • extraction • transformation • modeling • loading • operational systems • transactional systems • central repository • subject-based data marts • Conformed dimensions • metadata E T L Application Servers Data Warehouse Source Database Web Client Interfaces Data Mart Source Database Decision Support Servers Metadata Desktop Interfaces Other Sources (e.g. files, spreadsheets) Operational Data Store Data Cube

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

26. Building DW Foundation – 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

27. 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 Building Subject Oriented Data Marts Alignment between the Technology and Information Quality

28. 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: (month-end reporting)

29. Rollout

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

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

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

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

34. Data Mart Release to the Core Administration • 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

35. Initial Tiered Access – Who will have access to what Cabinet; Deans; Department Chairs; Center Directors Low Data Policies Training Department level Core Administration Portfolio/Division level High

36. Demo

37. 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 Common Usage

38. 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 Mix

39. Level 1: Data Mart Basics Level 2: Advanced Brio Documents Brio 101 Operational Training Level 1: Portfolio/Dept-Specific Pre-Built Docs Brio 101 Dashboard & Portal training One-on-one or small group format Ongoing Follow-up Training Program Overview Track 1 High Track 2 Medium Track 3 Low

40. Training Philosophy • The goal of the training program goes beyond teaching the mechanics: • Need to sell the Brio tool and the project • Need to educateon 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!

41. Data Addressing Information Quality • Establishing processes to capture erroneous and inconsistent data • ETL process to identify errors • Rejecting data • Load data and clearly label errors • Data Audit processes • Ensuring that the loaded data reconciles back to the operational systems

42. Data Addressing Information Quality Establishing Data Stewards roles and responsibilities • 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

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

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

45. Catalyst of Change Processes and Culture

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

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

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

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

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