Configure Data Hub Mappings

Mappings establish relationships between your ADP Workforce Manager data and the Data Hub data model, and allow Data Hub to properly extract, process, and deliver your data. From the Data Hub Configuration Portal, you manage mappings for paycodes, business structure location types, and custom labor drivers.

Paycodes help organize time or money amounts that employees earn. They also identify spans of time for payroll purposes. Paycode mappings are the assignment of paycodes to various pay categories and areas, including Regular, Overtime, Absence, and Other, and are used to predefine calculated metrics based on the paycode values. For example, the Data Hub summary metric "Regular Hours" would aggregate hours for paycodes assigned to the Regular pay category. Other paycode areas include Absence, Paid, Meal Penalty, and Holiday, among others.

Extracted ADP Workforce Manager datasets contain metrics based on timecard and schedule totals data that are filtered based on configurable paycode mappings. For the filtered metrics to return data, paycodes must be aligned, or mapped, since paycodes may have different names.

Note: Only regular paycodes, those which contribute to relevant Data Hub metrics that leverage the mappings, are available in the Configuration Portal. Other types of paycodes, such as combined and cascading, are not relevant.

When Data Hub is first deployed, paycode mappings are bulk loaded into your Data Hub project. The mappings establish the paycode relationship between ADP Workforce Manager and the Data Hub data model in the tenant. Data Hub admins can do this directly in the Configuration Portal. For more information, see Paycode Mapping Bulk Update.

Note: As an alternative, supply the source paycodes and the appropriate mapping values to a ADP Functional Expert in a CSV file. The ADP Functional Expert will then bulk load the file directly into your GCP Data Hub project during implementation.

You monitor and maintain the mappings from Paycode Mapping page in the Data Hub Configuration Portal. Edit mapping values based on your business needs. Assign values when new paycodes are added to the project.

Note: Each paycode must be mapped to a pay category/area before you can run the pipeline.

After your paycodes are bulk loaded into Data Hub, you can update the paycode mapping values as needed and map any new paycodes added to Data Hub. Map paycodes based on how you want hours to be reported.

The following table includes guidelines and best practices for mapping paycodes.

Mapping Options Description
Pay Category Regular, Overtime, Absence, Other
  • Regular – Regular paid hours.
  • Overtime – Paycode where employees have incurred premium pay typically due to defined thresholds, such as > 40 hours in a week.
  • Absence – Non-worked time.
  • Other – Paycodes that do not fall into regular, overtime, or absence categories.

Note: Not all paycodes with premium pay are overtime. For example, employees may get a premium for working on Sunday, but this would be considered regular as the premium is not avoidable.
Absence Category Planned, Unplanned, Extended Leave
  • Planned - Usually includes vacation and PTO where planned.
  • Unplanned – Paycodes for unforeseen absences without advance notice. Examples: sick, no call no show .
  • Extended Leave – Where employees are on extended leave. Examples: FMLA, Military Leave

Productive

 Yes, No
  • Paycodes you consider productive. Typically, worked hours are productive. In a healthcare context, paid hours that contribute to productive FTEs.
Paid Yes, No
  • Paycodes that result in paid wages.
Non-core (exclude hours from paid hours metrics) Yes, No
  • This determines if hours for a paycode are excluded from metrics for paycodes where paid = Yes.
  • This would be set to Yes for unpaid paycodes or where hours would be duplicative. For example, if an employee works an overnight shift and gets 8 hours regular and 8 hours shift differential, the shift differential would be Non-core to prevent double counting the hours.
  • If this column is blank for a paycode, the system will map that paycode as Core.
Training Yes, No  
Holiday Yes, No  
Meal Penalty Yes, No

 
On-Call Yes, No  
Callback Yes, No  
User Defined String

10 user defined fields are available for each paycode. Populate one or more custom fields with any additional information (using a maximum of 50 characters) that can be used to group and filter paycodes.

For example, for Healthcare Productivity, you could add the payroll paycode description, category, category descriptions, sub categories, and sub category descriptions. Then run BigQuery reports on labor metrics that reflect these category groupings.
Excused Absenteeism

 

 

Not used by Data Hub
Unexcused Absenteeism
Low Census
Shift Differentials
Paycode Ignored

The following table shows examples of how to map paycodes.

Typical Paycode Description Pay Category Absence Category Mappings

Regular

 Regular worked hours - multiplier is 1 Regular   Productive, Paid
Overtime Hours > 40 in a week - multiplier is 1.5 Overtime   Productive, Paid
Sick Employee calls in sick - paid Absence Unplanned Paid
PTO Planned time off or vacation - paid Absence Planned Paid
Doubletime Hours for week meet criteria for doubletime - multiplier is 2 Overtime   Productive, Paid
Shift Differential Employee gets an hourly shift premium for hours worked on graveyard shift - multiplier is .5 Regular   Productive, Paid, Non-core
Jury Duty Planned time off for civic duty Absence Planned Paid
Holiday Any planned paid holiday hours Absence Planned Paid, Holiday
Sunday Premium Premium pay for regular hours worked on Sunday - multiplier is 1.5 Regular   Productive, Paid
Unpaid Time Off Planned time off where employee has no PTO hours available Absence Planned Non-core
Timecard Event Tracking Paycode edit generated by the system - not a paid event Other   Non-core
Callback Employee is called back to work when not scheduled. Regular   Productive, Paid, Callback
On Call Employee is designated as available for work Regular   Productive, Paid, On-Call
No Call No Show Employee is scheduled but is absent Absence Unplanned  
Salary Regular Employee is paid to schedule - 40 hours Regular   Productive, Paid
Bereavement Absent due to death in the family Absence Unplanned Paid
  1. Go to the Setup > Paycode Mapping page. All available paycodes for the project display, including columns of categories, showing the current mapping values. A message at the top of the page indicates which paycodes, if any, have no Pay Category values mapped.
  2. Click a cell in a column to add a value or change a paycode category value for the paycode you want to edit. Note the following:
    • Supported values for categories are Yes, No, or (-), except for the following categories:
      • For Pay Category, select Regular, Overtime, Absence, or Other
      • For Absence Category, select Extended Leave, Planned, Unplanned, or (-)
    • 10 user defined fields are available for each paycode. Populate one or more custom fields with any additional information (using a maximum of 50 characters) that can be used to group and filter paycodes. For example, for Healthcare Productivity, you could add the payroll paycode description, category, category descriptions, sub categories, and sub category descriptions. Then run BigQuery reports on labor metrics that reflect these category groupings.
  3. Click Save . The next time pipelines run, the summary metrics will recalculate based on the updated mappings.
  4. To export the paycode mappings to a CSV file, click Export Paycodes. This is useful as a template when mapping paycodes in bulk.
  5. To bulk update the paycode mappings, click Bulk Update. See Paycode Mapping Bulk Update.

Every GCP tenant has a business structure that defines how data is organized. ADP Workforce Manager Location Types (for example, company, department, and job) must be mapped to your data’s business structure location as a hierarchy in the businessStructure table schema in your GCP project. The mapping populates the orgBreak0-25 columns which gives you more insight into the data you want to report on.

This mapping is done in the Data Hub Configuration Portal. When mapping, you assign each Location Type a unique Mapping Assignment ID value. This ID implies organizational priority and assures that each business structure location type is represented in the database (in the orgBreak0-25 columns). You must map location types before you can run meaningful GCP reports against business structure attributes.

There is no requirement for how location types are mapped, but as a best practice it is recommended that you map them in ascending order based on the organizational hierarchy. For example, a retailer location may be mapped as follows:

  • Category = 0
  • Job = 1
  • Department = 2
  • Location = 3
  • District = 4
Company Business Site Division
Regions District

Business Location
Area Department

Subarea
Category Job

 
  1. Go to the Setup > Location Type Mapping page. All available location types for the project display, along with a description and category of each type (if available). A message at the top of the page indicates which location types, if any, are not mapped. By default, when Data Hub is first deployed, the mappings for all Location Types will show a dash (-) indicating no mappings have been done. 
  2. In the Location Type Mapping Assignment column, triple-click a cell for the location type you want to map to open the ID list. A cell with a dash (-) in the Mapping Assignment column means no value has been mapped for that location type.
  3. Select a number from 0 to 25. The number you select should not be used for another location type. If it is, you must change the ID value for the other location type before you can save your work.
  4. When you are finished mapping location types, click Save .

Retail clients use custom labor drivers as part of the labor forecasting process to generate and manage the schedules of an employee’s hours for labor (non-volume) related work. To report on these amounts through Data Hub, the drivers must be mapped to columns in BigQuery.

When you map a custom driver, the mapping assignment value is associated with a equivalent column in the Summary Dataview in BigQuery. The mapping manages the data that populates the column. For example, if you have the custom driver Promotional Hrs assigned a mapping assignment value of 1, then the BigQuery column CstmDriver1_Amt would return Promotional Hrs data.

Note: ADP Workforce Manager custom drivers are loaded into your GCP project during Data Hub deployment.

  1. Go to the Setup > Custom Driver Mapping page. All available custom drivers for the project display, along with the custom driver currency column indicating whether the driver value is expressed in currency. A message at the top of the page indicates which custom drivers, if any, are not mapped. By default, when Data Hub is first deployed, the mappings for all custom drivers will show a dash (-) indicating no mappings have been done. 
  2. In the Custom Driver Mapping Assignment column, triple-click a cell for the custom driver you want to map to open the ID list. A cell with a dash (-) in the Mapping Assignment column means no value has been mapped for that location type.
  3. Select a number from 0 to 25. The number you select should not be used for another custom driver. If it is, you must change the ID value for the other driver before you can save your work. These numbers do not imply organizational hierarchy.
  4. When you are finished mapping custom drivers, click Save .