Building Data Warehouse at Rensselaer

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

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

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?? Now that we know what the solution should be, our next step is to obtain funding. In Fall of 97 we are making our first attempt to come up with funding through Strategic Initiatives committee proposing to build a DW architecture that will address the reporting needs. We are not funded. 1) Timing – we are in the middle of Banner Student implementation project, no one has time or the energy to start yet another project…. 2) Expectation - Banner will address all the needs 3) Lucking business sponsor, people are over stretched, no one from the business community takes the initiative or pushes for solution. 4) Analytical Culture – we are mostly processing data and the decisions are made by the guts feeling as oppose to quantitative analysis. We don’t have an office of IR 5) And the most important factor: What does it really means??Now that we know what the solution should be, our next step is to obtain funding. In Fall of 97 we are making our first attempt to come up with funding through Strategic Initiatives committee proposing to build a DW architecture that will address the reporting needs. We are not funded. 1) Timing – we are in the middle of Banner Student implementation project, no one has time or the energy to start yet another project…. 2) Expectation - Banner will address all the needs 3) Lucking business sponsor, people are over stretched, no one from the business community takes the initiative or pushes for solution. 4) Analytical Culture – we are mostly processing data and the decisions are made by the guts feeling as oppose to quantitative analysis. We don’t have an office of IR 5) And the most important factor: 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 We started our second attempt in Spring 2000 The following changes had occurred since then: Timing – Organizational changes had occurred – The general political climate is quite different, we all embark on performance planning.We started our second attempt in Spring 2000 The following changes had occurred since then: Timing – Organizational changes had occurred – The general political climate is quite different, we all embark on performance planning.

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 Once we had a prototype in place…. The buy into process had began: From the CIO to School Deans, and Chairs, to the Cabinet… numerous committees on the campus (I am sure you don’t have any committees on your campus…) the bottom line the word was out. People were asking us to come and do the presentation for so and so…Once we had a prototype in place…. The buy into process had began: From the CIO to School Deans, and Chairs, to the Cabinet… numerous committees on the campus (I am sure you don’t have any committees on your campus…) the bottom line the word was out. People were asking us to come and do the presentation for so and so…

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) And with the EVERYONE LOVES IT comes the basic questions: how much will it cost, how long will it take. BE Prepared to answer Budgets (allocating for hardware, software, people, training, consulting) Timelines (project timeline vs. ongoing support) Communicating …. Key components (iterative development under overall planning, business users involvement, meta data, approaches) The bottom line: You have to be prepared to be at least couple of steps ahead…. ALWAYSAnd with the EVERYONE LOVES IT comes the basic questions: how much will it cost, how long will it take. BE Prepared to answer Budgets (allocating for hardware, software, people, training, consulting) Timelines (project timeline vs. ongoing support) Communicating …. Key components (iterative development under overall planning, business users involvement, meta data, approaches) The bottom line: You have to be prepared to be at least couple of steps ahead…. ALWAYS

10. Rensselaer’s Solution - Data Warehouse The data warehouse is a collection of data that is pulled together primarily from operational business systems and is structured and tuned for easy access and use by information consumers and analysts, especially for the purpose of decision making.

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

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

13. Rolling Implementation

14. Phase I – Building Foundation Organizational Structure Project framework and high level plan Building Technical Infrastructure Develop Data Policies and Procedures Hiring Project organizational structure: Sponsorship committee – Steering committee – Implementation groups. PROJECT ORGANIZATION: Includes all the activities necessary to get the project off the ground, such as: Defining user groups and committees. Hiring and training staff. Developing tools selection criterion. Hosting data warehousing product vendor visits. Developing communication resources. Developing metadata standards. Creating issue resolution process. Establishing enhancement-tracking process.   DEVELOP DETAILED PROJECT SCOPE AND PLAN: Based on the prioritized list of requirements, develop detailed project scope and plan.   BUILD TECHNICAL INFRASTRUCTURE: This task will include procuring and installing base system hardware and software needed to implement the data warehouse architecture. It includes: Developing technical architecture plan. Initially re-allocating of existing data base server and Oracle 8i installation. Selecting and installing front-end tool. Purchasing and installing Web and application servers. Selecting and installing extraction transformation and loading tool. Project organizational structure: Sponsorship committee – Steering committee – Implementation groups. PROJECT ORGANIZATION: Includes all the activities necessary to get the project off the ground, such as: Defining user groups and committees. Hiring and training staff. Developing tools selection criterion. Hosting data warehousing product vendor visits. Developing communication resources. Developing metadata standards. Creating issue resolution process. Establishing enhancement-tracking process.   DEVELOP DETAILED PROJECT SCOPE AND PLAN: Based on the prioritized list of requirements, develop detailed project scope and plan.   BUILD TECHNICAL INFRASTRUCTURE: This task will include procuring and installing base system hardware and software needed to implement the data warehouse architecture. It includes: Developing technical architecture plan. Initially re-allocating of existing data base server and Oracle 8i installation. Selecting and installing front-end tool. Purchasing and installing Web and application servers. Selecting and installing extraction transformation and loading tool.

15. Project Organizational Structure

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

17. High Level Analysis and Prioritization process Add subject areas and the prioritizationAdd subject areas and the prioritization

18. Prioritization Process

19. Building Technical Architecture Fill out the blanks….as you progress with the project… Once the Architecture framework is decided: After selection process Informatica as ETL, Brio as Front end access;Fill out the blanks….as you progress with the project… Once the Architecture framework is decided: After selection process Informatica as ETL, Brio as Front end access;

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

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

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

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

24. Modeling Subject-based data marts Star Schemas Conformed dimension

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

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

27. Summary GFA Modelone row per graduate student per term

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

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

30. 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 Prior To User Acceptance Testing

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

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

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

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

35. Data Security options Securing schemas Securing facts only Securing dimensions only Securing both facts and dimensions

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

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

38. HR Security Policy Overview Access to Employee Information

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

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

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

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

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

44. Initial Tiered Access – Who will have access to what

45. Brio Products Overview (Brio Intelligence)

46. Brio Portal

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

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

49. Data Warehouse Cascaded Rollout Strategy

50. Training Methodology

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

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

53. How to get there …. Common Vision: One version of the truth Data Experts across campus and across organizational boundaries Data Experts: Portfolio Financial Managers or Equivalents will be expected to: access data create reports perform analysis enable/train Portfolio end-users

54. Training approach Evaluating skill levels: Surveys before training Measuring satisfaction with training program: Overall satisfaction with program content is very high: 91% gave the highest survey rating. Partnering with HR – The DW training was included in the appropriate Performance Evaluations / Job Descriptions and course offerings Measuring access levels – generating log files Utilizing Web – self help, registration, communication

55. Status of the Data Warehouse Initiative

56. DW Program Timeline

57. Data Warehouse Operations Support Transitioning from Development to Operations Portal Administration Dash Board maintenance Data Marts maintenance Users support Data Base Administration Brio documents development, support, and administration Informatica Administration

58. On – Going Training Functional Training Brio training Refreshers courses: Finance/Research, Labor, HR, Enrollment, GFA, etc. Advance curses

59. DW User Support levels Signing up for sessions Creating user profiles/security Installs Publishing requests General problems/questions 5-10 Emails Daily 2-4 Calls Daily 1-2 Major problems that need extensive work from developers/front-end technical support on a daily basis.

60. Data Policy Administration Each Data Policy is administered by the appropriate Committee appointed at the VP level Requests outside the policy are submitted in writing to the Data Warehouse group The Committee has the discretion to either authorize/deny access or recommend access to the appropriate VP depending on the nature of the request. The respective portfolio owner are notified of access granted.

61. Maintenance

62. RPI Resources

63. Benefits Gained Empowers decision-makers Redirects costly personnel hours Enhances institutional effectiveness Improves integrity and conformity of campus-wide information Promotes the “no walls” culture. Improves data quality over time.

64. Kirsten M. Volpi, Assistant VP/Controller “...There has been analysis that we have not been able to get at before because the data was not retrievable in a fashion conducive to perform analytics.  For instance, we have begun utilizing the warehouse to analyze the indirect cost yield on our research grants.  This data was not readily available before.” “We are also using the warehouse not only for analytics but for reports to assist with monitoring compliance with internal policies, assisting with data gathering for external surveys, as well as assisting with automating certain processes (encumbrances for graduate financial aid).”  

65. Eileen G. McLoughlin, Director of Financial Planning & Budget “The Budget material was consolidated two weeks sooner than the previous years.  Many factors contributed to the success, however a significant contributor was the data warehouse allowing the Budget Office to provide data and analysis of the data to decision makers faster than in the past.” “…reinforces the “no walls” culture – i.e. as the warehouse becomes known as the one and only data source – this will contribute towards individuals recognizing that we are one organization with one version of the truth.“ “…Improved quality over time, integrity, conformity – as data is viewed and questioned issues have and will come to the surface on processes that impact data.  This has occurred in the budget office, accounting practices have been simplified so the resultant data is more easily interpreted”

66. Diane Veros, Director Research Accounting “The Data warehouse along with the BRIO software has proven to be an extremely useful tool for providing information for reporting, monitoring and analysis.  BRIO queries and pivot tables have definitely helped to make some of our work more efficient and effective.  We have developed queries for monitoring reports, verifying data integrity, and analysis that before would have required days, weeks, or even months working with IACS to program and develop.  Once developed, those older reports (and/or the data in them) would have allowed limited access to campus, and another user might have started from scratch to produce a similar report.  The data warehouse provides a consistent data stream that allows all campus users to view and analyze the same information in many alternative ways.”

67. Jeff Tanis, Manager of Financial Operations School of Science “The time it has taken me to gather information has been cut by at least half. I now query the warehouse--where previously I had to initiate many e-mails and phone calls to collect what I needed. Last month while doing a research expenditure analysis, it took me a matter of hours--where in the past it took days to get what I needed.” “While doing a research expenditure analysis last month I identified a substantial amount of research expenditures on other schools grants using School of Science Orgs. I could not have identified and subsequently corrected these errors without the use of the Data Warehouse.”

68. Helen Grzymala, Associate Director Budget “As we roll the Finance Data Mart out to all Portfolio Financial Managers, the Budget Office will be providing more and more reports via the Data Warehouse. Portfolios will be able to see the various reports that are prepared on an Institutional level for the data. We will be able to have ongoing, meaningful discussions about the data, rather than how to get the data and how to manipulate it.” “The Data Warehouse will result in a change in job expectations for both the Budget Office and the Portfolio Financial Managers. The forecast and budget process will evolve to a more analytical review of history and a fact-based projection of the future. Users will move from simply ‘crunching the numbers’ because they will have more time and because more data is actually available. Once the Contracts and Grants information is available, the research units will be able to track activity right from the pre-proposal stage thru the award close out. Using this data, trending and other analysis will follow, leading to more accurate forecasts and budgets.”

69. Benefits

70. Benefits

71. Program metrics

72. Financial Analysis Web Access

73. Lessons Learned Picture worth thousand words – prototype Funding (time, resources, and dollars) Business Sponsorship – find the Champion and promote them 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 DW development Dash Boards – unless it is visible it is not there Building Data Warehouse is far more than a technical endeavor it is all about changing the culture

75. Questions ???