Spanning the Reporting Abyss

1. Spanning the Reporting Abyss A Reporting Strategy using functional tables in a data warehouse UM Reporting Task Force March 13, 2003

2. About the Presenter • Art Brooks, Dir ADP UMR • 12 years in Admissions and Registrar’s Office • 24 years in administrative computing • 16 years involved with data warehousing • Participant in the development, implementation and for several years the support of current SIS • Involved at various levels with SFA, HR, Univ. Advancement and other systems • Participant in the design and a user of the data warehouse and functional tables.

3. Basic Perspectives • 1. A core system is a corporate foundation NOT an entire solution! • 2. Reporting can only be successful when there is a solid database foundation. • 3. Trying to be the solution to everyone’s needs, satisfies no one.

4. Many dimensions of reporting: • A. Vendor provided • 1. Payroll • 2. Purchasing • 3. Grade reports • B. External agency requirements (may be vendor supplied) • 1. State mandated • 2. Federal Mandated • 3. Grant applications

5. Additional dimensions • C. University-wide • 1. Budget planning • 2. Miscellaneous reporting • D. Campus unique • 1. Phone directories • 2. Lists and labels • 3. Miscellaneous • 4. Ad hoc

6. Multiple Data Sources • Need to weigh requirement for currency of data, then apply to appropriate data source. • A. Real time (small percentage and must be justified) (production system) • 1. On-line registration • 2. Account balances • 3. Leave information • B. Close of business day (majority of usage) (data warehouse) • 1. Phone directories • 2. Mailings of any kind • 3. Standard reports • C. Frozen data (data warehouse) • 1. External agency reports • 2. Longitudinal reports • 3. Statistical analysis

7. Corporate Systems • PeopleSoft • 1. Finance • 2. Human Resources • 3. Student • Other corporate systems include (but not limited to): • 1. Advance (University Advancement) • 2. fsaAtlas (Sevis reporting) (INS) • 3. Loan Management System

8. Data WarehouseA logical reporting solution • The most logical arena where corporate systems and campus unique systems can be assembled to support the business needs of the organization. • Since NO vendor provides an all-inclusive suite of software for the University’s business needs in totality, a data warehouse is the sole solution. • The data warehouse needs to be viewed as the foundation for daily business reporting. (i.e. administrative core systems need to be viewed as the source for accurate data, a data repository, and the data warehouse the distributor of that collective data.)

9. Data Warehouse‘Out-house’ over ‘In-house’(Out of core system vs In core system) • Maintained outside the core system reduces the ‘politics’ of the data contained. • Allows for the definition to be molded according to the needs of the local entity. • Provides greater flexibility in structure. • Provides for the potential of local control. • Makes it easier to incorporate local data/systems • Keeps consistency in the event of a major change in the core system.

10. Affirmative Action Bookstore Admissions Budget Cashiers Chancellor’s Office Deans offices Faculty Evaluations Food Service Grants Human Resources Institutional Research International Affairs KUMR radio Registrar’s Office Residential Life Student Affairs Student Financial Aid Student Loans University Advancement UMR Administrative Systems Staff1 director, 5 programmers to provide some level of support for:

11. Summary of UMR’s Data Warehouse Experience • 1. August 1986 – initiated the campus data warehouse effort. • 2. January, 1988 – introduced first activity on Gopher. (precursor to the Web) (electronic class rolls) • 3. Fall, 1998 – functional table concept formulated. • 4. May, 1999 – functional table concept first applied with University Advancement system. (Advance) • 5. March, 2000 – impact of PeopleSoft on reporting inventoried (applications/reports ceasing to function) • A. Approximately 3,000 reports • B. 26 applications • 6. October, 2000 – began translating PeopleSoft data into legacy format.

12. Functional Table Concept • A reporting strategy that focuses on formatting the report, NOT selecting the data. It utilizes the creation of ‘event oriented, functional tables’. • GOAL – to produce reports with ZERO table joins and ZERO ‘where’ statements. • Basic principle – SIMPLIFICATION

13. Functional Table Concept was developed to: • 1. ‘Empower the users’ • 2. Simplify the data structure • 3. Reduce report development time • 4. Reduce processing time for the server (quicker response) • 5. Improve programmer efficiency • 6. Provide another tool for reporting

15. Basic Process • User makes request to functional lead, including a draft of the desired report, if possible. • The ‘design team’ (functional lead & programmer) defines a unique table that may be a combination of several core system tables, or core tables and local tables. • That new table pre-selects data and eliminates the need for such selects as: • 1. Location (campus) • 2. Effective date • Report is written by user/functional lead/programmer

16. Usage with PeopleSoft • 1. Determined the UMR or UM data warehouse, by definition could be viewed as a set of functional tables. • 2. Basic premise – if the legacy data could be converted to PeopleSoft, then the PeopleSoft data could be translated to a functional table. • 3. Have NEVER stated 100% of the PeopleSoft data could be translated. • 4. Perspective – if SOME of the applications or reports can be retained, then more time is available for staff to work on new requirements.

17. Misunderstanding • ‘All you are trying to do is keep the legacy system running to avoid a new system!’ • NO! • Programming resources are scarce. • Offices MUST keep running in the transition to a new system. • NO vendor can supply more than a base set of reports with their system. • We are trying to keep our reports running until replacement reports can be prepared. • Without their daily reports, offices will either be seriously impaired or cease to function. • The technique is used with other applications on a daily basis.

19. Introduction of Hybrid Tables • After further experience and discussion it was realized the functional tables could be hybridized to satisfy specific reporting needs and to provide a transitional bridge to the future. • Definition – a hybrid functional table is one that has data derived from disparate systems. (normally legacy and PeopleSoft) • Hybrid tables can become transitional tables. • With time, hybrid tables can become normal functional tables. (When the legacy data is no longer required, the columns cease to be filled.)

21. In Production -- NEW • 1. Admissions reports (on the Web or on demand) • 2. Admissions edits (compares PeopleSoft data with legacy to identify discrepancies between systems) • 3. Faculty grant reports on the Web • 4. Institutional Research analytical tables • 5. Vacation/Sick Leave reporting • Other uses (non-PeopleSoft): • 1. Advance system (hundreds of reports) • 2. Miscellaneous reports

22. In Production -- Legacy • 1 ChemTrack (campus chemical tracking system that requires faculty data from HR) • 2 CIS account creation and maintenance (HR data) • 3. Graduate Teaching report (legacy data from SIS and Cashiers with HR data from PeopleSoft) • 4. HR frozen tables. • 5. Miscellaneous lists and labels from HR (required minor tweaking for length of line) • 6. PRO (new student pre-registration) (in production using translated PS data since Jan, 2001)(starting THIRD year) • 7. UIDS – (HR data. NOT in production, but ready for testing)

23. In Production -- Hybrid • 1. Affirmative Action (for 2003 report, data came from PS 2002 HR and legacy 2001 HR) • Chancellor’s summer mailing to newly admitted and currently enrolled students (address data from PS for admits and SIS for currently enrolled.) • 3. Chancellor’s Christmas cards (HR and Retiree from PS and University Advancement from Advance system) • 4. Exam data (PS and SIS) • 5. SEVIS reporting (Admissions data from PS and enrolled data from SIS) • 6. Web phone directory (HR from PeopleSoft and student from SIS)

24. PeopleSoft Modules Involved • 1. Admissions – in production • 2. HR – in production • 3. SA – still testing and developing. Have been able to get results from over 150 legacy reports.

25. Disadvantages to Functional Tables • 1. A nightly refresh window MUST be considered and tactical decisions made. • 2. Documentation may be more difficult to establish and maintain • 3. Knowledge of the base system is lost (blind faith on programmer creating the table)

26. Advantages of the Functional Table Strategy • 1. Simplicity in data presentation and development. • 2. Reports run significantly faster. • 3. Changes in data standards much more adaptable (One place to make changes in data interpretation and NOT in every report.) • 4. Provides a transitional bridge from legacy to new system • 5. Provides a greater potential to ‘empower the users’. • 6. Reporting accuracy improved and many potential errors removed. • 7. Reporting consistency is greatly enhanced

27. Plus • 8. Shorter learning curve (no requirements on the part of the report writer to learn the core system data structure or methodology) • 9. Allows for continuation of longitudinal studies • 10. Allows for the creation of hybrid tables and thereby creating a TRANSITIONAL BRIDGE to SPAN the REPORTING ABYSS.

29. Questions?