Salary Management Collection Snapshots
The Salary Management snapshot tables were created to facilitate historical
reporting of employee and role/job-related data. Since it can be very cumbersome
to report on employee or role/job history from the standard tables, the snapshots
capture the employee, distribution, role/job or ethnicity record as it appeared
on last day of the accounting period. All users of the Salary Management
data collection are welcome to use the snapshots; the standard data collection
security applies to all queries of these tables as well.
The following are notes and cautions on the snapshot tables in general.
Please refer to table-specific notes for documentation
particular to individual tables.
- Snapshots exist for a subset of the Salary Management Collection tables,
including:
- The columns included in the snapshot tables are identical to those
in the current RSALMGT tables, with the additional
of columns to identify the accounting period of the snapshot. These
columns are:
Fiscal_Year
Fiscal_Month_Seq
Accounting_Period
Calendar_Year
Calendar_Month
Quarter_Seq
- Salary Management collection snapshots are taken on a monthly basis, after the end of the accounting
period but before any new transactions have been posted in the new period.
- The data goes as far back as the JUL-96 accounting period, in fiscal
year 1997), and continues from that point, through JUN-19 (fiscal year 2019), when Workday Human Capital Management was implemented at Penn. Snapshots were populated from available job and payroll change
log data for periods before SEP-02, with real-time snapshots beginning
in SEP-02. Therefore, some data elements may not be available as far
back as 1997, given the availability of values when the snapshots were
first created; please refer to the individual table notes for details
on exceptions.
- Following the retirement of PennWorks and the mainframe Personnel/Payroll system in July 2019, the legacy Salary Management collection and its snapshot tables will remain available for historical analysis. Only select columns in the EMPLOYEE_SNAP table (and the EMPLOYEE_GENERAL_SNAP view) and the JOB_CLASS_SNAP table (and JOB_CLASS_SNAP will be updated with Workday data, to support longitudinal and cross collection reporting.
- Since the snapshots capture data at the end of each period, data changes
are picked up as they occur. These changes are not applied to previous
periods, to preserve the accuracy of the snapshot as a view of the data
at a point in time. There may therefore be cases where data (such as
a birthdate) appears incorrectly in an early period, and appears correctly
in later snapshots after it was corrected in the source system.
- PennID/ SSN updates are applied to all collection tables (the regular
tables and their snapshots), so users can join properly on PennID or
SSN over time.
Table-Specific Notes
PENNWORKS SNAPSHOTS: The COMP_DISTRIBUTION_SNAP, COMP_DISTRIBUTION_EXT_SNAP, COMP_PERSON_SNAP, COMP_PERSON_ROLE_SNAP, COMP_PERSON_ROLE_JNT_ORG_SNAP, COMP_PERSON_SALARY_SNAP and COMP_PERSON_SALARY_SMRY_SNAP tables all date from the NOV-09 (November 2009) period, when PennWorks came into use.
- Note: In COMP_PERSON_SALARY_SMRY_SNAP the snapshot fiscal year is contained in the Fiscal_Year_Snap column.
EMPLOYEE & EMPLOYEE_GENERAL: Rows will
appear for employees as far back as EMPLOYEE_JOB records exist for the
individual. The snapshots capture the entire table as of the end of the
accounting period on the row. Note that this does include rows for terminated
employees or those on leave - the snapshot represents the EMPLOYEE table
in its entirety as it stood at the end of the period (this includes the
primary appointment and primary academic appointment data -- these are
not recalculated once the individual's employment has been terminated).
- Following the implementation of Workday in July 2019, only select columns in the EMPLOYEE/EMPLOYEE_GENERAL tables will be updated with Workday data, to support longitudinal and cross collection reporting. Refer to the data element indexes for details on mapping.
- For each employee, snapshots for periods before SEP-02 were populated
as follows:
- Uses Home_Dept_Org from EMPLOYEE_JOB for periods up until MAR-99,
as far back as EMPLOYEE_JOB records exist. For periods APR-99 to
SEP-02, Home_Dept_Org is derived from the Payroll Change Log.
- Calculated Primary Appointment and Primary Academic Appointment
based on available EMPLOYEE_JOB data, as far back as EMPLOYEE_JOB
records exist
- Populated Name, Birthdate, Penn_ID, SSN and SEX for all periods
for which EMPLOYEE_JOB data was available
- Beginning with APR-99, applied any corrections possible using
any available Payroll Change Log data for the employee
- The pre-Payroll Change Log (periods before APR-99) and post-Payroll Change-Log (APR-99
and after) updates handle nulls differently. If the Payroll Change Log showed
a "null" as the old value for the data element, the null will
appear in the contemporaneous snapshot. For periods before Payroll Change Log
data was available, elements for which no data was available will default
to ".".
- Email_Address is not populated for periods before SEP-02 (when snapshots
were first taken in real time). Since email address is loaded to the
Data Warehouse from the directory, there is no way to populate it historically.
- There may be inconsistencies in select pre-SEP-02 values of Academic_Base_Salary,
due to a formatting problem in the Change Log. In cases where the data
was unusable, the closest available correct data is carried back in
time until an earlier usable value was available.
- The following columns were added to the data collection in February 2009, and will only be populated in snapshots from that period and forward (for documentation on these elements, please refer to the EMPLOYEE element documentation page):
- BARG_UNIT_ELIG
- CPUP_BENEFITS_INDICATOR
- CPUP_JOB_INDICATOR
- CURRENT_DIST_INDICATOR
- CURRENT_JOB_INDICATOR
- EXEMPT_NONEXEMPT
- LAST_ACTION_DATE
- LAST_PAY_ PROCCESSED_DATE
- LAST_PERSONNEL_ACTION
- PAY_STATUS
- PRIOR_PAY_DATE
- The following columns were added to the EMPLOYEE table in March 2010, and will only be populated in snapshots from that period and forward (for documentation on these elements, please refer to the EMPLOYEE element documentation page):
- BENEFIT_BASE
- REHIRE_DATE (also available in EMPLOYEE_GENERAL_SNAP)
EMPLOYEE_BENEFITS: For periods May 2007 and after, the snapshots capture the entire table as
of the end of the accounting period on the row. No data is available for prior periods.Access to EMPLOYEE_BENEFITS_SNAP is restricted to select central organizations. Contact Human Resources if you need information from this table.
The following columns were added to the EMPLOYEE table in March 2010, and will only be populated in snapshots from that period and forward (for documentation on these elements, please refer to the EMPLOYEE_BENEFITS element documentation page):
- BRIDGING_DATE
- PHASED_RETIREMENT_DATE
- TDR_VESTING_DATE
- TDR_VESTING_MONTHS
- TDR_VESTING_PERCENT
EMPLOYEE_JOB and EMPLOYEE_DISTRIBUTION:
Snapshot views capture all records with a History_Status of 'C' as
of the end of the accounting period on the row (regardless of distribution
and/or appointment start or end dates).
- Since pre-SEP-02 periods of EMPLOYEE_DIST_SNAP and EMPLOYEE_JOB_SNAP
were populated in SEP-02, the load logic for those periods selects the
row that would have had a History_Status of 'C' at the end of the period
being populated. Only the History_Status_Date is compared to the period
end date. When there are two rows that meet the selection criteria,
the row with the earliest History_Status_Date is used. If there is more
than one such row with the same History_Status_Date, the one with the
earliest Last_Extract_Date is used.
- Since the snapshots capture the job record as it appeared at the end
of the month, multiple changes to one column over the course of a single
period will not appear.
- The History_Status, History_Status_Date and Last_Extract_Date columns
have been removed from the snapshot views, since data should always
be "current" as of the period (i.e., History_Status_Date >
last day of period).
EMPLOYEE_TAX: For periods January 2009 and after, the snapshots capture the entire table as of the end of the accounting period on the row. No data is available for prior periods.Access to EMPLOYEE_TAX_SNAP is restricted to select central organizations. Contact Human Resources if you need information from this table.
JOB_CLASS & JOB_CLASS_GENERAL:
For periods SEP-02 and after, the snapshots capture the entire table as
of the end of the accounting period on the row. Minimal historical data
is available for periods before that time, except for changes before and
after the Classification Redesign Project timeframe of April 1998. Therefore,
periods up to and including MAR-98 will include identical data from before
the redesign, while those APR-98 through AUG-02 will include job class
data as it appeared in AUG-02.
- Following the implementation of Workday in July 2019, only select columns in the JOB_CLASS/JOB_CLASS_GENERAL tables will be updated with Workday data, to support longitudinal and cross collection reporting. Refer to the data element indexes for details on mapping.
- ACADEMIC_CATEGORY, a higher-level grouping of FACULTY_CLASS, was created in January 2007, and only populated in the snapshot tables from then forward. As part of this implementation, three new FACULTY_CLASS values were created (ACN, FER, SEC) and some job classes remapped accordingly. The UNC faculty class, formerly used a a grouping of any job classes starting with '2' and not falling in another faculty class, is no longer used, as of January 2007.
- JOB_CLASS_EMPLOYEE_TYPE, JOB_CLASS_STATUS, and JOB_CLASS_STATUS_DATE were added to the JOB_CLASS table in January 2009, and only populated in the snapshot tables from then forward.
RSALMGT Snapshots (RSALsnap)
Universe
A special Business Objects universe, RSALMGT
Snapshots (short name RSALsnap) is available for querying the snapshot
tables. RSALsnap is structured very similarly to RSALMGT,
with the following differences:
- Fiscal_Year and Fiscal_Month_Seq are included in the joins between
the tables.
- COMP_PERSON_ADDRESS, EMPLOYEE_ADDRESS, EMPLOYEE_PAYMENT_SUMMARY and EMPLOYEE_ENCUMBRANCE_CURRENT are not
included in RSALsnap
- To improve query performance in joining EMPLOYEE_JOB_SNAP to EMPLOYEE_PAYMENT,
use the EMPLOYEE_PAY_JOB_SNAP view. This view accomplishes the outer
join between the base tables (based on fiscal year/month, SSN, job class,
job dept org and position number) to present all the data together in
what appears as one table to the user. EMPLOYEE_PAY_JOB_SNAP is joined
in RSALSnap directly to EMPLOYEE_GENERAL_SNAP (on common fiscal year/month/SSN).
When you use this view, do not pick any other objects or set conditions
from EMPLOYEE_JOB or EMPLOYEE_PAYMENT.
- There can be cases where a person may have more than one job with
the same class, Job/Home_Dept_Orgs and position number. In these cases,
when the job records are joined to the payment records (on common SSN,
accounting period, job/home org, position number), payment records may
be over represented. This will only occur for jobs with no position
number (which is represented by a 0). You should be able to clearly
see cases where this occurs, by retrieving the job sequence number in
addition to the other elements. For the most accurate join possible
between payments and jobs, use the Job_Link_ID
column in the standard EMPLOYEE_JOB and EMPLOYEE_PAYMENT tables (available
in the RSALMGT universe).
You can re-direct RSALMGT queries to the snapshot universe, so long as
all the result objects and conditions from your query exist in the universe
to which you'd like change. To do this, go to the Data menu, and select
"View Data" to display the Data Manager window. The General
section of the Definition tab contains a field called "Universe",
which has a small button with "..." directly to the right. When
you first display this window, the Universe field will display the universe
against which the query is currently directed. Click the "..."
button to display a list of all other universes to which you have access.
Choose the one to switch your query to, and click ok. As long as all the
objects in your query are available in the new target universe, that universe
name will now appear in the Universe field.)
|
