1 / 48

Data Warehousing: You’ve Captured and Cleansed It, Now Exploit It!

Data Warehousing: You’ve Captured and Cleansed It, Now Exploit It!. A Case Study Using Payroll and Budget Systems. Ryan Cherland and Brenda Tucker University Management Information University of Kansas. Polling Question:

lobo
Download Presentation

Data Warehousing: You’ve Captured and Cleansed It, Now Exploit It!

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Warehousing:You’ve Captured and Cleansed It, Now Exploit It! A Case Study Using Payroll and Budget Systems Ryan Cherland and Brenda Tucker University Management Information University of Kansas

  2. Polling Question: • How many attendees currently have data warehouses that contain Payroll and Budget data? • Have this in a data warehouse • Do not have this in a data warehouse • Do not have a data warehouse

  3. Key Objectives: • Discuss how KU has developed a data-warehouse that brings together payroll, budget, and financial data and the methods that the data is exploited • What this has meant to users on our campus

  4. About the University of Kansas • History - The University of Kansas opened its doors in 1865. • Academics - The university offers more than 100 undergraduate and graduate majors and programs including allied health, architecture, business, education, engineering, fine arts, journalism, law, liberal arts and sciences, nursing, pharmacy, and social welfare. • Lawrence Campus Enrollment – 20,692 undergraduates and 6,122 graduate students from every state in the nation and more than 100 countries around the world.

  5. About the University of Kansas Staffing* • Faculty, Librarians, Faculty Administrators 1,426 • Unclassified Nonfaculty 1,756 • Classified 1,480 • Student Employees 4,032 * As of Fall 2003

  6. Polling Question: • What business sectors are the attendees from? • Educational institution • Commercial sector • Private sector • Public sector

  7. KU’s Data Warehouse Data from: • Human Resources • Financials • Budget • Student • Research Are extracted into a data warehouse as often as business needs require

  8. KU’s Data Warehouse • Data in the warehouse is exploited using: • Web query front-end (DEMIS) • BI analytic software for power users

  9. DEMIS Departmental Executive Management Information System • Campus Intranet system for the University of Kansas • Developed gradually over-time from a few static web pages to a campus management ‘portal’

  10. What is DEMIS? DEMIS is a decision support system, which can be defined in many ways. One definition that fits DEMIS well is: “A decision support system is an interactive system that provides the user with easy access to decision models and data in order to support semi-structured and unstructured decision-making tasks.” (Watson, Houdeshel, & Rainer, 1997, p.265)

  11. What is DEMIS? Key parts for a decision support system are: • Ease of use • Contains models or analytical aids used to analyze the data • Data are maintained to be used in the analysis

  12. DEMIS User Menu DEMIS is password protected • User authenticates • Userid is used to create a custom series of available links that their campus security profile allows • 5 broad areas of information from DEMIS: General, Academic, Student Administration, HR/Pay, and Financial

  13. DEMIS Main Menu

  14. DEMIS Query Architecture A DEMIS Query web form Filters + Rpt Breaks User receives output in HTML, RTF, PDF, or Excel-ready format CGI Functional Subject Area Data mart SAS report program

  15. Solving a Business problem by exploiting related systems • With daily extracts of KU’s transactional systems, initial queries just reported on different views in those systems • Then a business need arose….

  16. Development of an Integrated Budget System Expectations • A flexible, powerful reporting system • Convenient for campus departments • Accessible for the university community

  17. Current Budget System Customized system, housed within KU’s Financial system • Dollars and FTE, if applicable, managed at three levels • Positions • Pools • Other Operating Expenditures (OOE)

  18. Limitations of Current System • View access is limited • Update access even more limited • No departmental reporting

  19. Budget Data Limitations • OOE expenditures not posted to the budget system • Transactional data held in the source database • No expenditure history • No position history • No detail on employees included in pools

  20. The Problems: • The budget data by itself provided a very “flat” view of the University’s financial activities • Restricts auditing abilities • Hinders reconciliation with other DEMIS tools

  21. Can the web reporting product be made more robust?

  22. Sources of related data in the Budget: Human Resources

  23. Sources of related data in the Budget: Financials

  24. Sources of related data in the Budget: HR related Financials

  25. Data Choices • How to structure data across fiscal years? • What filters and report breaks do users need? • How will users view entire transfer transactions?

  26. Implementation Issues • Report links enable drill downs to non-Budget datamarts • Pool type-staffing detail • YTD • Separate datamarts for each fiscal year • Data warehouse dependencies • Create link to view complete transfer transaction

  27. The Initial System • Two levels of web form: Power User and Departmental User • Multiple report summary/break levels • Web forms created twice daily • Powerful datamart accessible to campus power users • Datamart updated twice daily to capture transfer transactions and expenditure data • Dynamically created links show detail behind the summary numbers

  28. Budget Web Report Filters

  29. Web Form Report & Output Options

  30. Sample Employee Report

  31. Sample Employee ReportPopup Window Transfer Detail

  32. Initial System Overview

  33. And with Success…. Comes more challenges • Develop a salary forecast system • Integrate it with the new budget system

  34. New Implementation Issues • Report links enable drill downs to non-Budget datamarts • YTD by Pay Period • Forecast by Pay Period • Data warehouse dependencies

  35. A Quick view of the Salary Forecast model…

  36. A Quick view of the Salary YTD model…

  37. Sample Employee Report

  38. Sample Employee Report - YTD

  39. Sample Employee Report - Forecast

  40. Budget Web Audit Report

  41. Current System Overview

  42. Benefits of Integration to Users • Flexible reporting • Timely data • Auditable • Balances Across Databases • Future Payroll Funding Problems • Facilitated Decentralized Culture Shift

  43. Future Directions • Increasing the “drill-down to detail” functions in reports to other related systems • Reusable code • No data duplication • Increases validity of reports and data to the users • Development of an integrated Executive Dashboard for high level administrators

  44. Data Warehouse Benefits to Users • Standardized approach to data fields and reporting • Time saver – single place for reporting needs • Provides historical data • Consistency of information • Decreased reliance on department “shadow systems” • Transactional systems are protected from having to allocate resources for reporting

  45. Conclusion Campus users have stated that the Budget reporting system has proved to be a truly valuable management tool for financial planning and analysis. Projects that used to take several days now take minutes.

  46. Conclusion • How we did it: • Incremental approach • Financial 1999; Payroll 2000; Budget 2001; Forecast 2002; Integrated Budget 2003 • Created systems which met the largest needs first • As we learned more and the users learned more, we increased the number of specialized reports using the same data

  47. QUESTIONS?

  48. Contact Information • Ryan Cherland, Director • University Management Information • Email: ryan-cherland@ku.edu • Brenda Tucker, Senior Programmer Analyst • University Management Information • Email: brenda-tucker@ku.edu

More Related