Penn Computing

Penn Computing

Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

 
  UPDATES
Current Load Status
Regular Availability
 
  INFORMATION
FAQs & Tips
Password Changer
Support services
Security
About the Data Warehouse
Data Administration
 
  DATA COLLECTIONS
Advancement
Assets
BBR
BRIM
Express Mail
Facilities
Faculty
GAR
General Ledger
Infrastructure
ISSS-iOffice
Learning Management
Position Inventory
Research-PennERA Proposals
Research Inventory
Salary Management
Student
Travel Expense Management
Tuition Distribution
World Travel
Cross-Collection

Salary Management Collection Snapshots

·Table-specific notes
· RSALMGT Snapshots (RSALsnap) Universe 

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.

  • 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 the most recent closed period. 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.
  • 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).

  • 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.

  • 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.)

top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


Penn Computing University of Pennsylvania
Information Systems and Computing, University of Pennsylvania