Innovations

Richer PAS Database Aids Analysis

by Mary Brokaw

For the past couple of years, Financial Services/Payables Administration has been using data extracted from the Purchasing Accounting System (PAS) mainframe to answer questions about vendors, payments, requisitions and invoices.  Until recently, they were only able to obtain a small data feed from the mainframe system. This data, stored in an MS Access database has been of a limited scope with very little detail, and has limited analytic possibilities.

Tom Phillips, Financial Data Analyst in Financial Services, identified the need for Financial Services to get more PAS data for analysis.  He found that there were two basic problems with the current dataset.  First, it did not include enough information.   For example, the original database would not include the data necessary to build a report to show vendors and all of their accounting transactions within the UW.  Secondly, the PAS data is purged (deleted) every six to seven months, leaving us with no historical purchasing records. 

Tom recognized the value in capturing the historical data for analysis and rounded up a workgroup consisting of Dinah Walters and Nate Findley of the FM Decision Support Center, and Glenn Eades, a programmer, to design a solution.

The new larger dataset was extracted from PAS based on ten data tables from the PAS mainframe.  The data extracts were then loaded into MS Access for testing to ensure that the right data was extracted.  It was soon discovered that the new volume of data was too large for MS Access. 

In search of an alternate database solution, the workgroup contacted C & C.  C & C Client services assisted the team in acquiring UW server storage space for the new database.  This would enable to the group to build a larger database using a database platform called mySQL.  mySQL is a more robust technology that can handle larger volumes of data and more complex database design.  The new PAS mySQL database is in the final stages of completion and should be ready for production in the first two weeks of November.  The database will begin with January 1, 2005 data and will continue to grow as each new month is added.
 
There are many advantages of the new database. Public records will be easier to process, there will be less special ERGO requests, and, of course, the data will be used to support our ongoing process improvement efforts.   According   to Tom Phillips, who will be the main database user, “The new system is not foolproof, but the level of detail and quality of our available information has increased significantly!” 

The project is sure to be a great success.  The team had a great mix of technical expertise and business process knowledge, which made the identification of the necessary data, the building of a relational database, and ultimately the project itself, achievable.  Ultimately, the team would like to see the PAS data become part of the data warehouse that is being constructed by the Data Warehousing Program in C & C, but until then, we have an excellent solution for our analysis needs.