Payroll Personnel Data Warehouse

SCHEDULE OF DATA UPDATE

The Payroll Personnel Data Warehouse is updated automatically from the Payroll/Personnel system (PPS) production data base at Office of the President, DaFIS Decision Support and Mothra data bases on campus. The update process has daily, weekly and monthly cycles. If any of these systems are not available, these automatic update processes will wait and begin later in the day.

For those using the PPS Decision Support web-based reporting tool, the date the data was last updated is identified on the main menu for each of the report categories:

Expense Reports	All Expense Based Reports. Data updated through Jun-2007 as of Jul-03-2007.
Personnel Reports	All Personnel Based Reports. Data is updated as of Jul-17-2007.

The schedule is as follows:

Employee, job appointment and payroll distribution data is updated daily. Most of the data comes from the Office of the President with Email addresses from the Mothra data base, financial org information from DaFIS and several derived selection flags.
Expense and Lien processes run monthly, near the beginning of the month. Payroll computes run throughout the month (3-4 times). As Office of the President runs these programs at night, the PPS Data Warehouse daily schedule updates the following morning.
The majority of the control or translation tables are updated weekly though some are on a daily update cycle.
Staffing List updates from Resource Management and Planning. Updates to this data happen daily during May, June and July in preparation for balancing staffing and weekly throughout the remainder of the year. Staffing data is not updated during August following the final version of the yearly staffing report. FINAL versions of the data are produced once a year and will be maintained for several years. REFERENCE versions of the data are produced periodically and will be maintained for a few years. The CURRENT version of the data is overwritten by a new CURRENT, REFERENCE or FINAL version of the Staffing data. Staffing list data will be updated weekly (Thursdays, after 10:00am) but for the months of May, June and part of July, the staffing list data will be updated daily (after 10:00am).
The Chart of Accounts and Organization Hierarchy tables are updated monthly from DaFIS.

There is a NOTIFICATION table in the Data Warehouse which identifies when the different parts of the Data Warehouse were last updated. This table also indicates whether an update is in progress. Each entry in this table relates to each of the different parts of the Payroll Personnel Data Warehouse.

EXAMPLE OF THE NOTIFICATION TABLE PLUS EXPLANATION:

Extract Name	Update Message	Update in Progress	Activity Date	System Explanation
edb	Update in Progress.	Y	21-MAY-07	The Employee Data Base tables are updated daily, beginning at 6:00am. They include the employees’ personal information, appointments and distributions. See Tables Description for tables beginning with ‘EDB’.
control	Update Completed.	N	20-MAY-07	The majority of the control or translation tables are updated weekly on Sundays however some are updated daily. They include home department code translations and job title code translations. See Tables Description for tables beginning with ‘CTL’.
expense	Update Completed.	N	06-MAY-07	Expense Distribution tables are updated on a monthly basis after the monthly maintenance process (identified as ‘MM’ on the Payroll Production calendar - usually during the first week of the following month.) The summarized expense tables includes the transfer of expense (TOE) tables. See Tables Description for tables beginning with ‘ETH’.
security	Update Completed.	N	21-MAY-07	Tables that maintain the access to the Data Warehouse are updated from the similar tables at Office of the President on a daily basis.
lien	Update Completed.	N	06-MAY-07	The table from the Committed Salary/Payroll Lien system. It is updated on a monthly basis - usually during the first week of the month.) See Tables Description for the table beginning with ‘CSE’.
edw	Update Completed.	N	06-MAY-04	The detailed Expense Distribution table is updated on a monthly basis after the monthly maintenance process (identified as ‘MM’ on the Payroll Production calendar - usually during the first week of the following month.) See Tables Description for tables beginning with ‘EDW’.
payroll	Update Completed.	N	06-MAY-07	Payroll Compute tables are updated after each of the pay compute cycles which happen 3-4 times a month (identified as ‘MOC’, ‘B1C’ and ‘B2C’ on the Payroll Production calendar). They include the earnings, hours and time adjustments tables. See Tables Description for tables beginning with ‘PAR’.
staffing	Update Completed.	N	20-MAY-07	The tables of Staffing List data are produced by Resource Management and Planning. FINAL and REFERENCE versions of the data will be maintained for several years with the CURRENT version being overwritten. The CURRENT staffing list is updated every Thursday after 10:00am. During the final stages of balancing staffing in May/June, the CURRENT staffing list will be updated daily after 10:00am. See Tables Description for tables beginning with ‘STF’.
the_date	Update Completed.	N	21-MAY-07	The date table is updated daily and is used by the update process.
fis	Update Completed.	N	06-MAY-07	DaFIS Decision Support tables that contain Chart of Accounts and Org Hierarchy information. They are updated on a monthly basis. See Tables Description for tables beginning with ‘FIS’.

Update processes not referenced in the NOTIFICATION table:

dvt	The locally-developed translation tables are manually maintained. As additional codes are added and as additional translations are needed, these will be updated. The Davis translation tables begin with 'DVT' and include medical code translations and school/division code translations.

Send comments to PPS Data Warehouse Support at: paysys-support@ucdavis.edu

Last Updated: September 10, 2007