Building Data Warehouse at Rensselaer

1. Building Data Warehouse at Rensselaer Ora Fish Rensselaer Polytechnic Institute Best Practices in Higher Education Student Data Warehousing Forum Northwestern University October 20-21, 2003

2. Agenda • Background • Development Methodology • Rollout Strategy • Summary Status – where are we now • Benefits • Lessons Learned • Demonstrations of the Financial Analysis Data Mart – Executive Information Systems Q & A

3. Facts about Rensselaer (RPI) • “We are the first degree granting technological university in the English-speaking world” • Research University • Total Students 9,145 • Graduates – 4,006 • Undergraduates – 5,139 • Faculty - 450 Founded in 1824 by Stephen Van Rensselaer

4. Facts about Presenter • Bachelor in Math and Computer Science from Tel-Aviv University, Israel • MBA from RPI • 23 years in system implementation and software development in variety of technical and management positions • Over 8 years in RPI • Involved with the Data Warehouse for the past five – six years

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

6. First attempt to fund DW project Fails Reasons for failing funding in Fall of 1997 : • Timing • Expectation • Lucking business sponsor • Analytical culture • What does it really means??

7. Second Attempt to Fund DW Project is Successful Spring 2000 - The following changes had occurred: • Timing – Banner does not addresses reporting; Views are too slow to be used • Organizational changes (New President, CIO) • Performance Planning • We have build a Prototype

8. Buy-in Process • Demonstrate to those who need this information desperately • The word is out • From the CIO to the committees to the cabinet

9. Buy-in Process We are prepared to address: • Budgets • Timelines We are ready with the white paper to communicate the key components (iterative development under overall planning, business users involvement, meta data, approaches)

10. The Fundamental Goal The fundamental goal of the Rensselaer Data Warehouse Project is to integrate administrative data into a consistent information resource that supports planning, forecasting, and decision-making processes at Rensselaer.

11. Development methodology • Phase I – Building Foundation • Phase II – Iterative Process of Building Subject Oriented Data Marts • On going Operations: Support and Training; Maintenance and Growth

12. Rolling Implementation

13. Phase I – Building Foundation • Organizational Structure • Project framework and high level plan • Building Technical Infrastructure • Develop Data Policies and Procedures • Hiring

14. Project Organizational Structure

15. Project Framework • High Level Analysis • Prioritization process • Hire and train staff, Choose consultant • Establish communication channels (web site, newsletters, kickoff event…)

16. High Level Analysis and Prioritization process

17. Prioritization Process

18. Building Technical Architecture Brio ODS/JF Node Informatica PowerCenter ETL Server Brio Portal Dash Boards content Viewers Brio WebClient DATA SOURCES DATA ACQUISITION DATA WAREHOUSE DATA CONSUMPTION DATA DELIVERY transactional systems • business intelligence • decision-support • OLAP • querying • reporting • central repository • subject-based data marts • metadata • conformed dimensions • user-facing applications • report generation • subject-based data cubes • data mining • extraction • transformation • modeling • loading banner reporting instance (clone) AIX Banner Reporting Instance Oracle 9i end-user machines production data warehouse machine AIX 4-CPU 4GB RAM Brio Portal AIX DWDB (targets) Oracle 8i [DWDB] Microsoft Excel analysts Informatica Repository metadata banner production AIX Brio Insight analysts Banner Oracle 9i [PROD] Brio Shared Metadata webserver AIX

19. Technical Architecture Inventory • ERP – Banner from SCT • ETL – Power Center from Informatica • Data Base – Oracle 8i • Models – Star schemas with conformed dimensions • Web Front end tools – Brio, Dash Boards • Desktop Front End tools – Brio, Excel

20. 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 • This policy considers security and privacy paramount • 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

21. Phase IIComponents of Building Subject Oriented Data Marts • Defining Scope and Timelines • Modeling • Development • Record Metadata • Local Testing • Core Administration Testing • Design and Develop Security • Core Administration live in Production • Front-End development for the campus • Campus Rollout JAD & RAD Approach

22. Defining Scope • Identify Constituency • Detailed Requirements Definitions • Analyze Data Sources / raise issues • Define Scope • Acceptance/Project Review • Develop and approve specific security policy

23. Modeling • Subject-based data marts • Star Schemas • Conformed dimension

24. Graduate Financial Aid Data ModelOne row per student per term per support type

25. Student Enrollment Model – one row per enrolled student per term

26. Summary GFA Modelone row per graduate student per term

27. Development - ETL • Data Staging Design and Development • Design & Develop Aggregation Process • Develop Data Quality Assurance Processes

28. User testing testing and testing ….. Note: the Data Warehouse serves the needs for ad-hoc analysis and reporting of various groups of users • Testers are: Deans, Cabinet, Financial Managers, Core Administration offices… • Testing period is an opportunity to create more definitions, groupings, and transformations…

29. Prior To User Acceptance Testing • Identify Testing Candidates - key users identified in the scope • Train Users in Brio • Transfer of Knowledge from Developer to Testing Group • Sample Reports • Document Data Mart Description • Document Standard Naming Conventions • Document Common Uses for Each Star Schema • User Set-Up

30. Testing sessions • Allocating time slots • Targeting – aiming to produce results • Verifying that the models do address the need • Great opportunity to bridge diverse groups

31. Defect/Enhancement Log • Date Reported • Priority Level (i.e. High, Medium, Low) • Defect and/or Enhancement Description • User Reporting Defect and/or Enhancement • Defect/Enhancement Status • Incoming • Pending • Work In Progress • User Acceptance Testing • Closed • Focus Group • Assigned To • Resolution • User Assigned To Test Resolution

32. Recording Metadata • User driven effort • Stored in Informatica repository • Accessed via Brio

33. Development - Securing Data Marts • Ensure that the subject oriented Data Policy is defined • Technically feasible • Approved • Build Security Front End application

34. Data Security options • Securing schemas • Securing facts only • Securing dimensions only • Securing both facts and dimensions

35. Nuts and Bolts of the Data Base Security • Data Base security applies to all individuals given either direct access to the warehoused data or given permissions to process Brio dynamic reports • Organization Managers And Financial Managers will have access to the warehoused financial data based on the following criteria • All financials posted against that Org • All funds listing that Org as a home Org (in cases of research funds, this defines where the research is brought into) • All funds listing the PIs (or the Financial Manager) associated with that Org as fund financial managers. (Resolves the Multi-disciplinary issue) • All funds and orgs listing that Org as a predecessor in either one of the above three cases. • Administrative role: Individuals might be granted access to additional funds and org based on their needs and their role within Rensselaer.

36. Position Control and Labor Data Policy Overview • Already have access to Labor data in Banner • Completed DW training • Access to Budgets and Labor data for all Funding, Employees, or Positions owned by their Organization as following: • Funding: All actual and budgeted labor expenses posted against their Organization • Employees: All actual and budgeted labor expenses associated with the Employees reporting to their Organization within the timeframes of the employees’ employment in the Organization. • Positions: All actual and budgeted labor expenses associated with Positions owned by their Organizations. • All of the above within their Organizations’ hierarchy.

37. HR Security Policy Overview Access to Employee Information

38. Enrollment and Graduate Financial Aid Data Policy Overview • Access to aggregate data is based on “need to know” • Access to student identifiable information is restricted as following: • Sponsoring graduate students • Major • Advisement • Central administration/management of the University

39. Access to Undergraduate Financial Aid Restricted to very few positions within: • President Office • Institutional Research • Students Records and Financial Services • Financial Aid Office

40. Development – Front End • Dash Board Design and Development – Joint effort with Core Administration • Training testing groups in Brio • Develop first version of Brio dynamic documents and publish via Portal – Joint effort with Core Administration

41. Campus Rollout • Defining roles and responsibilities • Who will have initial access to what • Develop Roll out strategy • Setting expectations • Designing and carrying out Training Programs

42. Communicate • Executive briefings • During Training • Campus orientations • Wed site • Any possible vehicle ….

43. Initial Tiered Access – Who will have access to what Cabinet; Deans; Department Chairs; Center Directors Low Data Policies Training Department Financial Managers Finance Administration Portfolio Financial Managers High

44. Position Control Data Mart Graduate Financial Aid Finance Data Mart Student Enrollment Connects to the DW without Portal Web Other Data Marts Brio Products Overview (Brio Intelligence) Brio Desktop User (i.e., Brio Explorer, or Designer) Data Warehouse Brio Portal Portal and Insight are also available to Desktop users via the Web Brio Insight User Brio Insight Folders, Published Documents, Personalized Content, Dashboards, ERD Connects to the DW with Insight and Portal via the Web Each user will have separate Portal and database usernames and passwords. The Portal login provides the user with access to published content based on a security profile. The database login is necessary to extract data from the Data Warehouse.

45. Brio Portal Allows users to access published documents (e.g., BQYs, Brio manuals, training documents) and personalize their content

46. Executive Dashboard Overview • Accessed via the Portal • High-level, graphical views of Portfolio-specific data • Designed primarily for executive use, though available to other users as well • Comprised of monthly summary data, refreshed nightly Dashboard Help: http://www.rpi.edu/datawarehouse/dw-help-dashboards.html

47. Campus Rollout Assumptions • Training is mandatory at all levels. • Several levels of training will be offered to campus in Brio tools, Data, and Data Policies. • Joint effort between DW Group and Core Administration • Portfolio Financial Managers responsibilities: • Rollout within Portfolio • Training within Portfolio

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

49. Level 1: Data Mart Basics Level 2: Advanced Brio Documents Brio 101 Level 1: Portfolio/Dept-Specific Pre-Built Docs Brio 101 Dashboard & Portal training One-on-one or small group format Training Methodology Training Required Track 1 Primarily Portfolio Financial Managers who will build ad-hoc queries and reports (i.e., Brio documents) from data mart star schemas and meta topics. High Track 2 Department Financial Managers who will work primarily with pre-built Brio documents. Medium Track 3 Designed for Executive users, this track focuses on Dashboards and the Brio Portal. Low

50. Setting and Communicating Expectations Communicate to Institute Executives • Creating an Information Revolution • Changing culture • Top down approach is needed • Recognize Barriers • Ask for commitment