The University of Nebraska Data Warehouse

1. The University of Nebraska Data Warehouse University of Nebraska Computing Services Network (UNCSN) Walter Weir, University of Nebraska CIO Randy Goldenstein, UNCSN Data Administrator UNCSN Information Access Team

2. Data Warehouse Overview • Warehouse Terminology • Warehouse Benefits & Challenges • Current State of the University Data Warehouse • Future Plans • Campus Partnerships Needed for Success • Discussion/Questions

3. Data Warehouse Terminology • Data Warehouse • A copy of transaction data specifically structured for querying and reporting • Data Mart • A logical subset of the complete data warehouse • OLAP (On-Line Analytic Processing) • The activity of querying and presenting text and number data, usually with underlying multidimensional ‘cubes’ of data • Dimensional Modeling • A specific discipline for modeling data that is an alternative to entity-relationship (E/R) modeling; usually employed in data warehouses and OLAP systems.

4. Data Warehouse Goals • Speed up reporting • Reduce reporting load on transactional systems • Make institutional data more user-friendly and accessible • Integrate data from different source systems • Enable ‘point-in-time’ analysis and trending over time • To help identify and resolve data integrity issues, either in the warehouse itself or in the source systems that collect the data

5. Warehouse Challenges • High-level support • Identification of reporting needs by subject area and organizational role • Bridging the gap between reporting needs and technical specifications • Partnerships with central and campus administrative areas • Customer support and training

6. Current Warehouse Environment • Physical Architecture • Subject Areas of Data • Time Dimensions • Customer Demographics • Report Delivery Mechanisms

7. Physical Architecture • Production, Fail-over, and Test Servers • All Servers are 4-way 700mHz processors with 2 GB RAM • Shared SAN contains approximately 2 Terabytes of storage • Current Production Database size is approximately 450 GB

8. Subject Areas and Sources of Data • Financial Transactions – Legacy & SAP • Human Resource – Legacy & SAP • Payroll – Legacy & SAP • Budget Data – University PSL (Personnel Services Ledger) system • Student Data • Institutional Research Feeds from UNK, UNL • Current Data Feeds from UNO

9. Time Dimensions • Financial Transactions – Fiscal Year 1993/94 to Present • Human Resource – March 1995 to Present • Payroll – Fiscal Year 1995/96 to Present • Budget – Fiscal Year 1989/90 to Present • Student Data • UNK IR – Academic Year 1997/98 to Present • UNL IR – Academic Year 1996/97 to Present • UNO Current – Academic Year 1986/87 to Present

10. Customer Demographics • 1,646 Users As Of 3/31/2003 • By Campus: • UNL – 878, IANR – 256 • UNMC – 104 • UNO – 120 • UNK – 182 • UNCA – 106 • By Organizational-Level Access: • Campus – 252 • VC/VP – 70 • College – 316 • Department & Below – 1008

11. Customer Demographics • By Subject-Area Access: • Financial/Budget/Payroll - 1578 • Human Resource – 1564 • Student – 1272 • By Report Delivery: • Web Only - 714 • Ad-hoc Access - 932

12. Report Delivery Mechanisms • Web Sites • Ad-hoc Reporting Access • Scheduled and On-Demand Report Generation

13. Data Warehouse Web Sites • Customer Support Web Site • Main Reporting Web Site • General Operating Budget Web Sites • Web Sites Using Warehouse Extracts • University Tuition Remission Web Site • UNL Job Listing Web Site • UNK Phone Directory Web Site

14. Customer Support Web Site • Address: http://nulookhelp.nebraska.edu • Contents: • News • Online Help & FAQ • Downloads • Data Dictionary • Training Registration

16. Main Warehouse Web Site • Address: http://mynulook.nebraska.edu • Contents: • Dynamic pre-defined reports • Main developed reporting sections • Personal Reports (‘my stuff’ section) • Employment information • Course and student reports • Sponsored activity reports • University → Ledgers • University → Employee • University → Student Info

21. General Operating Budget Web Sites • Address: http://www.nebraska.edu/about/budget_operating.shtml • Contents: • Public Access to Official Published General Operating Budget • Last 4 Fiscal Years listed on site • Also have a private site during budget development cycle

24. Web Sites Using Warehouse Data • University Tuition Remission Web Site http://trp.unl.edu • UNL Academic Affairs Job Listing Web Site http://svcaa.nebraska.edu • UNK Phone Directory Web Site http://www.unk.edu/directory

25. Ad-hoc Data Warehouse Access • End-user views are available for all subject areas • Access requires more technical knowledge • Ad-hoc access given out by security administrators on the campuses • UNCSN actively supports MS Excel and MS Access, but customers can use any reporting tool capable of connecting to an OLE DB or ODBC data source

26. Scheduled and On-Demand Report Generation • General Operating Budget Reports (on-demand during budget development) • Printed GOB ‘Budget Books’ • Annual Salary Increase Notification Letters • Phone Directory Information (UNO and UNK) • Monthly Benefit Enrollment Reports • Mandatory Retirement Enrollees • Voluntary Retirement Enrollees

27. Current Projects • Academic Management Information System (AMIS) for UNL Academic Affairs • Financial Reporting Enhancements • Payroll Reporting Enhancements • Data Dictionary Development • Subject-area Specific Training Curriculum

28. Future Enhancements and Directions • U-Wide Institutional Research Reporting • Dimensional Modeling • OLAP Tools • Customized/Specialized Reporting Systems • Campus Data Marts

29. U-Wide Institutional Research Reporting • Oversight by Central Administration • Automatic Generation of IPEDS Reports • Transmission of NEEDS data to NCCPE • Census-date Student Data needed for all campuses • Still need UNO and UNMC data • Would free up campus IR staff for more in-depth analysis and reporting

30. Dimensional Modeling • Dimensional Modeling is a departure from traditional ‘Entity-Relationship’ database design • Helps facilitate top-down analysis and data mining • Needed by many OLAP reporting tools • Requires help to define • High-level Reporting Needs • Functional Definitions and Validation

31. OLAP Analysis & Tools • Usually requires dimensional models to be defined and in place • Many vendor tools available for this purpose • SAP Business Warehouse module uses dimensional models and OLAP tools

32. Specialized Reporting Systems • A data warehouse is NOT a Decision Support System (DSS) • A data warehouse CAN be utilized as the data repository to support DSS solutions • Challenge here is high-level support and clearly defined goals • Examples • UNL’s Academic Management Information System (AMIS) • Central Institutional Research Reporting System

33. Campus Data Marts • Logical next step in warehouse architecture • Will help balance workload on hardware and software • Provides opportunity for more campus-specific development and reporting • Will require close partnerships for planning, training, implementation, and on-going support

34. Campus Partnerships Necessary for Success • Central and Campus Administrative Areas • Institutional Research • Accounting • Budget • Human Resources/Payroll • Student Registration & Records • Sponsored Programs/Grants • Facilities Management • Administrative Systems Group • Campus Computing Areas

35. Recommended Resources • Books Ralph Kimball, The Data Warehouse Toolkit Ralph Kimball, The Data Warehouse Lifecycle Toolkit Inmon, W.H., Building the Data Warehouse • Web Sites • Data Warehousing Information Center, http://www.dwinfocenter.org • The Data Warehousing Community Site, http://www.datawarehouse.com

36. Discussion/Questions Contacts: UNCSN Helpdesk – (402) 472-7373 UNCSN Helpdesk Email – helpdesk@nebraska.edu Warehouse Team – nulook@nebraska.edu