The Data Warehouse: a Transitional Bridge between Legacy and PeopleSoft

1. The Data Warehouse: a Transitional Bridge between Legacy and PeopleSoft 11:20 – 12:10 May 16, 2005 CUMREC 2005 Conference Keystone, Colorado

2. Your Presenter • Art Brooks, Director of Applications University of Missouri – Rolla • 12 years in Registrar’s Office • 26 years in Information Technology • Involved in data warehousing since 1986 • Instructor of Data Warehouse course

3. University of Missouri - Rolla • Founded 1871 as the Missouri School of Mines and Metallurgy • Smallest of 4 campus system. • Enrollment around 5500. • Predominantly engineering and science.

4. University of Missouri and PeopleSoft University of Missouri has implemented Finance and HR across the enterprise. Admit/Recruit is in production at UMKC, UMR and UMSL. Student Records is in production only at UMR (since January, 2004) NO campus has Student Financial Aid implemented nor scheduled at this time.

5. The Evolving Data Warehouse • UMR Started data warehouse adventure in 1986. • Over 18 years the data warehouse and reporting has transitioned: • from local mainframe SQL/DS to local client/server with Informix • from Informix to Oracle • from native SQL for report creation to InfoMaker • through four strategic methodological changes • through three version upgrades in the Advance system • through implementation of PeopleSoft • through one PeopleSoft version upgrade.

6. Presentation Topics • Basic reporting perspective • UMR’s unique approach • The Registrar’s conversion

7. Basic Reporting Perspective KEEP IT SIMPLE

8. The Players and their roles • Technical staff– address the data infrastructure, provide a user friendly reporting software and provide training as well as consulting services. • Functional office– identify and define reporting needs. Produce new reports, handle production scheduling and submit reports.

9. Reporting: • Is user oriented. • Is an entity separate from the transactional system. • Must be insulated from transactional system changes. • Must include a transitional bridge. • Can begin BEFORE a system conversion.

10. Data Warehouse dividends • Is an evolutionary phenomenon. • Insulates business processes (reporting side) from changes in hardware or software. (and in some cases process changes) • Provides extended flexibility. • Produces a point in time reference for reporting. • Produces a single point for change. • Can provide consistency across reports and time. • Can eliminate some logic errors. • Can simplify the data.

11. UMR Download Process UM core systems Campus systems • Data is refreshed • Nightly • Weekly • Seasonal • On-demand UMR written download scripts UMR DW Reports Applications

12. 4 fulltime staff Schedule downloads from core systems Maintain download scripts Local DW table support Create new tables Write reports Define data to users Advise and assist users Staff for reporting and data warehouse

13. UMR’s Unique Approach FOCUSED ON THE USERS

14. The UMR Approach • Free standing • Ambiguously • Related • Entities • In contrast to such conventional approaches as star schemas, fact tables and dimension tables.

15. The Reporting Foundation • ‘Event oriented, functional tables’: relational tables designed for a specific reporting need that draws data from multiple data warehouse tables, focusing on the needs of the user and not the technical staff.Goal– reduce technical requirements to formatting the output page. SIMPLICITY!!

16. ‘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 YOU can do this!

17. Concept • Simplicity • Zero table joins • Zero ‘Where’ statements • Event oriented • Report design centric • User takes less than five minutes to develop query • User concentrates on report appearance • Data source is the data warehouse

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

19. 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. Estimated eleven fold reduction in code to create a report • 11. Allows for the creation of hybrid tables and thereby creating a TRANSITIONAL BRIDGE to SPAN the REPORTING ABYSS.

20. Comparison of Relational Approaches Fact and dimension structure with Star Schema UMR Approach Ed. data Appt data Functional HR table with same data Fact HR table Benefit data Bio data Dept data Address data

21. Report creation process Report created by user or technical staff Technical staff creates appropriate functional tables Finished report User identifies report requirements and format

22. Proven Approach • Concept developed in 1998 • First put into use in 1999 with conversion of University Advancement system from mainframe to client/server • Over 500 new reports created for that department using this technique • Utilized in 2000 to retain orientation system when Admissions implemented PeopleSoft • An integral part of reporting solution at UMR prior to 2004 Registrar’s PeopleSoft implementation

23. Example report:User requests a count of all new freshmen University of Missouri – Rolla New Freshman Applicant Count FS2005 Major Count Biology 21 C Sc 53 IST 78 MIS 39

24. Report Creation Comparisons UMR Functional Table Query Direct PeopleSoft Query select a.admajor, count(*) from admfresh a where a.adterm=’FS2001’ and a.fraction=’A’ and a.fractiondate<=sysdate group by a.admajor”; select f.acad_plan, count(*) from ps_pers_data_effdt a, ps_pers_dtef_sa_vw a1, ps_stdnt_career b, ps_adm_appl_data c, ps_adm_app_car_seq d, ps_adm_appl_prog e, ps_adm_appl_plan f, where a.emplid=a1.emplid and from ps_pers_data_effdt a_eda.effdt=a1.effdt and (a.effdt=(select max(a_ed.effdt) where a.emplid=a_ed.emplid anda_ed.effdt<=sysdate) and a.emplid=b.emplid and b.acad_career=c.acad_career and c.emplid=d.emplid and c.acad_career=d.acad_career and c.stdnt_car_nbr=d.stdnt_car_nbr and c.adm_appl_nbr=d.adm_appl_nbr and d.emplid=e.emplid and d.acad_career = e.acad_career and d.stdnt_car_nbr = e.stdnt_car_nbr and d.adm_appl_nbr = e.adm_appl_nbr and d.appl_prog_nbr = e.appl_prog_nbr and e.effdt=(select max(e_ed.effdt) from ps_adm_appl_prog e_ed where e.emplid=e_ed.emplid and e.acad_career=e_ed.acad_career and e.stdnt_car_nbr=e_ed.stdnt_car_nbr and e.adm_appl_nbr=e_ed.adm_appl_nbr and e.appl_prog_nbr = e_ed.appl_prog_nbr and e_ed.effdt <=sysdate) and from ps_adm_appl_prog e_es where e.emplid=e_es.emplid and e.acad_career=e_es.acad_career and e.stdnt_car_nbr=e_es.stdnt_car_nbr and e.adm_appl_nbr=e_es.adm_appl_nbr and e.appl_prog_nbr=e_es.appl_prog_nbr and e.effdt=e_es.effdt and e.prog_status=e_es.prog_status) and e.admit_term=’FS2001’ and e.prog_status in (‘AC’, ‘AD’) and e.action_dt <= sysdate and c.admit_type=’FTC’ and d.acad_career=’UGRD’ and e.emplid = f.emplid and e.acad_career=f.acad_career and e.stdnt_car_nbr=f.stdnt_car_nbr and e.adm_appl_nbr=f.adm_appl_nbr and e.appl_prog_nbr = f.appl_prog_nbr and f.effdt=(select max(f_ed.effdt) from ps_adm_appl_plan f_ed where f.emplid=f_ed.emplid and f.acad_career=f_ed.acad_career and f.stdnt_car_nbr=f_ed.stdnt_car_nbr and f.adm_appl_nbr=f_ed.adm_appl_nbr and f.appl_prog_nbr=f_ed.appl_prog_nbr and f_ed.effdt<=e.effdt) and f.effseq=(select max(f_es.effseq) from ps_adm_appl_plan f_es where f.emplid=f_es.emplid and f.acad_career=f_es.acad_career and f.stdnt_car_nbr=f_es.stdnt_car_nbr and f.adm_appl_nbr=f_es.adm_appl_nbr and f.appl_prog_nbr=f_es.appl_prog_nbr and f.effdt=f_es.effdt)) group by f.acad_plan; 1 query 1 table 7 distinct tables 12 process tables 0 joins 3 'where' statements 6 joins 6 lines 50 'where' statements 5 sub queries 70 lines

25. Report Prepared direct from PeopleSoft PS table 2 PS table 3 PS table 4 PS table 5 PS table 6 PS table 7 PS table 1 Sub Query 1 12 Process tables 12 Process tables 12 Process tables Query Sub Query 2 12 Process tables 12 Process tables 12 Process tables Sub Query 3 Sub Query 4 Report: Count number of Freshmen for Specified term Sub Query 5

26. Report prepared from UMR functional table UMR functional table Query Report: Count number of Freshmen for Specified term

27. 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 or are removed.)

28. Hybrid table translation Legacy Stdnt No. Addr_type Addr_line1 12345678 L 123 Bishop Ave 23456789 203 State M 34567890 4578 Hampton Circle B Converted PeopleSoft Stdnt No. Addr_type PS_Addr_type Addr_line1 123 Bishop Ave 12345678 L LOC 23456789 203 State M Mail 34567890 4578 Hampton Circle B BUS Table name (address) is unchanged

29. Hybrid table flexibility Code changes kept to a minimum Legacy reports Hybrid Tables Output New reports

30. Evolution of a Hybrid Table

31. Bridge tables Legacy system tables Code Abbrev Description 001 Aerospace Engineering AE PeopleSoft bridge tables Code Abbrev PS_abbr Description 001 AE Aero E Aerospace Engineering

32. Multi-year reports UMR DW New Reports PeopleSoft tables Longitudinal studies/ trend analysis requests Hybrid tables Existing Reports Bridge tables Legacy Tables

33. The Registrar’s Conversion KEEP THE BUSINESS PROCESSES FUNCTIONING

34. Road Closed -- Construction • When transitioning from a legacy system to a new system, you CANNOT shut reporting down. • Tantamount to shutting down an Interstate for road construction. • MUST find an alternate solution.

35. Registrar’s Environment • Legacy system in production since 1975 • Mainframe based • Four campus system • Nearly all UMR campus reports utilizing local data warehouse since late 1980s.

36. Strategic Statementfor Registrar’s Conversion • “If data can be converted from legacy to PeopleSoft, then data can be translated from PeopleSoft to legacy.” (We NEVER stated ALL of the data could be translated.) • Terms: • Converted – data in a legacy format, modified and loaded in PeopleSoft tables • Translated – data in a PeopleSoft format, modified and loaded in legacy defined tables.

37. The Challenge • With modules being implemented over a six year period of time, applications and reports had to continue to function when some data was in the mainframe in a legacy format and other data was in PeopleSoft in a client server/relational environment. • This required applications and reports to draw data from tables that had diverse sources. • Inventory indicated over 3,000 reports and 25 applications built over a 10+ year period of time would cease to function. • No defined subset of reports required or in use.

38. Implementation tactics • Wanted to ‘BUY TIME’ to keep reports running until replacements could be written. • Focused on identifying PeopleSoft data to re-create legacy tables for reports needed within first 2 weeks of the semester. • All staff dedicated to effort: • Programming staff worked on bringing tables up, AB tested and modified needed existing reports. • After 2 weeks focused on next set of most critical tables. • Found some data just could not be re-created. Developed a work around.

39. UMR Startup Tactic UMR DW New Reports PeopleSoft System Hybrid tables Bridge tables Existing Reports & Applications Legacy Tables Users did not see the difference.

40. Results • Translated PeopleSoft admissions data for 3 years without interrupting production. • Integrated PeopleSoft HR with student legacy for 2 years without interrupting production. • Integrated legacy and PeopleSoft grant data for a fiscal year report. • Amended HR data without affecting applications. • Had 400 reports and all applications needing student data in production by the end of the 4th week of classes. • Integrated admissions PeopleSoft data with legacy student data for 18 months until student system went live with PeopleSoft

41. Questions?

42. Contact Art BrooksDirector of Applications Information TechnologyUniversity of Missouri – Rolla E-mail: artb@umr.edu