Salary Management Data Collection

Following the retirement of PennWorks and the mainframe Personnel/Payroll system, the legacy Salary Management data collection remains available for historical analysis.  Only select columns in the EMPLOYEE and JOB_CLASS tables (and the EMPLOYEE_GENERAL and JOB_CLASS_GENERAL views) will be updated with Workday data, to support longitudinal reporting. Other tables in the Salary Management collection will not be updated beyond early July, 2019. For information about Workday Human Capital Management data in the Warehouse, visit the Human Capital Managment Data Collection site.

The following details explain the refresh schedule of the Salary Management collection before the implementation of Workday in July 2019:

Biographical, role and job data from PennWorks and the mainframe Payroll system is refreshed daily, as well as Change Log data and the Primary Appointment calculation, on Monday through Friday evenings. Note that there is period each night during which Primary Academic Appointment information may be null, due to job scheduling dependencies. Employee, role, job and distibution data snapshots are created at the end of the accounting period.

Payment data, including the PAR tables, is refreshed weekly, on Monday evenings, after the computation of payroll and encumbrances for weekly employees on the mainframe system. Monthly payments are loaded on the last Friday before the last weekly Payroll run for the month. Since payments are loaded to the Data Warehouse before BEN Financials, the best time to compare payroll and General Ledger balances is after the payments are loaded to BEN Financials, but before new payments are loaded to the Warehouse. To pull matching data from BEN Financials, Salary Management and the Data Warehouse at the end of a period you'll need to run your system reports and Warehouse queries between the Tuesday after payroll runs, producing the last weekly check for the month, and before the Monday of the following week. By that last Tuesday, monthly payroll has already been run, so those actuals and updated encumbrances will be correct. This limits the risk of retrieving data from Salary Management or the Warehouse for the last weekly pay of the month which may, in fact, be posted to the next BEN Financials Accounting Period. (For example, for AUG-99, reports would have to have been run between August 27 and August 30th.)

Social Security Number changes are loaded weekly, on Tuesday evenings.

Budget data, (extracted from the legacy Pillar system), stored in the BUDGET_BASE and BUDGET_DISTRIBUTION tables, is not currently refreshed, and contains data for fiscal year 1997 only.

Source of the Data
The data in the data warehouse that is accessible via the Business Objects RSALMGT universe comes from the PennWorks system, as well as the mainframe Payroll and Salary Management systems. The data in this Universe includes biographical information about employees, information on their roles/jobs, and financial data on their salaries (encumbrances, distributions and payments).

Time Span Covered
The Data Warehouse stores current information on employees, and current and historical information on their jobs and salaries. The data goes as far back as July 1, 1996 (the JUL-96 accounting period, in fiscal year 1997), and continues from that point, through early July 2019.  The exception is select columns in the EMPLOYEE and JOB_CLASS tables (and the EMPLOYEE_GENERAL and JOB_CLASS_GENERAL views) which will be updated with Workday data, to support longitudinal reporting.

Payroll Audit Record (PAR) data is available from fiscal year 2005 through June 2019.

PennWorks-specific data is available from November 2009 through June 2019.

The Salary Management collection is described in the RSALMGT, RSALMGT Snapshots, and Employee General diagrams. Each diagram illustrates the architiecture of a specific Business Objects universe and identifies the join criteria between tables.

• The RSALMGT diagram provides access to the employee biographical, job, distribution, encumbrance and payment data. In most instances, you will use this diagram when querying the Salary Management Data Collection. This diagram should be printed using landscape orientation.
• The RSALMGT Snapshots (RSALsnap) diagram identifies the tables that make up the Salary Management Snapshot universe and their join criteria. This diagram should be printed using landscape orientation.
• The RSALMGT PAR (RSALPAR) diagram describes the RSALPAR universe, which joins the restricted PAR tables to the generally available RSALMGT universe. This diagram should be printed using landscape orientation.
• The Employee General diagram provides access to the EMPLOYEE_GENERAL and related tables, for the purposes of viewing basic employee and primary appointment information with no salary or job details.

The BusinessObjects repository contains reports which users can use to retrieve data to which they have access. The reports available vary by data collection and business use, as listed below. You will only be able to view or use reports in the public folders that you have access to. Documents should be renamed and saved to your own "My Favorites" folder on the portal if you need to edit them using Webi. Documents can only be edited in Webi when placed in your "My Favorites" folder.

RSALMGT - Payments and Encumbrances is actually comprised of two separate queries (Payments and Encumbrances), linked by the posted account, job class and SSN. The Payments query retrieves all fiscal year to date payments for employees paid from a given ORG, while the Encumbrances query retrieves the remaining encumbrance balances for employees from the same posted ORG. The queries are each represented separately on the appropriately named tabs, and, once linked (common elements are Job Class, Posted COA Account, and SSN), the combined results are presented on the Combined tab to show the fiscal year commitment for each person (by account and job class). Data in this report can be verified using Salary Management reports, or at a higher level (by account segments) using BEN Financials reports.

PennWorks Administrative Roles - Description required: Uses the RSALMGT universe and can be used to identify administrative roles in need of updates in PennWorks. The single report tab displays individuals having an active role associated with job class 140010 (formerly known as A-1 Academician) for which a PennWorks administrative role has not yet been associated. The Role ORG and term dates are displayed, along with the Role State.

PennWorks Change Log: Uses PennWorks change log data available in the Payroll Change Log (change_log) universe, and can be used to monitor updates made to employee records in the PennWorks system. Access to the Change Log data in the Warehouse is required to run this report. The first tab, "Changes Summary", displays by individual, PennWorks update actions and the date they occurred, along with basic information about the person who performed the update for the report prompt timeframe and Home ORGs. The second tab, "Element Change Detail", provides the detail old/new values, by data element changed, for each update listed in the first tab.

PennWorks Compensation - Academic Base Review: Consists of two tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used to identify individuals for whom Academic Base compensation may need to be entered or updated in PennWorks. The first tab, “Standing CE and Lecturer C - Without Academic Base” displays records only for individuals in the Faculty population who should have an Academic Base Salary, but for whom one has not been entered in PennWorks for the report timeframe. The second tab, “Standing CE and Lecturer C - All Compensation Types”, displays whatever role and compensation data has been entered in PennWorks for the report timeframe, for the entire population in the Home ORG range.

PennWorks Compensation - Administrative Stipend and Role Review: 
Consists of two tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used together to identify individuals for whom Administrative Stipends or roles may need to be entered in PennWorks. 
The first tab, “Administrative Stipend Salary without Admin Role” displays records for individuals for whom an Administrative Stipend has been entered on the PennWorks Compensation tab, but who do not have a PennWorks administrative role active for the stipend timeframe. The second tab, “Admin Roles without Administrative Stipend Salary”, presents the converse view, showing individuals who have an administrative role in PennWorks, but for whom an Administrative Stipend has not been entered for the role timeframe.

PennWorks Compensation - Salary Distributions and Payments Review: 
Consists of three tabs which present information from the PennWorks and Salary Management data available in the RSALMGT universe, and can be used to compare PennWorks Faculty compensation salaries to distributions and actual payments.

• The first tab, "Faculty Compensation Comparison Summary" displays the Faculty compensation salaries and annualized distribution totals, summarized by person, with variances highlighted in red.
• The second tab, “Faculty Compensation Distribution and Payments” displays whatever role and compensation data has been entered in PennWorks for the report timeframe, for the entire population eligible for compensation data maintenance, in the Home ORG range. In addition, fiscal year calculated totals for PennWorks and Payroll distributions, as well as actual year to date Payments are summarized by Faculty Compensation category.
• The third tab, “Monthly Payments Review”, provides a view of actual Payments to the individuals eligible for compensation data maintenance in the Home ORG range, for a single prompted Accounting Period. Monthly Payment totals are compared to calculated monthly Faculty Compensation salary and Role Annual Salary amounts. Summary regular pay differences of greater than $100 are highlighted in red.

PennWorks Compensation and Roles: Uses the RSALMGT universe and can be used to monitor PennWorks compensation and role data. The single report tab displays whatever role and compensation data has been entered in PennWorks for the report timeframe, for the entire population eligible for compensation data maintenance, in the Home ORG range.

PennWorks - Distributions Ending Review: This report consists of two tabs which present information from the PennWorks and Payroll data available in the RSALMGT universe, and can be used to review PennWorks and Payroll distributions ending during a given date range. The first tab, "PennWorks Distributions" displays PennWorks distributions for each individual, along with related role information and distribution and role states (only Final and Projected states are included). The second tab, "Payroll Distributions", displays the distributions for the same individuals that are in the Payroll system for processing.

PennWorks - Distributions Review: This report consists of two tabs which present information from the PennWorks and Payroll data available in the RSALMGT universe, and can be used to review PennWorks and Payroll distributions.

PennWorks - Distributions vs Pay End Date: This report consists of three tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used to review whether distributions are active for employees as of the desired pay period end date.

PennWorks - Projected Roles and Distributions: Uses the RSALMGT universe and can aid in monitoring PennWorks roles or distributions whose state needs to be changed to "Final." The single report tab displays and highlights, by individual, PennWorks role and distribution records having a role or distribution state of “Projected.” In addition, role term start dates and distribution start dates less than a month in the future from the report run date are highlighted in red.

PennWorks - Role Salary and Distribution Review: Consists of two tabs which present information from the PennWorks data available in the RSALMGT universe, and can be used to compare PennWorks role annual salaries to distribution totals for monthly-paid employees. The first tab, “Salary and Distribution Comparison” displays PennWorks roles for each individual, along with annualized distribution totals for the role. The right-most column displays the difference between the role annual salary and the calculated annual distribution total. Differences of greater than $100 (positive or negative) or are highlighted. The second tab, “Distribution Detail”, displays the underlying Distribution COA Number and start/end dates that comprise the total on the first tab , to aid in navigating to the particular distributions in PennWorks. All roles and compensation states for current monthly or weekly-paid (non-hourly) employees are included in this report. External distributions not paid from Penn’s Payroll system are included where available.

Salary Increase - Current Faculty Compensation for Export: Presents information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to create the spreadsheet of faculty compensation information (e.g., academic base salary, etc.) needed to begin the Salary Increase Program process to update faculty compensation within an organization.

Salary Increase - Current Roles for Export: Presents information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to create the spreadsheet of role information needed to begin the Salary Increase Program process within an organization.

Salary Increase - Faculty Roles and Compensation Reports: Consists of several tabs which present information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to monitor progress of, and review data entered during the Salary Increase Program cycle for faculty roles and compensation categories. Throughout the report, current role information from PennWorks is displayed along with “new” data entered in the Salary Increase Program modules (if available). Faculty compensation data (current and new, if available) is also presented. The report is comprised of three providers, one of which retrieves role data, another the compensation categories, and the third retrieves any related justification information.

Salary Increase - Role Changes by ORG: Presents information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can bbe used to monitor progress of, and review data entered during the Salary Increase Program cycle for staff and faculty roles. Note that distribution information is NOT included.

Salary Increase - Role Salary and Distribution Reports: Consists of several tabs which present information from the PennWorks Salary Increase Program data available in the RSALMGT universe, and can be used to monitor progress of, and review data entered during the Salary Increase Program cycle for staff and faculty roles and distributions. Throughout the report, current role information from PennWorks is displayed along with “new” data entered in the Salary Increase Program modules (if available). The report is comprised of three providers, one of which retrieves role and summary distribution data, the second retrieves any related justification information, and the last retrieves more detailed distribution data.

Grants-related:

Payroll is used to identify all salary charges made on a specific fund (listed by person or by object code) for any period of time.
Payroll(all grants of RESP ORG) is ideal for running monthly detail for all funds for a RespOrg. Similarly, Payroll(all grants of PI) can be used for retrieving detail for all funds for a PI. Since these queries can bring back data for several funds we recommend that you run them for periods less than 3 months. 
Personnel is used to identify all payments made to a specific person for a period of time, and provides a cumulative percentage of all payments over that period of time that were charged to a specific fund. These reports are used extensively in reviewing the validity of effort on non-competing proposals.
ORGPersonnel displays current payroll distributions, including percentages and fund descriptions, for monthly and weekly paid employees for a given home organization. When the salary is above the NIH cap (for NIH-sponsored funds only), the report also displays the percentage the distribution contributes to the current cap. Additionally, separate tabs are provided to display current allocations hitting expired grants, as well as allocations hitting suspense because the grant is frozen. *
Note: you must have access to the Salary Management collection in the Data Warehouse to retrieve results for these reports.

Payments after Account End: Paylate is used to identify identify all grants that which have been used in employee payments where the check date or pay period end date is after the fund's Account End. The information is summarized by ORG, detail provided by fund, with a separate tab indicating funds that can be worked on prior to freezing. *
Note: you must have access to the Salary Management collection in the Data Warehouse to retrieve results for this report.

Data training for this collection is offered on an ad hoc basis -- please contact Enterprise Information & Analytics for assistance with specific training questions. You can also refer to the table and element documentation for information and cautions about specific data.

Those who wish to access the Salary Management data collection (including the PAR_MAIN and PAR_EARNINGS tables) must submit the Data Warehouse Access Request eForm for Salary Management Data. Although there is no training prerequisite, approval from the requestor's supervisor and school/center access administrator is required. Individuals only requesting access to the Employee General table should complete the Employee General eForm instead.

Individuals requesting access to the restricted PAR tables (PAR_DOLLAR_GTN_ADJUSTMENTS, PAR_GROSS_TO_NET, GAR_HOUR_ADJUSTMENTS, and PAR_TAX_ACH_BENEFITS) must already have access to the Salary Management collection, and should submit the Data Warehouse Access Request eForm for PAR Data as well.

Individuals requesting access to the PAYROLL_CHANGE_LOG data must already have access to the Salary Management collection, and should also submit the Data Warehouse Access Request eForm for Payroll Change Log Data.

Releasing Data Outside the University 
The only two offices that may disclose employee, job, or salary data outside the University are the Division of Human Resources and Institutional Research & Analysis. If you do not work for those offices and you receive a request for data to be sent outside the University, refer the requester to the Division of Human Resources.

Releasing Data Within the University
Within the University, employee, job, or salary data may be disclosed only if it is needed to do the business of the University, and only to those who need to know the information in order to do their jobs. If you are not sure whether to fulfill a request for employee, job, or salary data, call the Division of Human Resources.

Query Results
If you save your query results in Excel, pdf, or any other local file type, you must see to it that any sensitive data stored on your peronal computer is safeguarded through physical security, access control software, or encryption. When a computer is left signed to an account, it is easy for someone to gain unauthorized access. Either sign off from your account before you leave your computer or restrict access by some other means (physical security or access control software).

For more information on security and privacy, contact the Office of Information Security.

Overview. Salary Management data is complex, and a listserv and adhoc meetings are available to help you query the Warehouse and retrieve Salary Management data efficiently and effectively.

Whenever possible, canned queries and reports are provided to give you standardized perspectives on Warehouse data. Before creating your own query, check to see if a canned query or report already exists for the information you want.

Subscription to the listserv. When you are granted access to the Salary Management data in the Warehouse, a subscription is automatically set up for you to participate in an E-mail listserv called SALMGT-WH@LISTS.UPENN.EDU. (Those with subscriptions to the list are called list members. If any member sends an E-mail note to the list, every member of the list receives a copy.)

When you have a question. If you have a question about the Salary Management data, please send E-mail to the list -- and if you have the answer to a list member's question, please E-mail your reply to the list so all the members can benefit from the discussion.

The list is also the place to send E-mail if you notice that some documentation needs updating. For documentation published on the Web, please specify the URL for the documentation that needs to be updated. For BusinessObjects documentation (help for Objects), please specify the Universe, Class, and Object.

Notification about general and special meetings. From time to time, notes are sent to the list to invite list members to general meetings to discuss the Salary Management data in the Warehouse, or to special interest group meetings to discuss specific issues, such as joining payment and account balance data.