The University of Nebraska Data Warehouse University of

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

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

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

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

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

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

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

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 n n Institutional Research Feeds from UNK, UNL Current Data Feeds from UNO

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 n n n UNK IR – Academic Year 1997/98 to Present UNL IR – Academic Year 1996/97 to Present UNO Current – Academic Year 1986/87 to Present

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

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

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

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

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

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

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

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

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

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

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

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

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 n Still need UNO and UNMC data Would free up campus IR staff for more in-depth analysis and reporting

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 n n High-level Reporting Needs Functional Definitions and Validation

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

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 n n UNL’s Academic Management Information System (AMIS) Central Institutional Research Reporting System

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

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

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 n n Data Warehousing Information Center, http: //www. dwinfocenter. org The Data Warehousing Community Site, http: //www. datawarehouse. com

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