Volume 4. No 4 Information Technology News of the University of California, Davis December 1995

---

New Payroll/Personnel Data Warehouse Now Being Tested

Analyzing employee information will take on a new dimension when the new Payroll/Personnel System Data Warehouse goes online next year.

Poised for launching campuswide in April, the new P/PS data warehouse will give campus departments an opportunity to bring payroll and personnel information now available from the UC Office of the President directly to the desktop. What's more, those using the system will be able to answer such questions as how many people they have working in a particular class, or how much they paid out in sick pay in a given month.

"The information is there with a click of a few keys," says project manager Ray Reveles. "The ease of access is really surprising."

Like the UCOP database, the P/PS data warehouse has data on all UC Davis employees, and includes such information as accumulated earnings and information from Personnel Action Forms. Housed in a Unix system SunSparc 20 computer, the new P/PS data warehouse utilizes an Oracle Version 7 database.

Departments will have the same access privileges to the new data warehouse as they have had to the existing UCOP-based database - which generally means access to any non-confidential information pertaining to employees in that department. Although departments can already access UCOP-based employee information and view it on their office computers, the existing database cannot be used to download data, analyze figures automatically, or generate ad hoc reports.

To take advantage of the new system, departments need the following:

• A client/server capable PC or Macintosh - minimum power requirement is a 486 PC or Mac Quadra.

• A reporting program to generate queries, reports or extracts. Examples are Brio Query; Microsoft Excel, Access and Fox Pro.

• SQL Net - software that allows communication between the database and the individual's computer.

• An ODBC driver. This is required software for departments using ODBC-enabled reporting programs like Brio Query and Microsoft Excel, Access, and Fox Pro. Except for the cost of media, SQL Net and the ODBC driver software are available free to campus affiliates under the university's Oracle site license. ODBC driver software also is available from Microsoft.

Reveles has been working on the project for the better part of a year, along with Linda Honzik and Dave Squire, both of whom are also with Information Technology.

The data are arranged in tables that can be linked in any number of ways, so that a query can combine information from different fields. For instance, departments can obtain salary levels for specified title codes along with vacation pay for given months. By accessing the data warehouse, says Reveles, departments will be able to create a query and produce a report usually in just a few minutes.

Although campuswide implementation is not anticipated until April, 24 departments now are participating in a pilot project. Information gleaned from the pilot project, which by design includes participants with a wide range of technical sophistication, will be used to fine tune the new system.

If you have questions about the new Payroll/Personnel data warehouse, send e-mail to Ray Reveles at rreveles@ucdavis.edu or Linda Honzik at ljhonzik@ucdavis.edu.