Successful Data Warehouse Implementation Brings Changes to Campus Culture, Processes, and People

1. Successful Data Warehouse Implementation Brings Changes to Campus Culture, Processes, and People Ora Fish Rensselaer Polytechnic Institute

2. Agenda • Alignment between the Information Technology and the Business • Development Methodology • Lessons Learned • Demonstration • Q & A

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

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

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

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

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

8. Information Quality Campus Culture Implementing Data Warehouse Alignment Technology Business

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

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

11. Before, During, and After the implementation How does the IT leads and effectively aligns Technology, Information Quality, and Campus Culturebefore, during, and after the Data Warehouse implementation

12. 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 Adaptation and Growth Maintenance and Support

13. DW Program Timeline

14. Building DW Foundation • Organizational Structure • Project framework and high level plan • Building Technical Infrastructure • Develop Data Policies and Procedures

15. Project Organizational Structure

16. High Level Analysis and Prioritization process

17. Prioritization Process

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

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

20. 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, Information Quality, and Campus Culture

21. Modeling: Kimball’s Bus Architecture to Subject oriented Data Marts and the Conformed DimensionsStudent Enrollment Model – one row per enrolled student per term

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

23. 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, andConsistent Across The Board • Build Security Front End application • Security Options: • Securing schemas • Securing facts only • Securing dimensions only • Securing both facts and dimensions

24. Securing Facts Only Time Dim Time Key Calendar Year Calendar month Calendar day Date Fiscal year Fiscal Period Organization Dim Org Key Org code Org Description Org Financial Manager Type Various Indicators Various attributes Financial Transaction Fact Org Key Fund Key Acct Key Time Key ------------------------ $ Account Dim Acct Key Acct code Acct Description Acct Type Various Indicators Various attributes Fund Dim Fund Key Fund code Fund Description Fund Financial Manager Type Various Indicators Various attributes

25. Securing Only Identifiable Information Time Dim Time Key Calendar Year Calendar month Calendar day Date Academic Term Academic Year Snapshot Type Student Advisor Bridge Advisor Key Name Weight Factor Type Rank Discipline attributes Title School Department Various attributes Class Dim Class Key Class code Class Descr Student Enrollment Fact ------------------------ Count Matriculated Count Hours Registered Hours Attempted Credit Hours Earned Term GPA Overall GPA Tuition Paid Etc…. Student Dim Std Key Name Demographics Geographic Info Minority Ind Citizenship reporting Ind Various Indicators Various attributes Academic Program Dim Academic Prg Key Type Classification Major/Minor/Concentration Type Major/Minor/Concentration Desc. Campus School Department Official Headcount Indicator Weight Factor Various attributes

26. Managing Testing Sessions Alignment between the Technology, Information Quality, and Campus Culture • 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

27. Campus Communications During Testing Period Meeting one-on-one with Campus executives (Cabinet, Deans, etc.) • Getting feedback early on • Engaging • Marketing

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

29. Campus Rollout Alignment between the Technology, Information Quality, and Campus Culture • Developing Roll-Out strategy • Defining roles and responsibilities • Defining initial access level • Recognize barriers and Setting expectations • Designing Training Programs • Communicate to the Campus

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

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

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

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

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

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

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

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

38. Adaptation and Growth

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

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

41. Adaptation and GrowthChanges 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

42. 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 • Redirectingcostly personnel hours • Enhancing institutional effectiveness

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

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

45. Demo

46. Questions ??? Ora Fish fisho2@rpi.edu ?