Importing Internal Source Data to Data Warehouse Tables

This chapter provides an overview of internal source data and discusses how to:

Review the delivered ETL jobs and data warehouse tables.
Set up trees.
Map personnel actions for JOB_F00.
Map locations to geography ID codes.
Map compensation codes and plan values.
Load data warehouse tables.
View and edit data in dimension and reference tables.

Understanding Internal Source Data

Internal source data is the data from your organization's HRMS system, or other enterprise management systems, that you use to populate the Performance Management Warehouse tables. You first import the data from the HRMS system into the Operational Warehouse Staging (OWS) tables using the Ascential DataStage tool. This chapter assumes you have done this already. Then you use the ETL process to load the data from the OWS into the data warehouse tables (OWE).

Reviewing Delivered ETL Jobs and Data Warehouse Tables

To review the delivered ETL jobs and data warehouse tables:

Begin by evaluating your own internal source tables and planning your approach.
Use PeopleTools Application Designer to review the OWS tables and data warehouse tables delivered with Enterprise Warehouse. Determine how you want to structure your system to best fit the needs of your organization.
Evaluate the ETL jobs delivered with the Ascential DataStage Project and determine if they are properly configured to meet the needs of your organization.
See PeopleSoft Customer Connection for a listing of table loading sequences for mapping PeopleSoft application data to the OWS tables and data warehouse tables.

Warning! Any changes you make to our delivered data warehouse tables have the potential to affect Application Engine processes, and PeopleCode processing throughout the system. Evaluate and execute these types of changes very carefully.

Setting Up Trees

This section provides an overview of trees in workforce analytic applications and discusses how to:

Review trees.
Set up the account tree.
Set up the compensation code tree.
Set up the competency tree.
Set up the financials-related department tree.
Set up the FINCODE tree.
Set up the geography tree.
Set up the organization tree.
Set up the INDUSTRY tree.
Set up the JOBCODE tree.
Set up the position tree.
Set up the UNITCODE tree.
Define which trees the system uses.

Understanding Workforce Analytics Trees

Trees define hierarchical relationships for dimensions in Enterprise Warehouse. Trees add a visual layer to dimensions, helping you see where detail items (such as departments or job codes) fit into your organization's structure. Insight analysis templates in the HCM Warehouse use these hierarchical relationships to facilitate drill-down and roll-up through dimension data. The system also uses these trees to control and organize processing rules.

Warning! PeopleTools does not require the use of levels when creating trees. However, in Enterprise Warehouse, the use of levels is required whenever a tree is referenced by the Insight analysis templates for the HCM Warehouse. Workforce Analytics trees provide a hierarchical roll-up structure for the system dimensions. If you don't use levels within your trees, the analysis templates cannot access the data in the data warehouse tables. For each of the trees we discuss in this section, you must specify the use of levels on the Tree Definition Properties page, and you must define each level. The maximum number of tree levels that you can define for trees used with Insights is 16.

Before we summarize the main trees in Workforce Analytics, here's a refresher on some of the tree terminology used in the table:

Tree Category	This is the first level you see when you open PeopleTools Tree Manager to view the sample trees. The following two categories are especially important for Workforce Analytics: SHARE: This category indicates the tree is used across the entire Enterprise Performance Management product line. This category contains sample trees only, having a setID of SHARE. WA: This category indicates the tree is used primarily within the workforce analytic applications. This category contains sample trees that must be used as templates for the full functionality of Workforce Analytics to work properly. These trees have a setID of MODEL or SHARE. The ones with setID SHARE are sample trees provided as examples. The ones with setID MODEL are templates your tree must look like.
Tree Structure ID	This identification code corresponds to one of the main dimensions in the system. When you set up your trees in your system database, the following tree structure IDs must be used for Workforce Analytics to function properly: JOB_WA01, CMP_WA02, GEO_WA03, IND_WA04, FIN_WA05, UNT_WA06, and ORG_WA07, POS_WA08.
Tree Name	This is the name for an individual tree. You must create your own trees, and you can give them any names you choose.
Sample	This identifies that the tree that is delivered as sample data in your demo database (setID = SHARE or MODEL). Trees with a setID of SHARE are for your use as an example.The one tree with a setID of MODEL is for you to use as a template for creating your tree.

Reviewing Trees Used in Workforce Analytics

The following table lists the trees you may need to set up for the various workforce analytic applications. The last column uses these abbreviations:

PWSC	Workforce Scorecard
PWFI	HCM MDW
PWRW	Workforce Rewards
PWDP	Workforce Planning

Tree Structure ID/ Tree Name/ Dimension Name	Use Sample with SetID of	Tree Category (SHARE or WA)	Workforce Analytics Application
ACCOUNT / WFA_ACCOUNT / WFA Account	SHARE	SHARE	PWSC, PWFI
CMP_WA02 / COMPCODE / Compensation Code	MODEL	WA	PWSC, PWFI, PWRW
WA_COMPTENCY / WP_COMPETENCY / Competency	SHARE	WA	PWDP (optional)
DEPT ID / DEPARTMENT / (Financials-related) Department	SHARE	SHARE	PWSC, PWFI
FIN_WA05 / FINCODE / Financial Size	SHARE	SHARE	PWRW
GE0_WA03 / GEOGRAPHY / Geography	SHARE	SHARE	PWFI, PWRW PWDP (optional)
ORG_WA07 / ORG/DEPT_SECURITY / Organization (or Department)	SHARE	SHARE	PWFI, PWRW PWDP (optional)
IND_WA04 / INDUSTRY / Industry Type	SHARE	SHARE	PWRW
JOB_WA01 / JOBCODE / Job Code	SHARE	SHARE	PWFI, PWRW
POS_WA08 / POSITION / Position	SHARE	SHARE	PWFI
UNT_WA06 / UNITCODE / Unit Size	SHARE	SHARE	PWRW

Note. Before you begin building your trees you have the option of loading the dimension data needed to build the trees. Then you can load the remainder of the dimension data later. Before you load any dimension data into your system, we strongly recommend you read the remainder of this chapter, and the chapter in this book entitled Importing External Survey Data into Data Warehouse Tables.

Note. If your implementation includes HCM MDW, depending on the reporting tool you choose, you may need to flatten the PeopleSoft dimension trees before processing them. Consult with your systems implementation team.

Setting Up the Account Tree

This tree provides structure for the WFA account (Workforce Analytics account) dimension, for general ledger roll-up and drill-down.

It is a standard tree, meaning it has leaves, or details. You can expand the number of levels of nodes and add nodes, and change and add details as needed to suit the revenue and expense account structure for your organization.

Setting Up the Compensation Code Tree

This tree provides structure for the compensation code dimension. When you create your tree you must use a tree structure ID of CMP_WA02. All earnings codes, deduction codes, benefits plan types, and pay item name codes from your source data must map to a compensation code if you plan to include them in your analysis.

It is a winter tree, meaning it has nodes but no details. The Model version of the tree has the minimum number of levels and the necessary nodes required for the system to aggregate your compensation data properly. The delivered nodes must be used for data to be correctly interpreted by the system.

You can add lower levels of nodes, and add nodes to the existing levels, as needed to suit the needs of your organization. To add nodes you use the Compensation Code page (COMPCODE_D00).

On the COMPCODE tree all regular base pay plans should point to or roll up into the tree node 600 Regular Base Compensation. Overtime earnings codes should point to or roll up into the 610 Premium Base Compensation tree node. Similarly, all health benefit plans should point to or roll up into the 550 Medical tree node, under the 450 Health & Welfare Benefits tree node.

Later in the setup process you use the compensation mapping pages to map your Earnings Codes and Deduction Codes to these compensation codes.

Setting Up the Competency Tree

This tree provides structure for the competency dimension, that is, you can use it to provide a hierarchical structure for competency groupings. The competency tree is used in Workforce Planning, although its use is optional. If you choose to use a competency tree, then you must use tree structure WA_COMPETENCY. Use the sample tree we deliver (tree structure WA_COMPETENCY, tree name WP_COMPETENCY) as an example. The nodes are competencies and there are no details. It is important that you note this is not exactly the same as the HRMS competency tree, which has nodes that are competency types, and details that are competencies.

Setting Up the Financials-Related Department Tree

This tree provides structure for the financials-related department dimension. The system uses this tree to roll up account data through the accounting relationship for your departments.

It is a standard tree, meaning it has leaves, or details. You can expand the number of levels of nodes and add nodes, and change and add details as needed to suit the financial roll-up structure of the departments in your company.

The details, or leaves, for the DEPARTMENT tree come from the DEPARTMENT_TBL.DEPTID field. Either you import, using ETL, the department IDs that populate the DEPARTMENT_TBL table, or you add or change departments using the Department Table page.

Setting Up the FINCODE Tree

This tree provides a hierarchical structure for the financial size dimension. This tree is primarily used in the Workforce Rewards Market Compensation module, to deal with importing external survey data. You use financial codes to define the relative financial size of an organization, categorize data from external survey sources, and validate comparison to data for your organization. When you create your tree you must use a tree structure ID of FIN_WA05.

It is a winter tree, meaning it has no leaves or details. You can change the labels and range values for the nodes to financial categories appropriate for your industry. Use the WA_FINCODE_D00 page to create the nodes for the tree.

Setting Up the Geography Tree

This tree provides structure for the geography dimension. When you create your tree you must use a tree structure ID of GE0_WA03. All locations (from the location table) for your internal source data must map to, and roll up to, a geography ID (identification) code.

It is a winter tree, meaning it has no leaves or details.

Review all of your locations in your internal source data, and determine the GEOGRAPHY tree nodes (geography ID codes) you want them to map to. Then use the Geography page to create the Geography ID codes used for the nodes of the tree.

Once you've created a GEOGRAPHY tree, map all of your location codes to nodes on this tree. You can import your locations into the LOCATION_D00 table, or add or change locations using the Locations page. Later in the setup process you use the WA_LOCATION_MAP page to map your locations to these geography ID codes.

See Also

Mapping Locations to Geography ID Codes

Setting Up the Organization (Department) Tree

This tree provides structure for the organizationally related department dimension. When you create your tree you must use a tree structure ID of ORG_WA07. In this structure your departments are tied to their organizational reporting hierarchy. The system uses this tree to roll up departmental level data, such as compensation data for employees within departments. A secondary purpose of this tree is that it can provide a reference for setting up row-level employee security.

It is a winter tree, meaning it has no details or leaves. It uses the departments from the department table (DEPARTMENT_TBL) for its nodes.

Note. If your implementation includes row-level security at the employee level, based on JOB_F00 and PERSONAL_D00, then first load the JOB_F00 and PERSONAL_D00 tables before setting up security.

Setting Up the INDUSTRY Tree

This tree provides structure to the industry type dimension. Industry codes are primarily used in the Workforce Rewards Market Compensation module, to deal with importing external survey data. You use industry codes to define the industry an organization is in, categorize data from external survey sources, and validate comparison to data for your organization.

It is a winter tree, meaning it has no leaves or details. When you create your tree you must use a tree structure ID of IND_WA04. Use the WA_INDUSTRY_D00 page to create the nodes for the tree, called industry ID codes. You can change the labels and range values for the nodes to industries more appropriate for your organization.

Setting Up the JOBCODE Tree

This tree provides structure for the job code dimension. When you create your tree you must use a tree structure ID of JOB_WA01. The JOBCODE tree must have at least two levels. The highest level is for all job codes. The next level down is for logical groupings of job codes into tree nodes called job code sets. The leaves of the job code set nodes are the individual job codes.

This is a standard tree, meaning it has details or leaves.

You use the Job Code Set page to create the nodes for the tree. A job code set is a group of related job codes, or any node on the JOBCODE tree. Most nodes are only labels to define relationships. The real meaning is in the details of the tree, which are the job codes grouped together under that node. You either import your job codes to the JOBCODE_D00 table using ETL, or view and edit job codes using the Job Code page.

Setting Up the Position Tree

This tree provides structure for the position dimension. When you create your tree you must use a tree structure ID of POS_WA08. You import position data into the POSITION_D00 table. You can use the POSITION_D00 page to access these positions and use them for the nodes for the tree.

It is a winter tree, meaning it has no details or leaves. It uses the positions from the position table (POSITION_D00) for its nodes.

Setting Up the UNITCODE Tree

This tree provides structure for the unit size dimension. Unit codes are primarily used in the Workforce Rewards Market Compensation module, to deal with importing external survey data. You use unit codes to define the relative size of an organization (using a criteria other than a financial one), categorize data from external survey sources, and validate comparison to data for your organization.

It is a winter tree, meaning it has no leaves or details. When you create your tree you must use a tree structure ID of UNT_WA06. You use the WA_UNITCODE_D00 page to create the nodes for the tree, called unit codes. You can change the labels and range values for the nodes to a unit of measure more appropriate for your industry.

Defining Which Trees the System Uses

Once you have set up trees for Workforce Analytics, use the Workforce Analytics, Workforce Setup, Setup Workforce Trees page to define which versions of the trees the system uses. The page is discussed in detail in the chapter titled Importing External Survey Data to data warehouse tables.

See Also

Setting Up Trees

Mapping Personnel Actions for JOB_F00

To map personnel actions for JOB_F00 use the WA_ACTN_RSN_DFN.GBL component.

This section discusses how to:

Review source table action and reason codes
Define action types
Map action and reason combinations
Review your personnel action mappings
Load your personnel action data

Pages Used to Map Personnel Actions

Page Name	Object Name	Navigation	Usage
Action Type	WA_ACTION_TYPE	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Action Type.	Define personnel action types, and map them to personnel action types from benchmark metrics surveys. The primary purpose of the action type definition is to facilitate roll-up and drill-down for this attribute in the HCM Warehouse Insight analysis templates.
Action Reason Map	WA_ACTN_RSN_DFN	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Action Reason Map.	Map action and reason combinations from your HRMS system to the Voluntary Term(voluntary termination) field in Enterprise Warehouse. You can also map the action and reason combinations to benchmark survey action type codes on using this page. The two primary purposes of these attributes are to facilitate high-level action counts as well as turnover metrics in the HCM Warehouse Insight analysis templates.
Action Reason Map report	RUN_RWA_0002	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, Action Reason Map Report.	Run the Action Reason Voluntary Flag Crystal Report (WAC0002). Use the report output to review your personnel action mappings.

Reviewing Source Table Action and Reason Codes

Review your HRMS source table (PS_ACTN_REASON_TBL) and identify all the valid combinations of action code and reason code you want to use in Enterprise Warehouse. Then determine whether they should map to the workforce termination values Voluntary, Involuntary, or Not a Termination.

Also review your HRMS source table action codes and determine which action types you want to group them into. For example, your human resources organization may already have standard action types, or you may want to use the same action types used by your company's third-party provider of benchmark metric data.

Defining Action Types

Access the Action Type page.

Note. There is no delivered ETL job for the WA_ACTION_TYPE table. You define Action Types online prior to running ETL.

Warning! The Action Type values of HIR (Hire), OTH (Other), PRO (Promotion), SEP (Separation), and XFR (Transfer) are hard-coded in the data mart processing logic.

Action Type	The personnel Action Type code that you entered to access this page is displayed at the top of the page. Once you've created these codes, you can use them on the Action Reason Map page.
Benchmark Action Type	Select a Benchmark Action Type from the list of valid values. You map your action type to a standardized personnel action type used in many benchmark metrics surveys. Valid values are Hire, Other, Promotion, Separation, and Transfer. Other includes all other action types that are not hires, separations, transfers or promotions.

Mapping Action and Reason Combinations

Access the Action Reason Map setup page.

Note. There is no delivered ETL job for the WA_ACTN_RSN_DFN table. You map action and reason combinations online prior to running ETL.

Action Code	To open the page you select an Action Code, which is a translate table value. The valid values are based on the translate table values for the action code field in HRMS.
Reason Code	Indicate a Reason Code, if there is one. Enter only what you consider valid combinations of action and reason from HRMS. For example, your organization may not want to use a combination in which the action is a Separation, and the reason is Null.
Voluntary Term (voluntary termination)	In workforce analytic applications, some of the turnover metrics in the data marts require the ability to define whether an action and reason combination results in a termination, and whether the termination is voluntary or involuntary. Enter the voluntary termination code to which you want to map this action and reason combination. Valid values are from the translate table, and include Involuntary, Voluntary and Not a Termination.
Action Type	Enter the Action Type to which you want to map this action and reason code combination. Prompt values are from the WA_ACTION_TYPE table, which you created using the Action Types setup page.

Reviewing Your Personnel Action Mappings

Access the Termination Reason Map report page.

At any point after you have completed the Action Type and Action Reason Map setup pages, or after you have imported your source personnel action data, you can run the Action Reason Voluntary Flag report (WAC0002) to review your personnel action mappings.

See Also

Appendix A: Workforce Analytics Reports

Loading Your Personnel Action Data

Once you've completed the two mapping pages, use ETL to import your personnel action data into the data warehouse tables. For each incoming row of employee data the process validates the reason code against the WA_ACTN_RSN_DFN table. If there is a mapping then the system places the appropriate value (voluntary, involuntary, or not a termination), along with the row of data into the Job Data (JOB_F00) table. If there is no reason code mapping on the WA_ACTN_RSN_DFN table, then the data goes to the error table.

Mapping Locations to Geography ID Codes

To map locations to geography ID codes, use the WA_LOCN_MAP_DFN.GBL component.

Enterprise Performance Management Warehouse uses a hierarchical structure for the geography dimension based on geography ID codes and the geography tree. Locations roll up into geography ID codes for geographical regions.

This section discusses how to:

Review and load source table location codes
Enter geography IDs and create a geography tree
Map locations to geography IDs
Review your location and geograpy ID mappings

Warning! For best results, locations should be mapped to unique geographies. If you have employees that may work simultaneously in two different jobs within the same department but in different locations, then you must uniquely map locations to geographies to ensure the Workforce Data Mart logic works correctly. Note that the geography dimension of the Workforce Data Mart is tree-based, so that if you desire to aggregate employee data from multiple locations, you can view the Workforce Data Mart at a higher, more aggregated level of the Geography dimension (i.e. at a higher node in the Geography tree).

Pages Used to Map Locations to Geography IDs

Page Name	Object Name	Navigation	Usage
Geography ID Map	WA_LOCN_MAP_DFN	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Setup Geography ID Map	Map location codes to Geography IDs. These mappings are stored in the WA_LOCN_MAP_DFN table.
Geography ID Map report	RUN_RWA_0006	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Geography ID Map Report	Run the Geography ID/Location Mapping Crystal report (WAC0006).

Reviewing and Loading Source Table Location Codes

Review your HRMS location table. In Workforce Analytics each location must roll up to a geographical region on the geography tree. For each of your locations determine the type of geographical regions to which you want to map them.

For the first time setup, either load your HRMS locations to the LOCATION_D00 table without mapping, or manually enter the locations using the LOCATION_D00 page.

Entering Geography IDs and Creating a Geography Tree

Create a GEOGRAPHY tree where the nodes of the tree are geography IDs. Enter the nodes of the GEOGRAPHY tree using the Geography page. The values are stored in the GEOGRAPHY_D00 table.

See Also

Setting Up the Geography Tree

Mapping Locations to Geography IDs

Access the Geography ID Map setup page.

Location Code

The City, County, State, Postal Code, and Country for this Location Code display from the LOCATION_D00 table. Locations in different countries may display different address fields depending on the formatting you previously defined in the Country Table, Address Format page.

Geography ID

Since Geography ID is not a part of the HRMS location table (PS_LOCATION_TBL), you must map each location to a geography ID. Select the Geography ID (identification) code to which you want to map this location code. Valid values prompt from the GEOGRAPHY_VW table, and represent nodes on the GEOGRAPHY tree. You create geography IDs using the Geography page. Your mappings are stored in the WA_LOCN_MAP_DFN table.

Reviewing Your Location and Geography ID Mappings

Access the Geography ID Map report page.

After you have completed the Geography ID Map setup page, or after you loaded your source location data into the data warehouse tables, you can run the Geography ID/Location Mapping Crystal report (WAC0006), to review your mappings.

When you use ETL to load location codes, the system checks the WA_LOCN_MAP_DFN table for mapping to a geography ID. Locations, and corresponding geography IDs, are then stored in the LOCATION_D00 table.

See Also

Appendix A: Workforce Analytics Reports

Mapping Compensation Codes and Plan Values

To map compensation codes and plan values use the WA_COMP_ERN_MAP.GBL, WA_COMP_PIN_MAP.GBL, WA_COMP_DED_MAP.GBL, and WA_BEN_VALU_TBL.GBL components.

This section discusses how to:

Review source table earnings, deductions and benefits
Enter compensation codes and create a COMPCODE tree
Load source table dependencies
Map deductions and benefit plans to compensation codes
Map earnings to compensation codes
Map pay item names to compensation codes
Enter benefit plan values
Review benefits valuation maps
Load compensation data

Pages Used to Map Compensation Codes and Values

Page Name	Object Name	Navigation	Usage
Compensation Deduction Map	WA_COMP_DED_MAP	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation Deduction Map	Map deduction codes and benefits plans from the HRMS system to compensation codes in Performance Management Warehouse. Mappings are stored in the WA_COMP_DED_MAP table.
Compensation Earnings Map	WA_COMP_EARN_MAP	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation Earnings Map	Map earnings codes from the HRMS system to compensation codes in Performance Management Warehouse. Mappings are stored in the WA_COMP_EARN_MAP table.
Compensation GP Pin Map	WA_COMP_PIN_MAP	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation GP Pin Map	Map PIN (pay item name) codes from PeopleSoft Global Payroll to compensation codes in the Enterprise Performance Management Warehouse. Mappings are stored in the WA_COMP_PIN_MAP table.
Benefits Valuation Map	WA_BEN_VALU_TBL	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Benefits Valuation Map	Assign a monetary amount to a benefit, and to distinguish whether the amount is an employee cost, employee value, or employer's cost. The benefits amounts and values you enter using this page are stored on the WA_BEN_VALUE_TBL table. Use this page for compensation codes that you have already set up in the COMPCODE tree and mapped in the Comp Deduction Code Map (HR) page.
Benefits Valuation Map	RUN_RWA_0003	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Benefits Valuation Map report page	Run the Benefit Value Table Crystal report (WAC0003).

Reviewing Source Table Earnings, Deductions and Benefits

To load compensation data the system uses the following HRMS source tables:

Compensation Type	HRMS Source Tables
Deductions	PS_PAY_DEDUCTION, PS_PAY_CHECK, and PS_TOTAL_DED.
Benefits	PS_DEPENDENT_BENEF, PS_COVRG_CD, PS_HEALTH_BENEFIT, PS_LIFE_ADD_BEN, PS_DISABILITY_BEN, PS_FSA_BENEFIT, PS_SAVINGS_PLAN, PS_VACATION_BEN, PS_LEAVE_PLAN, PS_CAR_PLAN, PS_RTRMNT_PLAN, and PS_PENSION_PLAN.
Earnings	PS_PAY_EARNINGS, PS_PAY_CHECK, and PS_PAY_OTH_EARNS
Pay Item Names	PS_GP_RSLT_ERN_DED, and PS_GP_PIN

Generate a list of the HRMS deduction and earnings codes, benefits plans and plan types, and pay item names (PINs). Use this list to determine which ones you want to import into Enterprise Warehouse. Each earnings code, deduction code, benefit plan and PIN code that you want to import into Enterprise Warehouse must map to one compensation code in the target system. Multiple codes and plan types can map to the same compensation code, or each one can have a separate compensation code.

Also determine if any of these deductions, earnings or benefit plan types normally don't have cost or value data stored in the HRMS system. Most commonly this occurs for benefit plan-related information. For those items in need of a cost or value, first determine the cost or value, and then also determine whether it applies to the employee, the employer, or both. For example, assume that your company allows employees to enroll in a dental plan, and neither the employee nor the employer costs or values of the dental plan are tracked through payroll. In this situation you may want to set up data in Enterprise Warehouse to track the dental plan costs and to whom these costs apply.

Entering Compensation Codes and Creating a COMPCODE Tree

Once you have a plan for mapping your compensation codes from the HRMS source tables to Enterprise Warehouse, your next steps are to create your compensation codes on the Compensation Code page, and to create the COMPCODE tree.

See Also

Setting Up the Compensation Code Tree

Loading Source Table Dependencies

On the compensation and benefits mapping pages certain fields prompt from OWS tables. You populate the OWS tables by ETL prior to beginning work on setting up Workforce Analytics. The following table summarizes the prompts for fields on the compensation and benefits mapping pages:

Compensation or Benefit Mapping Page	OWS Prompt Tables
Compensation Deduction Map	Fields for deductions prompt to WA_PLAN_DED_VW and WA_PLAN_TYP_VW. These views in turn look at DEDUCTION_TBL and BENEF_PLAN_TBL.
Compensation Earnings Map	Field for earnings prompt directly to EARNINGS_TBL.
Compensation GP Pin Map	Fields for pay item names prompt to the GP_PIN_TYPE table and WA_PLAN_PIN_VW table. These views in turn look to the GP_PIN table.

Mapping Deductions and Benefit Plans to Compensation Codes

Access the Compensation Deduction Map page.

Plan Type

When you open the page you are prompted to specify a Plan Type.Valid values are from the translate table. If this mapping is for a general deduction select General Deduction.

Mapping Benefit Plan Types

To map a benefit, select from available values other than General Deduction, which are summarized in the following table:

Plan Type	Description	Plan Type	Description
401(k)	401(k) Retirement Account	PERS	Public Employees Retirement System
AD/D	Accidental Death or Dismemberment Insurance	Personal	Personal Leave
Benefits Program	Benefits Program	Profit Sharing	Profit Sharing
Capital Accumulation	Capital Accumulation	Retirement Counseling Canada	Canadian Retirement Counseling
Company Car	Company Car	Short-term Disability	Short-term Disability Insurance
Dental	Dental Insurance	Sick	Sick Leave
Dependent AD/D	Dependent Accidental Death or Dismemberment Insurance	Standard Pension	Standard Pension (Canada)
Dependent Care	Dependent Care Flexible Spending Account	Supplemental AD/D	Supplemental Accidental Death and Dismemberment Insurance
Dependent Life	Dependent Life Insurance	Supplemental Life	Supplemental Life Insurance
Employee Stock Purchase Plan	Employee Stock Purchase Program	Supplemental Pension	Supplemental Pension (Canada)
Family and Medical Leave Act	Family Medical Leave Act	Survivors Income	Survivors Income
General Deduction	General Deductions	Thrift	U.S. federal government employee Thrift Savings Plan
Health Care Can	Canadian Health Care	USDB Pension Plan 1	U.S. Defined Benefit Pension Plan 1
Health Care	Health Care Flexible Spending Account	USDB Pension Plan 2	U.S. Defined Benefit Pension Plan 2
IRA	Individual Retirement Account	USDB Pension Plan 3	U.S. Defined Benefit Pension Plan 3
Life and AD/D	Life Insurance- Accidental Death or Dismemberment Insurance	USDB Pension Plan 4	U.S. Defined Benefit Pension Plan 4
Life	Life Insurance	USDB Pension Plan 5	U.S. Defined Benefit Pension Plan 5
Long-term Disability	Long-term Disability Insurance	USDB Pension Plan 6	U.S. Defined Benefit Pension Plan 6
Major Medical	Major Medical	U.S. Savings Bonds	U.S. Savings Bonds
Medical	Medical	Vacation	Vacation Leave
NQ Dental	Non-Qualified Dental Insurance	Vacation Buy	Vacation Leave Buy
NQ Vision	Non-Qualified Vision Insurance	Vacation Sell	Vacation Leave Sell
Nonelective Contrib	Non-elective contributions to a retirement savings or pension plan.	Visn/Hear	Vision and Hearing Insurance
FEGLI Living Ben	Federal Employees Group Life Insurance Living Benefit	Option A Standard	Option A - Standard Option in the FEGLI program
403(b)	Type of U.S. retirement savings program.	Retirement TSP 1 %	U.S. government retirement plan called Thrifts Savings Plan, where agency contribution is one percent.
Employer Only	Retirement savings program contributions, which are employers only.

WA Compensation Code

Benefit Plan	The Benefit Plancolumn includes those deductions with a plan type other than 00 in the HRMS system, such as Medical. Prompt values are from the WA_PLAN_TYP_VW table. Ten HRMS source tables contain data used to track benefits enrollment and migration status. Note. If both vacation buy and sell values are to be analyzed, you must define them separately.
Deduction Code	The Deduction Code column includes those deductions with a plan type of 00 in the HRMS system, such as Parking Reimbursements or Charitable Contributions. Prompt values are from the %EDITTABLE table.
Coverage Code	A Coverage Code displays when you open the page for a plan type of 10 (Health Benefit, such as Medical). Examples of coverage codes are Employee Only, Employee & Spouse, Employee & Dependents, Family, and so on. Prompt values are from the WA_COVG_CD_R00 table. Tracking coverage codes in your Enterprise Warehouse serves two purposes: You can track health benefit enrollment migration at a lower level than the plan type. For example, instead of using only one compensation code for all coverage levels of medical insurance, you could have several lower level compensation codes for different coverage levels of medical insurance, all of which roll up to a single parent compensation code for the generic category called medical insurance. You can populate health benefits-related compensation data in WA_COMP_HST_F00 table, using the mappings in the WA_BEN_VALU_TBL table, more accurately. This is possible since you are allowing for different coverage costs for the different coverage levels, rather than accepting just one default cost value for all coverage levels. Note. Any new compensation codes you create to accommodate multiple coverage codes per a single plan type must be added to the COMPCODE_D00 table, and to the COMPCODE tree.
Compensation Code	The Compensation Code is a required field in the sense that all of the benefit plans and deductions you want to include in your data warehouse tables must be mapped to a compensation code, which must correspond to a code on the Compensation tree. Prompt values are from the WA_COMP_ACCT_VW table. You need only bring data into the data warehouse tables that supports your planned analyses. Any deduction code or benefits plan type that is not mapped using this page is not be brought into the data warehouse tables. Mappings entered in this page are stored in the WA_COMP_DED_MAP table.
Amount Type	Select an Amount Type from the list of translate table values. Valid values are Employee Expense, Employee Val and Employer Exp (employee value and employer expense), Employee Value, and Employer Expense. This indicates whether the monetary value of the deduction or benefit is an expense or value, to the employee or the employer. This is only the default value for the compensation code, and is overridden if the deduction class on the pay deduction row from HRMS indicates it is either an employer expense or employee expense. It is also overridden if a row is set up on the Workforce Analytics WA_BEN_VALUE_TBL table for this compensation code. The system uses the amount type to determine employee and employer compensation costs, especially in Workforce Rewards.

Mapping Earnings to Compensation Codes

Access the Compensation Earnings Map page.

Earnings Code

When you open the page you are prompted to specify an Earnings Code. Earnings are those values from the ERNCD field on the PS_EARNINGS and PS_PAY_OTHER_EARNS tables from HRMS.

WA Compensation Code

Compensation Code

The Compensation Code is a required field in the sense that all earnings you want to include in your data warehouse tables must be mapped to a compensation code, which must correspond to a code on the Compensation tree. Prompt values are from the WA_COMP_ACCT_VW table. You need only bring data into the data warehouse tables that supports your planned analyses. Any earnings code that is not mapped using this page is not brought into the data warehouse tables. Mappings entered in this page are stored in the WA_COMP_ERN_MAP table.

Amount Type

Select an Amount Type from the list of translate table values. Valid values are Employee Expense, Employee Val and Employer Exp (employee value and employer expense), Employee Value, and Employer Expense. This indicates whether the monetary value of the earnings is an expense or value, to either the employee or the employer. The system uses the amount type to determine employee and employer compensation costs, especially in Workforce Rewards.

Mapping Pay Item Names to Compensation Codes

Access the Compensation GP Pin Map page.

Pay Element Types

Element Type

When you open the page you are prompted to specify a pay Element Type. Pay Elements are from the PeopleSoft Enterprise Global Payroll system GP_PIN table. This is the main record in that application for setting up elements such as Earnings, Deduction, Absence Taken, and so forth.

WA PIN Code

PIN Number	Enter a PIN Number (pay item name code or number). The system displays the related pay item name. This field corresponds to the PeopleSoft Enterprise Global Payroll field PIN_NUM, for pay items within pay element types. For example, pay elements such as Earnings might include pay items such as Salary Plans, Sick Plans, and so forth.
Compensation Code	The Compensation Code is a required field in the sense that all PIN codes you want to include in your data warehouse tables must be mapped to a compensation code, which must correspond to a code on the Compensation tree. Prompt values are from the WA_COMP_ACCT_VW table. You need only bring data into the data warehouse tables that supports your planned analyses. Any PIN code that is not mapped using this page is not brought into the data warehouse tables. Mappings entered in this page are stored in the WA_COMP_PIN_MAP table.
Amount Type	Select an Amount Type from the list of translate table values. Valid values are Employee Expense, Employee Val and Employer Exp (employee value and employer expense), Employee Value, and Employer Expense. This indicates whether the monetary value of the earnings is an expense or value, to either the employee or the employer. The system uses the amount type to determine employee and employer compensation costs, especially in Workforce Rewards.

Entering Benefit Plan Values

Access the Benefits Valuation Map page.

Warning! Whenever you enter data in this page take care to ensure that you aren't creating redundant data. Only use this page to provide a valuation of benefits plans that don't otherwise have an amount and value associated with them in HRMS. That is, use it for a given compensation code when that information is not available through payroll data, or when your source data does not provide the amounts and values that your company wants to track. Do not use this table for expenses, or for duplicating information available from payroll data.

Amount Type

Select an Amount Type from the list of translate table values. Valid values are Employee Expense, Employee Val and Employer Exp (employee value equals employer expense), Employee Value, and Employer Expense.

Currency Code

Use the prompt list values in the Currency Code field to indicate the currency for the benefit value amount. Prompt values are from the currency code table (CURRENCY_CD_TBL).

Annual Rate and Percent of Salary

Enter an amount for the benefit value, either as the Annual Rate or the annual Percent of Salary.

These mappings are stored in the WA_BEN_VALU_TBL table.

Reviewing Benefits Valuation Maps

Access the Benefits Valuation Map report page.

To review the benefits valuation maps entered in the system, run the Benefit Value Table report (WAC0003).

See Also

Appendix A: Workforce Analytics Reports

Loading Compensation Data

Use the ETL process to load compensation data from the OWS staging tables to the data warehouse tables. Here is a summary of what happens when you load compensation data:

Compensation Type	Mapping Tables Referenced	Target Data Warehouse Tables
Deductions	WA_COMP_DED_MAP. Any deductions not mapped are not loaded.	WA_COMP_HST_F00 with rows of employee-level compensation cost and value data.
Benefits	WA_COMP_DED_MAP and WA_BEN_VALU_TBL tables. Any benefit plans not mapped are not loaded.	WA_COMP_HST_F00 with rows of employee-level compensation cost and value data, as annualized costs. WA_BEN_HST_F00 with benefits enrollment data. Note. If, while loading WA_COMP_HST_F00, the system finds an enrollment row for an employee on one of the enrollment tables (Health Benefit, Life Add Benefit, Disability Benefit, FSA, Savings, and so on), then the system looks to the WA_BEN_VALU_TBL table for a corresponding compensation code. If a row exists for mapping that benefit to a compensation code on the WA_BEN_VALU_TBL table, then the mapped values associated with that benefit are inserted for that employee on WA_COMP_HST_F00, with a Compensation Source of B.
Earnings	WA_COMP_ERN_MAP. Any earnings not mapped are not loaded.	WA_COMP_HST_F00 with rows of employee-level compensation cost and value data.
Pay Item Names	WA_COMP_PIN_MAP. Any pay items not mapped are not loaded.	WA_COMP_HST_F00 with rows of employee-level compensation cost and value data.

Loading Data Warehouse Tables (OWE)

This section provides an overview of the data warehouse tables of primary concern to workforce analytic applications and discusses how to:

Load benefit enrollment data.
Load competency data.
Load staffing data.
Load job data.
Load employee compensation history data.

Understanding OWE Tables

You use the ETL process to load internal source data from the OWS staging tables into your OWE tables. The following table provides a brief overview of the OWE tables loaded by the ETL process that are of primary concern to workforce analytic applications. Important points about some of the fact (*_F00) tables are presented both in this table and in this section following the table. Important points about some of the dimension (*_D00) and reference (*_R00) tables are discussed in the next section after that.

This table is not a comprehensive list of all of the data warehouse tables in Enterprise Warehouse. See PeopleSoft Customer Connection for the most complete, up-to-date documentation of the data warehouse tables. Where there is a discrepancy the report delivered on Customer Connection takes precedence.

See PeopleSoft Customer Connection for suggested table loading sequences.

Once you've completed the ETL process, you must run the Currency Conversion job if, and only if, the data you imported with ETL contains currency amounts, which require standardization into a base currency equivalent. Currency Conversion must be run as a standalone job within a jobstream.

OWE Table Name	Source Tables	Usage within Workforce Analytics
ABS_CLASS_D00	ABS_CLASS_TBL
ABS_CODE_D00	ABS_CODE_TBL
ABS_HIST_F00	ABSENCE_HIST
ABS_TYPE_D00	ABS_TYPE_TBL
ABSV_ACCR_F00	ABSV_ACCRUAL
ABSV_ADDL_D00	ABSV_ADDL_TBL
ABSV_PLAN_D00	ABSV_PLAN_TBL
ABSV_RATE_D00	ABSV_RATE_TBL
ABSV_REQ_D00	ABS_REQUEST
ACCOMP_D00	ACCOMP_TBL	Workforce Planning
ACCT_CD_D00	ACCT_CD_TBL	Workforce Rewards
ACMPLISHMNT_F00	ACCOMPLISHMENTS
APP_ACP_D00	APP_ACCOMPS
APP_CMP_D00	APP_COMPS
APP_DIS_D00	APP_DISABILITY
APP_EMAIL_D00	APP_EMAIL_ADDRS
APPLICANT_D00	APPLICANT_DATA PERS_APPL_INF0 APPL_DATA_EFFDT
BUDGET_BU_D00	BUDGET_BUS_UNIT
BUDGET_PER_D00	BUDGET_PERIOD
CAN_ERN_BAL_F00	CAN_ERN_BALANCE
CM_CLUSTER_D00	CM_CLUSTER_TBL
CM_EVALUATN_D00	CM_EVALUATIONS
CM_ROLE_ACC_D00	CM_ROLE_ACCOMPS
CM_ROLE_D00	CM_ROLE
CM_ROLECOMP_D00	CM_ROLE_COMPS
CM_RTNGDSCR_D00	CM_RATING_DESCR
CM_TYPE_D00	CM_TYPE_TBL
CMCLUSTRACP_D00	CM_TYPE_TBL
CMCLUSTRCMP_D00	CM_CLUSTER_COMP
CMPETNCYTYP_D00	COMPETENCY_TYPS
COMPANY_D00	COMPANY_TBL
COMPETENCY_D00	COMPETENCY_TBL	Workforce Planning
COMPRT_GRP2_D00	COMPRT_GRP_TBL2
COURSE_COMP_D00	COURSE_COMPS
COURSE_D00	HR_COURSE_TBL
CRSE_SESSN_D00	CRSE_SESSN_TBL
CRSESESINST_D00	CRSE_SESS_INSTR
CRSESESNCST_D00	CRSE_SESSN_COST
DED_FREQ_D00	DEDUCTION_FREQ
DEPARTMENT_TBL	DEPT_TBL	Workforce Planning Workforce Rewards
DEPT_BUDDED_D00	DEPT_BUDGET_DED
DEPT_BUDERN_D00	DEPT_BUDGET_ERN	Workforce Rewards
DISABILITY_D00	DISABILITY
EARNINGS_BL_F00	EARNINGS_BAL
EMPL_REVW_F00	EP_APPR and EP_APPR_ROLE	Workforce Rewards
EMPLOYEES_D00	EMPLOYEES
EMPLOYMENT_D00	EMPLOYMENT
ESTAB_D00	ESTAB_TBL
JOB_EARNDST_D00	JOB_EARNS_DIST	Workforce Rewards
JOB_F00	JOB	Workforce Planning Workforce Rewards Workforce Scorecard
JOB_INTRADY_F00	JOB
JOB_REQ_F00	JOB_REQUISITION
JOB_TASK_D00	JOB_TASK_TBL
JOBCD_CMPRT_D00	JOBCD_COMP_RATE
JOBCD_TRNPR_D00	JOBCD_TRN_PROG	Workforce Rewards
JOBCD_TSKDF_D00	JOBCD_TASK_DEFN
JOBCODE_D00	JOBCODE_TBL	Workforce Rewards Workforce Scorecard Workforce Planning
LEAVE_PLAN_D00	LEAVE_PLAN
LOCATION_D00	LOCATION_TBL	Workforce Planning Workforce Rewards
NID_TYPE_D00	NID_TYPE_TBL
PERS_DETAIL_D00	PERSONAL_PHONE
PERS_NAMES_D00	NAMES
PERS_NID_D00	PERS_NID (child of PERSON) SETID derived from JOB_F00_ETL_VW
PERSONAL_D00	PERSON PERS_DATA_EFFDT NAME ADDRESS CITIZENSHIP PERS_APPL_INF0	Workforce Scorecard Workforce Planning Workforce Rewards
POSITION_D00	POSITION_DATA
POSN_APPLD_F00	POSN_APPLIEDFOR
PRIORWRKEXP_D00	PRIORWORK_EXPER
REVW_FACTOR_D00	REVW_FACTOR_TBL
REVWRWRCMP_F00	EP_APPR and EP_APPR_ITEM
SAL_GRADE_D00	SAL_GRADE_TBL	Workforce Rewards Workforce Scorecard
SAL_MTRXPCT_D00	SAL_MATRIX_PCT
SAL_MTRXTBL_D00	SAL_MATRIX_TBL	Workforce Rewards
SAL_PLAN_R00	SAL_PLAN_TBL	Workforce Rewards Workforce Scorecard
SAL_RATECD_D00	SAL_RATECD_TBL	Workforce Rewards
SAL_STEP_D00	SAL_STEP_TBL	Workforce Rewards
SCHOOL_D00	SCHOOL_TBL
SCHOOL_EDUC_D00	SCHOOL_EDUCAT
SCHOOL_TYPE_D00	SCHOOL_TYPE_TBL
ST_GRANT_F00	ST_GRANT
STAFFING_F00	JOB_REQUISITION or JOB and POSITION_DATA	Workforce Scorecard Workforce Rewards
STOCK_PLAN_D00	STOCK_PLAN_TBL	Workforce Rewards
TIME_LABOR_F00	TL_PAYABLE_TIME and TL_TRC_TBL	Workforce Scorecard
TL_EMPL_D00	TL_EMPL_DATA
TRN_DMND_D00	TRN_DEMAND
TRN_DMNDDPT_D00	TRN_DEMAND_DEPT
VC_EE_VCOMP_D00	VC_EE_VCOMP_DTL
VC_PLAN_D00	VC_PLAN_TBL	Workforce Rewards
VENDOR_PLCY_D00	VENDOR_POLICY
WA_ACMP_MAJ_F00	ACCOMPLISHMENTS	Workforce Planning
WA_ACMPLISH_F00	ACCOMPLISHMENTS	Workforce Planning
WA_BEN_HST_F00	HEALTH_BENEFIT LIFE_ADD_BEN DISABILITY_BEN FSA_BENEFIT SAVINGS_PLAN VACATION_BEN LEAVE_PLAN CAR_PLAN RTRMT_PLAN PENSION_PLAN JOB WA_COMP_DED_MAP
WA_CMPTN_EE_F00	COMPETENCIES NVQ_UNITS_PROG CAREER_STRENGTH CAREER_WEAKNESS EP_APPR EP_APPR_ITEM	Workforce Scorecard
WA_COMP_HST_F00	PAY_EARNINGS PAY_OTH_EARNS PAY_CHECK PAY_DEDUCTION TOTAL_DED GP_RSLT_ERN_DED GP_PYE_PRC_STAT HEALTH_BENEFIT LIFE_ADD_BEN DISABILITY_BEN FSA_BENEFIT SAVINGS_PLAN VACATION_BEN LEAVE_PLAN CAR_PLAN RTRMNT_PLAN PENSION_PLAN WA_COMP_ERN_MAP WA_COMP_DED_MAP WA_COMP_PIN_MAP WA_BEN_VALU_TBL	Workforce Scorecard Workforce Rewards
WA_COMPTNCY_F00	COMPETENCIES COMPETENCY_TBL EP_APPR EP_APPR_ITEM WA_RATING_MAP	Workforce Planning
WA_COVG_CD_R00	COVRG_CD_TBL
WA_DEP_BEN_D00	DEPENDENT_BENEF
WA_INJ_ILL_D00	INJURY_ILL and INCIDENT_DATA	Workforce Scorecard
WA_RECR_EXP_F00	APP_EXPENSES
WA_RECR_OFR_F00	OFFER
WA_REVWRTG_R00	REVW_RATING_TBL	Workforce Scorecard Workforce Rewards
WA_ROLE_D00	CM_ROLE JOBCODE_TBL POSITION_DATA	Workforce Planning
WA_ROLEACMP_D00	CM_ROLE and CM_ROLE_ACCOMPS	Workforce Planning
WA_ROLECMPT_D00	CM_ROLE and CM_ROLE_COMPS	Workforce Planning
WA_ROLETASK_D00	CM_ROLE and CM_ROLE_COMPS	Workforce Planning
WA_RTGMDL_R00	RATING_MDL_TBL	Workforce Rewards
WA_STD_LTR_R00	STANDRD_LTR_TBL
WA_TASK_D00	CM_CLUSTER_TBL CM_ROLE JOB_FAMILY_TBL JOB_TASK_TBL	Workforce Planning
WA_TASKACMP_D00	CM_CLUSTER_ACMP CM_CLUSTER_TBL CM_ROLE_ACCOMPS	Workforce Planning
WA_TASKCMPT_D00	CM_ROLE_COMPS and CM_CLUSTER_TBL	Workforce Planning
WA_TRN_HST_D00	TRAINING
WA_TRNCST_F00	TRN_EEEQUIP_CST TRN_EEEXPN_COST TRN_EEFACIL_CST TRN_EEINST_COST TRN_EEPROV_COST TRN_EESAL_COST TRN_SSEXPN_COST JOB_F00_ETL_VW	Workforce Scorecard
WA_TRNSESSN_D00	CRSE_SESSN_COST
WRKS_CNCL_D00	WRKS_CNCL_LCL

Note. Only some, not all, of the fields in the HRMS tables are used in the data warehouse tables. If there is an apparent discrepancy between the information on the previous chart and on Customer Connection, the information in Customer Connection takes precedence.

Loading Benefits Enrollment Data

In the Benefits Enrollment History table (WA_BEN_HST_F00) there is a field called the WA Benefit Enrollment Source (WA_BENSOURCE). This field is an identifier, populated during data loading, that indicates the HRMS source table the benefits enrollment data is from. There are ten possible valid values and corresponding tables:

WA Benefit Enrollment Source	HRMS Source Table
Health Benefits	PS_HEALTH_BENEFIT
Pension Plan	PS_PENSION_PLAN
Life ADD Ben	PS_LIFE_ADD_BEN
Disability Ben	PS_DISABILITY_BEN
FSA Benefit	PS_FSA_BENEFIT
Savings Plan	PS_SAVINGS_PLAN
Vacation Benefit	PS_VACATION_BEN
Leave Plan	PS_LEAVE_PLAN
Car Plan	PS_CAR_PLAN
Retirement Plan	PS_RTRMNT_PLAN

Loading Competency Data

The WA_CMPTN_EE_F00 table records employee-level competency data, and it is used in HCM Warehouse and Workforce Scorecard. In this table the competency source field (WA_CMSOURCE) identifies the source of the competency data for an employee. The field is populated during data loading. The sources are the HRMS modules that use competencies: Plan Careers, Manage Competencies, and Performance Management. Valid values are Career Strength, Career Weakness, Competencies, NVQ Units Progression, and Performance Management.

The WA_CMPTNCY_F00 table records competency ratings for each employee, applicant or contractor evaluation, and it is used in Workforce Planning. The sources are the HRMS modules: Manage Competencies and Performance Management. (Employee Appraisals). Only the Official, Approved rating from the employee review is selected.

The following are the main competency related dimension and fact data warehouse tables used by Workforce Planning:

WA_CMPTNCY_F00
WA_ACMPLISH_F00
WA_ACMP_MAJ_F00
WA_TASK_D00
WA_TASKCMPT_D00
WA_TASK_ACMP_D00
WA_ROLE_D00
WA_ROLECMPT_D00
WA_ROLEACMP_D00
WA_ROLE_TASK_D00

Loading Staffing Data

During data loading the system imports data for the Staffing table (STAFFING_F00) from two HRMS tables. When the data comes from the JOB_REQUISITION table, the value for the EPM field REQ_NEW_POS is copied over directly, as Y (yes) or N (no). When the data is imported from the POSITION_DATA table the EPM field REQ_NEW_POS value is derived using the following mapping logic:

If the HRMS table POSITION_DATA.ACTION_REASON field value is NEW, then the value for the EPM field REQ_NEW_POS is set to Y. Otherwise, the value for REQ_NEW_POS is set to N.

Loading Job Data

Before you use ETL to load the OWE table JOB_F00, you must have completed setup of online pages for the following tables:

CURRENCY_CD_TBL
FREQUENCY_TBL
SETID_TBL
WA_LOCN_MAP_DFN
WA_ACTN_RSN_DFN
BUS_UNIT_TBL_PF
BUS_UNIT_SRC_PF

During data loading the system creates an additional row in JOB_F00 for employees that have a more recent row in JOB_F00 with another business unit, to indicate that the employee is no longer active for the first business unit. For example, if the system imports two rows of data for an employee with the following business units, effective dates, and effective statuses:

CORP1, 1/1999, Active CORP2, 2/2000, Active

then the system will insert a third row of data into JOB_F00 to indicate the employee is no longer active for the first business unit:

CORP1, 2/2000, Inactive

Setting the WA_VOLUNTARY_FLAG Field

The ACTION, ACTION_REASON, and WA_VOLUNTARY_FLAG fields are in the JOB_F00 table. The WA_VOLUNTARY_FLAG is a Workforce Analytics field you map online prior to data loading. The mapping identifies whether each action and reason combination imported from HRMS results in a voluntary action, involuntary action, or no action.

Importing Effective-Sequenced Job Actions

During data loading the system imports the maximum effective sequence row, for each effective date from the HRMS Job Data table, and places that row in JOB_F00. In contrast, the system loads all effective-sequenced rows from the HRMS Job Data table into JOB_INTRADY_F00.

Importing Review Rating Data to JOB_F00

Normally during data loading the system populates the EPM review rating field (JOB_F00.REVIEW_RATING) with HRMS data. When data is imported into the JOB_F00 table from the HRMS Job Data table, the loading process checks that only valid values are populated for REVIEW_RATING, by comparing them with values in the lookup table WA_REVWRTG_R00. If there isn't a corresponding value on the WA_REVWRTG_R00 table, then some rows of data that you might expect to see for that employee may not be imported. You must verify that all values for a given REVIEW_RATING have a corresponding row on WA_REVWRTG_R00.

Streamlining JOB_F00 and Row-Level Security

If your implementation includes row-level security at the employee level, based on JOB_F00 and PERSONAL_D00, then first load the JOB_F00 and PERSONAL_D00 tables before setting up security.

Loading Employee Compensation History Data

The EPM compensation code field (WA_COMPCODE), on the Compensation Code table (COMPCODE_D00) categorizes types of compensation. These codes are used to relate compensation to nodes on the COMPCODE tree. Prior to data loading earnings, deductions, benefits plans, and pay item types from HRMS tables are mapped to these compensation codes using online pages. During data loading employee compensation history is imported into the employee Compensation History table (WA_COMP_HST_F00).

Viewing and Editing Data in Dimension and Reference Tables

To view and edit data in workforce-related dimension and reference tables, use the GEOGRAPHY_D00.GBL, WA_JOBCDSET_D00.GBL, WA_UNITCODE_D00.GBL, WA_INDUSTRY_D00.GBL, WA_FINCODE_D00.GBL, and COMPCODE_D00.GBL components.

This section provides an overview of dimension and reference data and discusses how to:

View and edit departments.
Set up geography IDs.
View and edit job codes and job code sets.
View and edit personal information.
View and edit position data.
View and edit location data.
View and edit salary grades and steps.
Set up compensation codes.
Set up industry codes.
Set up financial codes.
Set up unit codes.
Define age, service, and other duration groups.

Understanding Review and Edit of Dimension and Reference Data

You can view and edit the data in certain data warehouse dimension and reference tables, at any time, using the pages in the OW-E Dimension Maintenance, HRMS menu. The main point to remember about these pages is that their intended purpose is for you to monitor and perform slight edits on the data in the data warehouse tables. Do not use them in the traditional, transactional sense to manually add rows of data to the system. Many of the edits and checks built into the HRMS system to ensure data integrity are not present in the data warehouse tables. This means you could create data that would not be valid in the source system. The vast majority of the data in your data warehouse tables should be imported using ETL.

There are several dozen dimension and reference table access pages in the OW-E Dimension Maintenance, HRMS menu. The fields on these pages and in the underlying tables are mostly a mirror representation of, or a selection of, those in the HRMS system. The pages themselves are fairly simple setup pages. For these reasons this documentation does not address these pages in any great detail, except to make note of any important differences in how they are applied in Workforce Analytics.

Pages Used to Access Dimension and Reference Data

Page Name	Object Name	Navigation	Usage
Department	DEPARTMENT_TBL	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, Common, Department	View and edit departments, which can be used for financial related accounting purposes, or to provide an organizational hierarchy for your company.
Geography	GEOGRAPHY_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, Common, Geography	Create geography identification codes, which are geographical regions, which your locations roll up to.
Employment	EMPLOYMENT_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Employment	View and edit an employee's employment related milestones and dates.
Job Code	JOBCODE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Job Code	View and edit data in JOBCODE_D00, and provide the details for the JOBCODE tree.
Job Code Set	WA_JOBCDSET_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Job Code Set	Create the nodes that define the structure of your JOBCODE tree. You must enter this information for each node using this page. A job code set is a collection of related job codes.
Job Earnings Distribution	JOB_EARNDST_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Job Earnings Distribution	View and edit how an employee's earnings should be posted for accounting purposes. For example, earnings can be posted to a job code, a department, a location, a general ledger account, a position, or distributed among several categories.
Job Tasks	JOB_TASK_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Job Tasks	View and edit job codes for which there are sets of job tasks.
Personal Data	PERSONAL_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Personal Data	View and edit personal information about your employees in the PERSONAL_D00 table. Rows from the HRMS tables PS_PERSONAL_DATA, PS_EMPLOYMENT and PS_EMPLOYEES are intended to be brought in to the PERSONAL_D00 table with ETL.
Dependent Data	WA_DEP_BEN_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Dependent Data	View and edit information about dependents and beneficiaries.
Position	POSITION_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Position	View and edit position data, if Position Management is implemented in the source HRMS system.
Salary Grade Data	WA_SAL_GRADE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Salary, Grade	View and edit a salary grade structure for your salary administration plan.
Salary Increase Matrix	SAL_MTRXTBL_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Salary, Increase Matrix	View and edit the identifier for each set of salary increase guidelines, the name of the matrix, and what rating scale is used for the ranges.
Salary Matrix Percent	SAL_MTRXPCT_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Salary, Matrix Percent	View and edit the acceptable percentage ranges for salary increases.
Salary Rate Code	SAL_RATECD_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Salary, Rate Code	View and edit compensation rate code information.
Variable Comp Plan (variable compensation plan)	VC_PLAN_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Variable Comp Plan	View and edit data for a variable compensation plan that is not likely to change over the life of the plan.
Stock Plan	STOCK_PLAN_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Stock Plan	View and edit stock plan and type information.
Training History	WA_TRN_HST_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Training, Training History	View and edit employee training history along with related course cost information.
Training Session	WA_TRNSESSN_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Training, Training Session	View and edit training session expenses.
Training Course 1	COURSE_1_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Training, Courses	View and edit training course information.
Training Course 2	COURSE_2_D00	Select the Course 2 tab.	View and edit additional training course details.
Course Session	CRSE_SESSION_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Training, Course Session	View and edit session information for training courses.
Training Program Job Code	JOBCD_TRNPR_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Training, Training Program Job Codes	View and edit information about training program assignments for jobs in your organization.
Course Goal Competency	COURSE_COMP_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Training, Course Goal-Competency	View and edit the competencies, and their associated proficiency levels that are associated with a particular training course.
Account Codes	ACCT_CD_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Account Codes	View and edit the account code information from general ledger and payroll.
Compensation Code	COMPCODE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation Code	Create nodes for the COMPCODE tree. Also use this page to view and edit compensation code data after you have run ETLs to import compensation data from your HRMS system.
Coverage Code	WA_COVG_CD_R00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Coverage Code	View and edit benefits coverage codes.
Financial Code	WA_FINCODE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Financial Code	Create the nodes for the FINCODE tree.
Industry Code	WA_INDUSTRY_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Industry Code	Create the nodes for the INDUSTRY tree.
Unit Code	WA_UNITCODE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Unit Code	Create the nodes for the UNITCODE tree.
School Codes	SCHOOL_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Competencies, School	View and edit codes to schools, colleges, and universities.
School Type	SCHOOL_TYPE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Competencies, School Type	View and edit codes for school types.
Accomplishments	ACCOMP_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Competencies, Accomplishments	View and edit the accomplishments that your employees, applicants or contractors may achieve. Accomplishments are languages, degrees, licenses, certificates, honors, awards, professional memberships, tests, or NVQs (National Vocational Qualifications).
Review Rating	WA_REVWRTG_R00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Salary, Review Rating	View and edit rating models, consisting of review ratings.
Competencies	COMPETENCY_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Competencies, Competencies	View and edit competencies organized by the following general categories: ability, knowledge, skill, salary planning, and NVQ unit.
Competency Type	CM_TYPE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Competencies, Competency Types	View and edit codes for competency types.
Competency Cluster	CM_CLUSTER_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Competencies, Competency Cluster	View and edit codes for competency clusters.
Vacation Plan	ABSV_PLAN_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Vacations/Absences, Vacation Plan	View and edit information about vacation benefit plans for employees in various groups; including the accrual frequency, maximum balance and maximum carryover.
Absence Code	ABS_CODE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Vacations/Absences, Absence Code	View and edit absence codes for your absence types.
Absence Type	ABS_TYPE_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Vacations/Absences, Absence Type	View and edit the type of absences employees can take.
Absence Class	ABS_CLASS_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Vacations/Absences, Absence Class	View and edit the codes for absence classes.
Health and Safety Data	WA_INJ_ILL_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Health and Safety Data	View and edit employee's health and safety incident data.
Applicant Disability	APP_DIS_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Applicants, Applicant Disability	View and edit information on an applicant's disability.
Disability	DISABILITY_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Disability	View and edit information about an employee's disability.
Department Budget Earn Dist (department budget earnings distribution	DEPT_BUDERN_D00	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Department Budget Earn Dist	View and edit the earnings distributions to accounts for each of the specified department budget levels.
Duration Group Definition	WA_DUR_GRP_DFN	EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Duration Group Definition	Define duration groups based upon time duration (such as age, service, job, grade, and so on) which may be used in analysis templates.

Viewing and Editing Departments

Load HRMS departments into the Department table (DEPARTMENT_TBL). Use them to create a financial-related Department tree to provide a hierarchy for departmental accounts. Also use the same departments to create an Organization (Department) tree to provide a hierarchy for all departments in your organization. A secondary purpose of the Organization tree is in creating row-level group security.

Setting Up Geography IDs

The Geography dimension in Workforce Analytics helps provide a hierarchical structure to your organization's locations, using the Geography tree. Prior to loading locations from HRMS, use the Geography page (GEOGRAPHY_D00) to create geographical regions, which logically group together locations in your organization. Map locations to the geographical regions using the Location Mapping page (WA_LOCN_MAP_DFN).

See Also

Mapping Locations to Geography ID Codes

Viewing and Editing Data in Dimension and Reference Tables

Viewing and Editing Job Codes and Job Code Sets

The job code dimension in Workforce Analytics uses the Job Code tree to provide a hierarchical structure to your organizations jobs. Job code sets are the nodes of the tree. They are logical groupings of job codes that you create using the Job Code Set page (WA_JOBCDSET_D00). Job codes provide the details of the tree. Load job codes into the Job Code table (JOBCODE_D00), prior to building the tree.

Compensation Values Associated with Job Codes

If you are using an external market compensation survey to compare your organization's salaries with industry averages, the salary midpoint for the equivalent job code goes in the survey salary field. These are usually annualized values.

Note. When you import market compensation survey data into the Enterprise Warehouse tables, the data loading process annualizes the compensation values. All salary survey data processed in the Define Market Compensation module of Workforce Rewards is standardized to annual values.

See Also

Setting Up the JOBCODE Tree

Viewing and Editing Personal Information

When you have loaded personal data from HRMS into the PERSONAL_D00 table, you can use the pages in the Personal Data component to view and edit the information.

Running Application Engine PER099 Prior to Importing Personal Data

Remember, the PS_EMPLOYEES table is used in HRMS to improve the efficiency of report processing. The table is not updated dynamically by the HRMS system; you must run the Application Engine process PER099 (Refresh Employees Table) to update the PS_EMPLOYEES table. To ensure you are loading the most up-to-date information into the Enterprise Warehouse tables, run the Application Engine process PER099 immediately prior to importing personal data into the OWS staging tables.

Clarifying Row-level Security Issues About Personal Data

The PERSONAL_D00 and JOB_F00 tables are intended to be populated with data using the delivered ETL process. If you enter new rows of employee data manually using the Personal Data page, and there is group security in effect based on JOB_D00_VW (which is a view of JOB_F00) then you may not see this data displayed on pages having row-level security based on employees. Two additional steps are required to view the data. You must first add a corresponding row of data to JOB_F00, and then you must rerun the PF_SECURITY process for security to be enabled.

If, after using the data migration process to load data into PERSONAL_D00, the data does not display in the online pages such as Personal Data, then the problem may have to do with implementation of group (row-level) security based on employees. The search record for PERSONAL_D00 is PERSONAL_SRCH. PERSONAL_SRCH is a join of PERSONAL_D00 and PF_SY_CLASS_OBJ. PF_SY_CLASS_OBJ is populated by running the PF_SECURITY process. If, in your implementation, row-level security is enabled at the employee level, then the security groups are likely built referencing JOB_F00 (or a view of JOB_F00). Both JOB_F00 and PERSONAL_D00 must both be populated with corresponding rows of data, and then the PF_SECURITY process must be run, before the data displays online.

Note. If your implementation includes row-level security at the employee level, based on JOB_F00 and PERSONAL_D00, then first load the JOB_F00 and PERSONAL_D00 tables before setting up security.

Importing Personal Names

During data migration the system populates the personal name field in the PERSONAL_D00 table with the data from the name field in the HRMS Personal Data table. You can view and edit this data using the Personal Data component. During data loading the system populates the personal name field with the name of the individual from the HRMS Names table.

The last name, first name, and middle name fields separate the person's personal name into its components. These fields are also populated by HRMS source fields during data loading.

The last name search and first name search fields are populated from the HRMS source fields during data loading. These are versions of the names which have no punctuation (for example, apostrophes are removed) and are in all capital letters. These versions of the names are used by the HRMS system as search values.

Viewing and Editing Position Data

The POSITION_D00 page and underlying table are provided for those sites that use the Position Management feature of HRMS. Use of this page and table is not necessary if Position Management is not used at your site.

The position dimension in Workforce Analytics uses the Position tree to provide a hierarchical structure for positions within an organization. Position data is loaded into the Position table (POSITION_D00). Position data and job requisition data are aggregated into a single structure, centering on the Staffing table (STAFFING_F00).

See Also

Setting Up the Position Tree

Viewing and Editing Location Data

You import locations into the Location table, and map them to geography IDs. The geography dimension uses the GEOGRAPHY tree to provide a hierarchical structure to your locations and geographical regions.

During initial setup first determine what geographical regions you want to map your HRMS locations to. Enter the geography IDs for those regions using the Geography page. Create a GEOGRAPHY tree using these geography IDs. Then map your locations to the geography IDs using the Location Mapping (WA_LOCN_MAP_DFN) page. If you have done all of this correctly, then during the ETL process, for each location imported to the LOCATION_D00 table, the value from the WA_LOCN_MAP_DFN.GEOGRAPHY_ID field is used to populate the LOCATION_D00.GEOGRAPHY.ID field.

See Also

Mapping Locations to Geography ID Codes

Setting Up the Geography Tree

Viewing and Editing Salary Grades and Steps

Once you have loaded the salary administration plan, salary grade, and salary step information from HRMS into the data warehouse tables, you can use the Salary Grades component to view and edit the grades and steps that make up salary administration plans.

Understanding Salary Value Calculations

Once you have imported salary data then you can review the data, and correct it manually, using these pages. When you change a value using one of the online pages, the Enterprise Warehouse system does not recalculate any of the other related numbers automatically.

Remember, in the HRMS system the salary terms are defined as follows:

Monthly	The annual rate divided by the pay months per year.
Hourly	The monthly rate prorated to 12 pay periods, divided by 52 weeks in the year, divided by 40 hours per week.
Midpoint	The maximum plus the minimum divided by two.

Note. In the Workforce Rewards Define Base Pay Structure module the system defines and calculates midpoints as the maximum plus the minimum divided by two. In your implementation of HRMS you may have chosen to define compensation midpoints differently. If this is the case, then keep this difference in mind when comparing compensation midpoints from your imported data with compensation midpoints created using Workforce Rewards.

Setting Up Compensation Codes

The compensation dimension in Workforce Analytics uses the Compensation Code tree to provide a hierarchical structure to the earnings, deductions, benefits values, and PIN codes loaded from the HRMS system.

You create compensation codes using the Compensation Code page (COMPCODE_D00). Then you map the earnings, deductions, benefits values, and PIN codes from HRMS to these compensation codes using a series of mapping pages.

Compensation Classes

Compensation classes are from the translate table. The valid values correspond to the delivered nodes on the model COMPCODE tree.

Account Field

The account field is used for tracking financial data. Prompt values are from the GL_ACCOUNT_TBL. You load values for the GL_ACCOUNT_TBL during the setup for Enterprise Warehouse.

Setting Up the Compensation Code Tree

Setting Up Industry Codes

The industry code dimension is used primarily in the Workforce Rewards Market Compensation module, to deal with loading external survey data. You use the Industry Dimension page to enter industry codes that define the industry an organization is in. These codes categorize data from external survey sources, and validate comparison to data for your organization. Industry codes are the nodes on the Industry tree.

See Also

Setting Up the INDUSTRY Tree

Setting Up Financial Codes

The financial code dimension is used primarily in the Workforce Rewards Market Compensation module, to deal with loading external survey data. You use the FinCode Dimension page to enter financial codes that define the relative financial size of your organization. These codes categorize data from external survey sources, and validate comparison to data for your organization. Financial codes are the nodes on the FINCODE tree.

The from value and to value fields indicate the value range for the approximate size of an organization's financial worth.

See Also

Setting Up the FINCODE Tree

Setting Up Unit Codes

The unit code dimension is used primarily in the Workforce Rewards Market Compensation module, to deal with loading external survey data. You use the Unit Code page to enter unit codes that define the relative size of an organization (using an alternate criteria other than a financial one). These codes categorize data from external survey sources, and validate comparison to data for your organization. Unit codes are the nodes on the UNITCODE tree.

Note. The purpose of the unit code dimension is to provide an alternate means of measuring the relative size of companies participating in external surveys, as opposed to using the relative financial size of the companies. A typical unit of measure would be the number of employees in a company. The concept of unit is generic enough that the units can be other measures besides number of employees. For example, in the hospital industry the unit could be the number of hospital beds. Or in the hotel industry the unit could be the number of rooms.

The from value and to value fields indicate the value range for the approximate size of an organization based on this unit of measure.

See Also

Setting Up the UNITCODE Tree

Defining Age, Service and Other Duration Groups

Since durations are not part of the HRMS system, you can define them online using the Duration Group page to provide time dimension for analysis templates (WA_DUR_GRP_DFN).

You can define duration groups based on Age, Department, Grade, Job, Pay Change, Promotion, and Service. If the group is Service you can map your service group ID to a benchmark survey service ID.

You can specify whether you want to measure your group duration in Days, Months, or Years. This defines the frequency for the values you enter in the group low value and group high value fields. For example, if you select a frequency of Months for the group type Promotion, then the system measures the duration since a person's last promotion in months. If you select Days then the duration since their last promotion is measured in days, and so on.

The group low value and group high value fields enable you to define the range of values for a duration group. If you create a group ID such as No Service or No Age, enter a value of 0 (zero) in both the group high value and group low value fields.