Importing Internal Source Data to Data Warehouse Tables

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

• Review the delivered Extract, Transform, and Load (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 Operational Warehouse - Enriched (OWE) tables.

• View and edit data in dimension and reference tables.

Understanding Internal Source Data

Internal source data is the data from your organization's Human Resources Management System (HRMS) system, or other enterprise management systems, that you use to populate the Operational Warehouse - Enriched (OWE) tables. You first import the data from the HRMS system into the Operational Warehouse Staging (OWS) tables using the IBM WebSphere DataStage tool. This chapter assumes that 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:

1. Begin by evaluating your own internal source tables and planning your approach.

2. Use PeopleTools Application Designer to review the OWS tables and data warehouse tables that are delivered with EPM. Determine how to structure your system to best fit the needs of your organization.

3. Evaluate the ETL jobs that are delivered with the IBM WebSphere DataStage Project and determine whether they are properly configured to meet the needs of your organization.

4. See My Oracle Support for a listing of table-loading sequences for mapping PeopleSoft application data to the OWS tables and data warehouse tables.

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

Setting Up Trees

This section provides an overview of Workforce Analytics trees and discusses how to:

• Review trees used in Workforce Analytics.

• 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 Performance Management (EPM). 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 drilldown and roll-up through dimension data. The system also uses these trees to control and organize processing rules.

Warning! PeopleTools does not require you to use levels when creating trees. However, in EPM, 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 discussed 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.

The following table defines some of the terms used in a subsequent table to summarize the main trees in Workforce Analytics.

Reviewing Trees Used in Workforce Analytics

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

Note. Before you begin building your trees, you can load the dimension data needed to build the trees. You can load the remainder of the dimension data later. Before you load any dimension data into your system, Oracle strongly recommends that 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 that you choose, you may need to flatten the PeopleSoft dimension trees before processing them. Consult your system's 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 drilldown.

It is a standard tree, meaning that it has leaves, or details. You can expand the number of levels of nodes and add nodes, and you can 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 that 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.

See Also

Mapping Compensation Codes and Plan Values

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 that Oracle delivers (tree structure WA_COMPETENCY, tree name WP_COMPETENCY) as an example. The nodes are competencies and no details are available. Note that 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 that 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 used primarily 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 that 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) that you want them to map to. Then use the Geography page to create the Geography ID codes used for the nodes of the tree.

After 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 to 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 used primarily in the Workforce Rewards Market Compensation module to deal with importing external survey data. You use industry codes to define the industry that 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 that are 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 used primarily 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 that is more appropriate for your industry.

Defining Which Trees the System Uses

After 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 for JOB_F00

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 that you want to use in EPM. Then determine whether they should map to one of the workforce termination values: Voluntary, Involuntary, or Not a Termination.

Also review your HRMS source table action codes and determine which action types 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 (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Action Type).

Note. No ETL job is delivered 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.

Mapping Action and Reason Combinations

Access the Action Reason Map setup page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Action Reason Map).

Note. No ETL job is delivered for the WA_ACTN_RSN_DFN table. You map action and reason combinations online prior to loading the OWE table.

Reviewing Your Personnel Action Mappings

Access the Termination Reason Map report page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, Action Reason Map Report).

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 generate the Action Reason Voluntary Flag report (WAC0002) to review your personnel action mappings.

See Also

Workforce Analytics Reports

Loading Your Personnel Action Data

After 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 a mapping exists, 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 no reason code mapping is 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.

EPM 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 geography ID mappings.

Warning! For best results, locations should be mapped to unique geographies. If you have employees who 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 that the Workforce Data Mart logic works correctly. Note that the geography dimension of the Workforce Data Mart is tree-based, so if you want to aggregate employee data from multiple locations, you can view the Workforce Data Mart at a higher, more aggregated level of the Geography dimension (that is, at a higher node in the Geography tree).

Pages Used to Map Locations to Geography ID Codes

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 in which 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 (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Setup Geography ID Map).

Location Code

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

Reviewing Your Location and Geography ID Mappings

Access the Geography ID Map report page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Common, Geography ID Map Report).

After you have completed the Geography ID Map setup page, or after you have loaded your source location data into the data warehouse tables, you can generate 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

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 Plan Values

Reviewing Source Table Earnings, Deductions, and Benefits

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

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 to import into EPM. Each earnings code, deduction code, benefit plan, and PIN code that you want to import into EPM 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 whether 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 EPM to track the dental plan costs and to whom these costs apply.

Entering Compensation Codes and Creating a COMPCODE Tree

When you have a plan for mapping your compensation codes from the HRMS source tables to EPM, you create your compensation codes on the Compensation Code page and 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 are supplied 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:

Mapping Deductions and Benefit Plans to Compensation Codes

Access the Compensation Deduction Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation Deduction Map).

Plan Type

When you access 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 plan types, other than General Deduction, that are summarized in the following table:

WA Compensation Code

See Also

Mapping Compensation Codes and Plan Values

Mapping Earnings to Compensation Codes

Access the Compensation Earnings Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation Earnings Map).

Earnings Code

When you access 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

Mapping Pay Item Names to Compensation Codes

Access the Compensation GP Pin Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Compensation, Compensation GP Pin Map).

Pay Element Types

WA PIN Code

Entering Benefit Plan Values

Access the Benefits Valuation Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Benefits Valuation Map).

Warning! Whenever you enter data in this page, ensure that you aren't creating redundant data. Use this page only 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 that is available from payroll data.

Reviewing Benefits Valuation Maps

Access the Benefits Valuation Map report page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Benefits, Benefits Valuation Map report page).

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

See Also

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:

Loading OWE Tables

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

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

See My Oracle Support for the most complete, up-to-date documentation of the data warehouse tables. Where a discrepancy exists, the report delivered on My Oracle Support takes precedence.

See My Oracle Support for suggested table loading sequences.

After you've completed the ETL process, you must run the Currency Conversion job if, and only if, the data that 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.

Note. Only some, not all, of the fields in the HRMS tables are used in the data warehouse tables. If an apparent discrepancy exists between the information on the previous chart and on My Oracle Support, the information in My Oracle Support takes precedence.

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.

This list shows the main competency-related dimension tables 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 who 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, assume that 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 that 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. WA_VOLUNTARY_FLAG is a Workforce Analytics field that 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 verifies that only valid values are supplied for REVIEW_RATING by comparing them with values in the lookup table WA_REVWRTG_R00. If a corresponding value isn't 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 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 the review and edit 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 that are built into the HRMS system to ensure data integrity are not present in the data warehouse tables. This means that 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.

Several dozen dimension and reference table access pages are 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 chapter 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 View and Edit Data in Dimension and Reference Tables

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 to create row-level group security.

See Also

Setting Up the Organization (Department) Tree

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 organization's 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 Operational Warehouse (OW), the data-loading process annualizes the compensation values. All salary survey data that is 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 that 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 that you are loading the most up-to-date information into the EPM 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 group security is 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 you use the data migration process to load data into PERSONAL_D00, the data does not appear 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 be populated with corresponding rows of data, and then the PF_SECURITY process must be run, before the data appears online.

Note. If your implementation includes row-level security at the employee level, based on JOB_F00 and PERSONAL_D00, then 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 that 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

After 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

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

Remember that in the HRMS system, the salary terms are defined in the following way:

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 table. You load values for the GL_ACCOUNT_TBL table while setting up basic EPM infrastructure and EPM Foundation. This information can be found in the Enterprise Performance Management Fundamentals 9.1 PeopleBook.

See Also

Mapping Compensation Codes and Plan Values

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 that 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

Because 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 to measure your group duration in days, months, or years. This defines the frequency for the values that 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 the person's 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.