Mapping Source Payroll Information Using Domains
You can map from source Payroll balances/earnings/deductions/taxes to the Oracle Business Analytics Warehouse Payroll summary measures through the use of domain values.
HR Analytics Payroll model has two fact tables, the detailed fact table and the summary fact table. The detailed fact table has all the balances (in case of EBS Payroll) and Earnings/Deductions/Taxes (In case of PeopleSoft North American and Global Payroll) extracted from the source system on separate rows. This table will be used for detail reports and ad-hoc analysis.
To support analytic reporting, the summary fact table delivers a set of default summary measures. Source payroll balances are mapped to the summary measures and loaded into summary fact table. More than one payroll balance can be mapped to a summary metric, in which case the individual source balances will be summed to form a summary measure.
If, Base Pay constitutes Regular Salary, Total Earnings constitutes Regular Salary and Bonus and Total Tax constitutes Income Tax and Social Insurance Tax.
The source to target balance mapping should be done accordingly as shown below.
-
Regular Salary is mapped to Pay_Base and Total Earnings summary measures.
-
Bonus is mapped to Total Earnings (Total Earnings = Regular Salary + Bonus).
-
Income Tax and Social Insurance Tax are mapped to Total Tax (Total Tax = Income Tax + Social Insurance Tax).
To ensure additive property of measures, we only support run balances. For each payroll run the actual run balances processed are stored. Because we are not breaking these down by context, we can combine run balances across time to form higher level balances, for example, PTD, MTD, and YTD.
If the source balance mapping to the summary measures are not done in the Configuration Manager, the Payroll Summary Fact table will not be loaded with any data and this will cause the reports based on the summary fact table to return no data.
Optional or Mandatory
Mandatory, as the default HR Analytics payroll reports that are based on the Summary Fact table will return no data if the Source balance mapping is not configured.
Task description in detail
The graphic shows the domains used for mapping the source balances to the target summary measures.
Source payroll balances can be mapped to the target summary measures in two ways:
- One to One Mapping (using W_PAY_BALANCE domain).
- Many to One Mapping (using W_PAY_MAP_FACTOR_xxxx domain).
In one-to-one mapping, a source balance is directly mapped to a summary measure in Configuration Manager.
For example: If you have a source balance called Base Pay, you can map it to PAY_BASE summary measure code in the Configuration Manager using the Domain Mappings.
If you have multiple source balances Earns1, Earns2 that constitute a summary measure PAY_BASE, you can map multiple source balances to a single summary measure.
The source balances will be aggregated to populate the summary measure.
PAY_BASE = Earns1 + Earns2
List of delivered Payroll Summary Measures
FLEX_BALANCEx summary measures can be used to map any source balance that does not fit in to the out-of-box summary measures. The flex balance list can be extended as part of any customization.
The following list shows the tab-separated data for Summary Measure Code, Category, and Description for the delivered Payroll Summary Measures.
SUMMARY MEASURE CODE CATEGORY DESCRIPTION BEN_COST_EMPLOYEE Benefits Benefit costs paid by an employee such as employee premium for medical, dental, vision, disability and life insurance. BEN_COST_EMPLOYER Benefits Benefit costs paid by the employer such as employer premium for medical, dental, vision, disabilility, and life insurance, retirement funding and educational assistance, et., Employer-paid benefit cost is a key metric in analyzing employee total compensation and workforce cost. BEN_TAXABLE Benefits Taxable benefits are employer provided "non-cash" taxable compensation or fringe benefits, such as employer-provided vehicles, complementary tickets, and educational assistance, are subject to tax rules. DEDUCTIONS_INVOL Other Deductions Involuntary deducitons are payroll deductions that the employer is mandated by the law to withhold from an employee's paycheck, e.g. income tax witholding, social security taxes, court ordered garnishment such as child support, bankrupcy order, tax levy. DEDUCTIONS_POST_TAX Other Deductions Payroll deductions that are deducted after taxes are withheld. Examples of post tax deductions are union dues, transportation fees, garnishments etc. These deductions do not reduce taxable wages. DEDUCTIONS_PRE_TAX Other Deductions Payroll deductions that are deducted before taxes are withheld. Examples of before tax deductions are health insurance premium, 401K deductions, etc. These deductions reduce taxable wages. DEDUCTIONS_VOL Other Deductions Voluntary deductions are payroll deductions that have been authorized by an employee e.g. retirement saving deduction, health and life insurance premiums, contribution to disability and health saving plans. Some voluntary deductions are before-tax withholdings whereas others are withheld after taxes. FLEX_BALANCE1 Flex Balances Extensible balance field 1 FLEX_BALANCE10 Flex Balances Extensible balance field 10 FLEX_BALANCE11 Flex Balances Extensible balance field 11 FLEX_BALANCE12 Flex Balances Extensible balance field 12 FLEX_BALANCE13 Flex Balances Extensible balance field 13 FLEX_BALANCE14 Flex Balances Extensible balance field 14 FLEX_BALANCE15 Flex Balances Extensible balance field 15 FLEX_BALANCE16 Flex Balances Extensible balance field 16 FLEX_BALANCE17 Flex Balances Extensible balance field 17 FLEX_BALANCE18 Flex Balances Extensible balance field 18 FLEX_BALANCE19 Flex Balances Extensible balance field 19 FLEX_BALANCE2 Flex Balances Extensible balance field 2 FLEX_BALANCE20 Flex Balances Extensible balance field 20 FLEX_BALANCE3 Flex Balances Extensible balance field 3 FLEX_BALANCE4 Flex Balances Extensible balance field 4 FLEX_BALANCE5 Flex Balances Extensible balance field 5 FLEX_BALANCE6 Flex Balances Extensible balance field 6 FLEX_BALANCE7 Flex Balances Extensible balance field 7 FLEX_BALANCE8 Flex Balances Extensible balance field 8 FLEX_BALANCE9 Flex Balances Extensible balance field 9 HEALTHCARE_EMPLOYEE Other Deductions Employee contribution to healthcare insurance premiums including medical, dental and vision plans. HEALTHCARE_EMPLOYER Benefits Employer contribution towards the cost of employee healthcare insurance including medical, dental and vision insurance premium, or other employer-assisted wellness plans. HOLIDAY_HOURS Hours Holiday hours are hours compensated for paid company holidays such as New Year, Christmas, etc. OVERTIME_HOURS Hours Overtime hours paid PAY_BASE Standard Earnings Base salary is the fixed salary or wage paid to an employee based on an employment contract. Base pay does not include variable pay components such as bonus, overtime or sales commission. PAY_BONUS Standard Earnings Bonus pay is they pay compensation over and above the amount of pay specified as a base salary or hourly rate of pay PAY_COMMISSION Standard Earnings The amount of money that an individual receives based on the level of sales he or she has obtained. Sales commission is the amount earned in addition to his/her base salary. PAY_GROSS Standard Earnings Gross amount of remuneration for each pay type including regular pay, overtime pay, allowances, commissions, bonuses, and any other amounts, before any deductions are made. PAY_HOLIDAY Standard Earnings Holiday pay are pay compensated for paid company holidays such as New Year, Christmas, etc. PAY_NET Standard Earnings The remaining amounts of an employee's gross pay after deductions, such as taxes and retirement contributions, are made. PAY_OTHER Standard Earnings Other types of pay that are not base pay, bonus, overtime, commission pay. PAY_OVERTIME Standard Earnings The amount of pay compensated for hours worked beyond an employee's normal working hours and is entitled to overtime premium. PAY_VARIABLE Standard Earnings Variable pay is also known as performance pay, is used to recognise and reward employee performance above and beyond their normal job requirements. Variable pay may include profit sharing, bonuses, holiday bonus, or other forms of cash, and goods and services such as a company-paid trip. PENSION_EMPLOYEE Pension The amount contributed by an employee towards his/her retirement funding such as an employee's contribution to a retirement saving plan PENSION_EMPLOYER Pension The amount contributed by the employer towards an employee's retirement funding such as employer contribution to an employee's retirement saving plan REGULAR_HOURS Hours Hours compensation for an employee's normal working hours based on an employment contract SICK_HOURS Hours An employee sick time that is compensated SICK_PAY Standard Earnings Amount paid for an employee's sick time SOC_INS_EMPLOYEE Other Deductions Social security insurance taxes paid by an employee SOC_INS_EMPLOYER Other Deductions Social security insurance taxes paid by the employer STOCK_VESTED_VAL Benefits The value of an employee's vested stock options TAX_EMPLOYEE Tax Payroll taxes withheld from an employee's pay check such as income taxes, social security and medicate taxes, etc. TAX_EMPLOYER Tax Employer paid taxes are payroll taxes paid by the employer for social security, medicare tax withholding unemployment tax insurance or any other form of employer payroll taxes. Employer-paid tax is a key metric in analyzing employee total compensation and workforce cost. TOTAL_DEDUCTIONS Totals Total before and after tax deductions including benefit deductions, taxes, and other voluntary or involuntary deductions. TOTAL_EARNINGS Totals Total gross pay; this is the grand total of all gross pays on a pay check VACATION_HOURS Hours Total number of hours paid for an employee's vacation time or personal time off. VACATION_PAY Standard Earnings Amount compensated for an employee's vacation time or personal time off
Steps for One-to-One Balance Mapping
The following are the steps to be followed to map the Source Balances to the Warehouse Summary Measures.
- Map the Source balances to the corresponding summary measures.
- Navigate to 'Manage Source Domains' under Domains Administration to check if the source domains are populated.
- Navigate to 'Manage Warehouse Domains' under Domains Administration to verify the target domains (summary measures) are present.
- Navigate to 'Manage Domain Mappings and Hierarchies' for mapping the Source balances to the Summary Measures.
Steps for Many-to-One Balance Mapping
In Many-to-One mapping, you can also derive a summary measure using various balances.
For example, NET_PAY can be derived using a calculation like:
Earns1 + Earns2 – Ded1 – Ded2, Earns1, Earns2, Ded1 and Ded2 being source balances.
For this to achieve, you need to map the above source balances to warehouse domain W_PAY_MAP_FACTOR_PAY_NET.
- Navigate to Manage Warehouse Domains and search for W_PAY_MAP_FACTOR domain code.
You can add the desired multiplier as the Domain Member Code. For example: 1, if you want the balance to be added once or –1 to deduct once.
- Navigate to the Manage Domain Mappings and Hierarchies and search for the domain W_PAY_MAP_FACTOR.
- Select the Net Pay domain mapping and click Edit in the Domain Member Section.
In this screen, you can map the source balances to the balance multiplier. For example: If NET_PAY is calculated as Earns1 + Earns2 – Ded1 – Ded2, then Source Earns1 is mapped to 1, Earns2 is mapped to 1, Ded1 is mapped to –1 and Ded2 is mapped to –1. For each employee, per pay period, the NET_PAY is calculated with the above formula and loaded into PAY_NET column of the Payroll Summary Fact table.
