This chapter provides an overview of external survey data and discusses how to:
Review sources of external survey data.
Review external survey tables and Extract, Transform, and Load (ETL)setup.
View and edit the survey Data Warehouse tables.
Complete survey mapping definitions.
Review survey mapping definitions online.
Generate external survey data reports.
Review the external survey flat file definitions.
External survey data is workforce-related data that you obtain from third-party survey providers (such as market compensation data from compensation surveys). This chapter discusses how to import external survey data from flat files into data warehouse tables using the IBM WebSphere DataStage ETL tool.
Workforce analytic applications are designed to integrate data from three main types of external surveys:
Compensation surveys containing market compensation data, which you use in the modules within Workforce Rewards.
Benchmark surveys containing workforce-related metrics, which you can use with analysis templates in HCM Warehouse.
Employee surveys containing employee satisfaction and exit survey results, which you can use in the rules defined within Workforce Rewards.
Note. You can import, by ETL, external data for Workforce Planning, such as employee competency data, competency dictionaries, skills inventories, standard proficiency ratings, job profiles, and so on. To do this, you must create your own flat files and ETL jobs. Our data model supports this, and you will have access to IBM WebSphere DataStage to create the jobs and run the ETLs.
The external survey data setup and import process is similar for each type of survey data. Oracle delivers the tools to enable you to import external survey data into EPM.
Oracle has worked closely with third-party survey providers to develop the file definitions, ETL jobs, and data warehouse tables that are delivered with Workforce Analytics. Oracle designed the delivered file definitions, ETL jobs, and data warehouse tables to work efficiently and seamlessly together.
This section discusses how to:
Review the data warehouse tables and ETL jobs.
Review the survey file definitions.
Before you begin, take some time to plan your approach and evaluate your external survey data needs.
Use the PeopleTools Application Designer to review the data warehouse tables that are delivered with EPM. Determine how to structure your system to best fit the needs of your organization.
Evaluate the ETL jobs that are delivered and determine whether they are properly configured to meet the needs of your organization.
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.
See Also
Preparing to Load Source Data Into EPM
Prior to ETL processing, you must ensure that your external survey data is in a standard flat file format to ensure detailed and accurate mapping to the data warehouse tables. In Workforce Analytics, Oracle delivers standard file definitions for the following external survey elements:
Author file (common to all types of surveys).
Dimension file (compensation survey and benchmark survey).
Survey Instance file (common to all types of surveys).
Compensation Survey file.
Benchmark Survey file.
Employee Survey file.
The standard file definitions are provided in the tables at the end of this chapter, in the section Understanding the External Survey Flat File Definitions.
To use the ETL jobs that Oracle delivers, you must work with your survey provider to ensure that you receive your survey data in files meeting these definitions. This includes ensuring the proper ordering of the fields and using a comma-delimited *.csv or *.txt, flat file format. A *.csv format file is recommended and preferred. Arrange to receive separate survey data flat files for each of the elements in the previous list. For compensation surveys, arrange to obtain author, survey instance, dimensions, and compensation survey files from the provider. For benchmark surveys, arrange to obtain author, survey instance, dimensions, and benchmark survey files from the provider. For employee surveys, arrange to obtain author, survey instance, and employee survey files from the provider.
If the external survey data that you receive is from a provider whose data doesn't fit these file definitions, then you have several options:
Work with your current survey provider to make sure that you receive the survey data in a format that meets these file definitions.
Work with a different survey provider who delivers the survey data in a format that meets these file definitions.
Manipulate the survey data that you receive from your provider to change it into a format that meets these file definitions.
Use our file definitions and delivered ETL jobs as templates, and modify them to suit your needs.
After you have run the appropriate ETL jobs and imported your data into the Operational Warehouse - Enriched (OWE) tables, you can view and edit the data in the tables online.
Note. The following table lists pages used to view and edit survey data in the data warehouse tables. Because these pages are basic setup pages and the fields for the underlying tables are shown in the flat file definitions at the end of this chapter, this chapter doesn't discuss these pages in detail here.
Page Name |
Definition Name |
Navigation |
Usage |
Survey Author |
WA_AUTHOR_R00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Author |
View and edit data about external survey providers and their address information. |
Survey Instance |
WA_SURVEY_R00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Instance |
View and edit publication information about a survey. |
Survey Job Code |
WA_SUR_JOB_D00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Job Code |
View and edit the job codes that are used in external surveys. |
Survey Compensation Code |
WA_SUR_COMP_D00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Compensation Code |
View and edit external survey compensation codes. |
Survey Geography ID |
WA_SUR_GEO_D00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Geography ID |
View and edit external survey geographical regions. |
Survey Industry Code |
WA_SUR_IND_D00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Industry Code |
View and edit external survey industry categories. |
Survey Financial Code |
WA_SUR_FIN_D00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Financial Code |
View and edit data about the financial size (based on factors such as sales or revenue) of companies participating in external surveys. |
Survey Unit Code |
WA_SUR_UNIT_D00 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Setup Survey Unit Code |
View and edit data, other than financial data, that measures the relative size of companies participating in external surveys. |
To map survey data, use the WA_MAP_TREE_TBL.GBL, WA_JOB_MAP_DFN.GBL, WA_COMP_MAP_DFN.GBL, WA_GEO_MAP_DFN.GBL, WA_IND_MAP_DFN.GBL, WA_FIN_MAP_DFN.GBL, and WA_UNIT_MAP_DFN.GBL components.
This section provides an overview of survey mapping definitions and discusses how to:
Set up workforce trees.
Map job codes.
Map survey compensation codes.
Map geography IDs.
Map industry codes.
Map financial codes.
Map unit codes.
When you run the setup batch for salary surveys and benchmark surveys, the system partially populates the following OWE mapping tables: WA_COMP_MAP_DFN, WA_FIN_MAP_DFN, WA_GEO_MAP_DFN, WA_IND_MAP_DFN, WA_JOB_MAP_DFN, and WA_UNIT_MAP_DFN. Your next task is to complete the data mapping definitions using the access pages for these tables. The mapping definitions are needed to prepare for your second set of ETLs. In this step you map survey data to previously defined EPM dimensions (JOB, COMPENSATION, GEOGRAPHY, INDUSTRY, FINANCIAL, and UNIT) and trees (JOBCODE, COMPCODE, GEOGRAPHY, INDUSTRY, FINCODE, and UNITCODE).
Common Elements
The following common elements apply to this section:
SetID |
Select a setID to access the survey mapping pages. |
Survey Instance |
Select a survey instance to access the survey mapping pages. |
Note. Depending upon the page, you also specify one of the following: a survey job code, survey compensation code, survey geography ID, survey industry code, survey financial code, or survey unit code. The values for these fields are in the system for you to use as prompts after you have run the Setup batch of ETLs.
Survey |
Displays the survey author ID that is associated with this survey instance. |
Description |
Click access the Mapping Notes page, where you can enter a long description or more detailed mapping notes (Access this page from these other pages: Job Code Map, Compensation Code Map, Geography ID Map, Industry Map, Financial Code Map, or Unit Code Map page, click the Description button. Access the Mapping Notes page by clicking the Description button). |
View Tree |
Click to access the Tree Viewer page, where you can specify the name of the tree node or detail to which you want to map your survey value. The system also displays the name of the current selection for the appropriate default tree. You indicated this tree on the Workforce Trees page. |
Page Name |
Definition Name |
Navigation |
Usage |
Workforce Trees |
WA_MAP_TREE_TBL |
Workforce Analytics, Workforce Analytics Setup, Setup Workforce Trees, Workforce Trees |
Specify the setID, effective date, and names of the trees that you want the system to use. In Workforce Rewards (external surveys, market compensation) and Workforce Planning, many of the main dimensions get their hierarchical structure from trees. |
Job Code Map |
WA_JOB_MAP_DFN |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Job Code |
Use this page to map survey job codes to nodes on the Workforce Analytics JOBCODE tree. |
Mapping Notes |
WA_DESCR_SPNL |
From the Job Code Map, Compensation Code Map, Geography ID Map, Industry Map, Financial Code Map, or Unit Code Map page, click the Description button. |
Enter a long description or more detailed mapping notes for any of the following primary pages that are applicable: Job Code Map, Compensation Code Map, Geography ID Map, Industry Code Map, Financial Code Map, or Unit Code Map. |
Compensation Code Map |
WA_COMP_MAP_DFN |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Compensation Code |
Map survey compensation codes to nodes on the Workforce Analytics COMPCODE tree. |
Geography ID Map |
WA_GEO_MAP_DFN |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Geography ID |
Map survey geography IDs to nodes on the Workforce Analytics GEOGRAPHY tree. |
Industry ID Map |
WA_IND_MAP_DFN |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Industry ID |
Map survey industry IDs to nodes on the Workforce Analytics INDUSTRY tree. |
Financial Code Map |
WA_FIN_MAP_DFN |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Financial Code |
Map survey financial codes to nodes on the Workforce Analytics FINCODE tree. |
Unit Code Map |
WA_UNIT_MAP_DFN |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Unit Code |
Map survey unit codes to nodes on the Workforce Analytics UNITCODE tree. |
Access the Workforce Trees page (Workforce Analytics, Workforce Analytics Setup, Setup Workforce Trees, Workforce Trees).
Note. This page has SetID as a key. When you specify a default tree on this page for use with a Workforce Rewards application, the tree mapping value must remain constant. That is, the tree mapping must remain unchanged from the start of your market compensation survey import through your use of the survey data in the Workforce Rewards business process modules.
Job Code Tree, Compensation Code Tree, Geography Tree, Organization Tree, Account Tree, Position Tree, and Business Unit Tree |
These trees are used in one or more of the workforce analytic applications. Prompt values are from the PF_METATREE_TBL table. This ensures that only those trees that have tree metadata are available to map. |
Industry Tree, Financial Code Tree, and Unit Code Tree |
These trees are used in the Workforce Rewards application. Prompt values are from the WA_INDTREE_VW, WA_FINCODE_VW, and WA_UNITCODE_VW tables, respectively. |
Competency Tree |
This tree is used in the Workforce Planning application. Prompt values are from the WA_CMPT_TREE_VW table. |
Access the Job Code Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Map Job Code).
Survey |
Displays the description, FLSA status, and total points from the WA_SUR_JOB_VW table. |
Workforce Analytics |
Select the job code set to which you want to map the survey job code (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Employee and Job, Job Code Set). |
Job Code Set |
Job Code Set prompts you from the WA_JBCDSET_VW table. A job code set is a group of related job codes belonging to the same node on the JOBCODE tree. A leaf or detail on the tree is an individual job code. |
Access the Compensation Code Map page.
Survey |
Displays the description from the WA_SUR_COMP_VW table. |
Workforce Analytics |
Select the compensation code to which you want to map the survey compensation code. |
Compensation Code |
Compensation Code prompts you from the COMPCODE_VW table. |
Access the Geography ID Map page.
Survey |
Displays the description from the WA_SUR_GEO_VW table. |
Workforce Analytics |
Select the geography ID to which you want to map the survey geography ID (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, Common, Geography). |
Geography ID |
Geography ID prompts you from the GEOGRAPHY_VW table. |
Access the Industry Code Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Industry Code Map).
Survey |
Displays the description from the WA_SUR_IND_VW table. |
Workforce Analytics |
Select the industry ID to which you want to map the survey industry ID. |
Industry ID |
Industry ID prompts you from the WA_INDUSTRY_VW table. |
Access the Financial Code Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Financial Code Map).
Survey |
Displays the description, from value and to value from the WA_SUR_FIN_VW table. |
Workforce Analytics |
Select the financial code to which you want to map the survey financial code. |
Financial Code |
Financial Code prompts you from the WA_FINCODE_VW table. When you use the Tab key to move through the field, the system displays the associated from value and to value. |
Access the Unit Code Map page (EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Unit Code Map).
Survey |
Displays the description, from value and to value from the WA_SUR_UNIT_VW table. |
Workforce Analytics |
Select the unit code to which you want to map the survey unit code. |
Unit Code |
Unit Code prompts you from the WA_UNITCODE_VW table. When you use the Tab key to move through the field, the system displays the associated from value and to value. |
After you've entered your survey mapping definitions, you can view the survey mapping definitions at any time using the following pages.
Page Name |
Definition Name |
Navigation |
Usage |
Job Code Map |
WA_SURMAP1_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Job Code Map |
Review job code mapping definitions online. |
Compensation Code Map |
WA_SURMAP2_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Compensation Code Map |
Review compensation code mapping definitions online. |
Geography ID Map |
WA_SURMAP3_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Geography ID Map |
Review geography ID mapping definitions online. |
Industry Code Map |
WA_SURMAP4_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Industry Code Map |
Review industry code mapping definitions online. |
Financial Code Map |
WA_SURMAP5_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Financial Code Map |
Review financial code mapping definitions online. |
Unit Code Map |
WA_SURMAP6_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Unit Code Map |
Review unit code mapping definitions online. |
Job Code Set Map |
WA_JOBMAP_VW |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Survey, Review Job Code Set Map |
Review job code set mapping definitions online. |
After you've completed the batches of ETL job sequencers needed to import external survey data into your data warehouse tables, you may want to review the data and jobs. To do this, you use the following Crystal reports.
See Also
Page Name |
Definition Name |
Navigation |
Usage |
Compensation Survey Data report |
RUN_RWA_0020 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Compensation Survey Data |
Generate the Compensation Survey Data Crystal report (WAC0020). |
Benchmark Data Record report |
RUN_RWA_0021 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Benchmark Survey Data |
Generate the Benchmark Data Crystal report (WAC0021). |
Unit Code Map report |
RUN_RWA_0022 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Unit Code Map |
Generate the Unit Code Map Crystal report (WAC0022). |
Compensation Code Map report |
RUN_RWA_0023 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Compensation Code Map |
Generate the Compensation Code Map Crystal report (WAC0023). |
Geography ID Map report |
RUN_RWA_0024 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Geography ID Map |
Generate the Geography ID Map Crystal report (WAC0024). |
Industry Code Map report |
RUN_RWA_0025 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Industry Code Map |
Generate the Industry Code Map Crystal report (WAC0025). |
Job Code Map report |
RUN_RWA_0026 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Job Code Map |
Generate the Job Code Map Crystal report (WAC0026). |
Financial Code Map report |
RUN_RWA_0027 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Financial Code Map |
Generate the Financial Code Map Crystal report (WAC0027). |
Employee Survey report |
RUN_RWA_0035 |
EPM Foundation, Business Metadata, OW-E Dimension Maintenance, HRMS, Workforce Survey Setup Reports, Employee Survey Data |
Generate the Employee Survey Data Map Crystal report (WAC0035). |
This section provides an overview of external survey flat file definitions and discusses how to:
Review the author file definition.
Review the dimension file definition.
Review the survey instance file definition.
Review the compensation survey file definition.
Review the benchmark survey file definition.
Review the employee survey file definition.
Review the valid values for selected fields in the file definitions.
This section discusses the external survey flat file definitions that you use to work with your survey providers. Work with your providers to ensure that the survey data you receive from them fits the format specified in these file definitions. The end of this section provides lists of the valid values for selected fields in the file definitions that must use specified valid values.
Note. Wherever fields in the file definitions are designated as optional, Oracle recommends that you enter data in those fields. If you don't enter data in the optional fields, the system displays error messages when you run the ETL job sequencers, and data is not supplied in those fields in the data warehouse tables.
The following table contains the Author flat file definition.
Note. You need the Author file when you have new author identification information. That is, you need it only when the survey provider data is new or has changed.
Field Name |
Field Type / Length / Format |
Valid Values |
Required or Optional |
Target Warehouse Tables |
WA_AUTHOR_ID |
Character / 10 |
R |
WA_AUTHOR_R00.WA_AUTHOR_ID |
|
EFFDT |
Date / 8 / MMDDYYYY |
R |
WA_AUTHOR_R00.EFFDT |
|
EFF_STATUS |
Character / 1 |
Yes |
R |
WA_AUTHOR_R00.EFF_STATUS |
ADDRESS1 |
Character / 35 |
R |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
ADDRESS2 |
Character / 35 |
O |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
ADDRESS3 |
Character / 35 |
O |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
ADDRESS4 |
Character / 35 |
O |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
CITY |
Character / 30 |
R |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
COUNTRY |
Character / 3 |
R |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
COUNTRY_CODE |
Character / 3 |
Yes |
R |
WA_AUTHOR_R00.BUS_PHONE_SBR |
DESCR |
Character / 30 |
R |
WA_AUTHOR_R00.DESCR |
|
DESCRLONG |
Long / 100 |
O |
WA_AUTHOR_R00.DESCRLONG |
|
EXTENSION |
Character / 6 |
O |
WA_AUTHOR_R00.BUS_PHONE_SBR |
|
FAX |
Character/ 24 |
O |
WA_AUTHOR_R00.BUS_PHONE_SBR |
|
PHONE |
Character / 24 |
R |
WA_AUTHOR_R00.BUS_PHONE_SBR |
|
POSTAL |
Character / 12 |
R |
WA_AUTHOR_R00.WA_ADDR_SBR |
|
STATE |
Character / 6 |
R |
WA_AUTHOR_R00.WA_ADDR_SBR |
The following example shows a single row of sample data, in comma delimited format, to help you create your author file:
Example row of Author file data in flat file format
The following table contains the Dimension flat file definition. With respect to the last two columns, please note the following:
If WA_SUR_DIM_TYPE = C, then the target record is WA_SUR_COMP_D00.
If WA_SUR_DIM_TYPE = F, then the target record is WA_SUR_FIN_D00.
If WA_SUR_DIM_TYPE = G, then the target record is WA_SUR_GEO_D00.
If WA_SUR_DIM_TYPE = I, then the target record is WA_SUR_IND_D00.
If WA_SUR_DIM_TYPE = J, then the target record is WA_SUR_JOB_D00.
If WA_SUR_DIM_TYPE = U, then the target record is WA_SUR_UNIT_D00.
Note. You need the Dimension file when you have a new or changed dimension information. The WA_AUTHOR_ID in the Dimension file must be a valid author identification in the Author file definition.
Field Name |
Field Type / Length / Format |
Valid Values |
Required or Optional
|
Target Warehouse Tables |
Comments |
WA_AUTHOR_ID |
Character / 10 |
R |
Target record is WA_AUTHOR_ID |
||
WA_SUR_DIM_TYPE |
Character / 1 |
Yes |
R |
Target record is *_CD |
The '*' symbol could be G for Geography ID, J for Job Code, F for Financial Code, and so on. |
WA_SUR_CODE |
Character / 10 |
R |
None |
Driver for Target Record ETL mapping. These are the actual Geography, Compensation, Job Codes depending upon the WA_SUR_DIM_TYPE. |
|
EFFDT |
Date / 8 / MMDDYYYY |
R |
Target record is EFFDT |
||
EFF_STATUS |
Character / 1 |
Yes |
R |
Target record is EFF_STATUS |
|
JOB_TYPE |
Character / 1 |
Yes |
R |
Target record is FLSA_STATUS |
Required only when type is J |
WA_POINTS |
Number / 3.0 |
R |
Target record is WA_POINTS |
Required only when type is J |
|
WA_FROM_VALUE |
Number / 15.0 |
R |
Target record is WA_FROM_VALUE |
Required only when type is F or U |
|
WA_TO_VALUE |
Number / 15.0 |
R |
Target record is WA_TO_VALUE |
Required only when type is F or U |
|
DESCR |
Character / 30 |
R |
Target record is DESCR |
||
DESCRLONG |
Long / 100 |
R |
Target record is DESCRLONG |
Required only when type is J |
The following example shows six rows of sample data, in comma delimited format, to help you create your Dimension file:
Example rows of Dimension file data in flat file format
The following table contains the Survey Instance flat file definition.
Note. The Survey Instance file is required for a new survey instance with new survey data. The WA_AUTHOR_ID in this file must be a valid author identification in the Author file definition.
Field Name |
Field Type/ Length/ Format |
Valid Values |
Required or Optional |
Target Warehouse Tables |
WA_SURVEY_ID |
Character / 10 |
R |
WA_SURVEY_R00.WA_SURVEY_ID |
|
EFFDT |
Date / 8 / MMDDYYYY |
R |
WA_SURVEY_R00.EFFDT |
|
EFF_STATUS |
Character / 1 |
Yes |
R |
WA_SURVEY_R00.EFF_STATUS |
WA_AUTHOR_ID |
Character / 10 |
R |
WA_SURVEY_R00.WA_AUTHOR_ID |
|
DESCR |
Character / 30 |
R |
WA_SURVEY_R00.DESCR |
|
DESCRLONG |
Long / 100 |
N |
WA_SURVEY_R00.DESCRLONG |
|
WA_PUBLICATION_DT |
Date / 8 |
R |
WA_SURVEY_R00.WA_PUBLICATION_ID |
|
WA_SUR_CO_COUNT |
Number / 8.0 |
R |
WA_SURVEY_R00.WA_SUR_CO_COUNT |
|
WA_SUR_EE_COUNT |
Number / 8.0 |
R |
WA_SURVEY_R00.WA_SUR_EE_COUNT |
|
CURRENCY_CD |
Character / 3.0 |
Yes |
R |
Note. This field is not mapped directly to the WA_SURVEY_R00 table. The ETL job uses the currency code as join condition to determine all of the setIDs from the WA_SET_CUR_VW with the given currency code. Then those setIDs are inserted in the WA_SURVEY_R00 table. |
The following example shows a single row of sample data, in comma delimited format, to help you create your Survey Instance file:
Example row of Survey Instance data in flat file format
The following table contains the Compensation Survey flat file definition.
Note. For the WA_SURVALUE_F00.WA_MEASURE_ID field, you should use the EPM Measure IDs that are delivered with the system. To add more requires modifications to the system. Also, note that the WA_SURVALUE_F00.WA_MEASURE_VAL field is for the actual monetary value of the measure, not the ID code.
Field Name |
Field Type / Length/ Format |
Valid Values |
Required or Optional |
Target Warehouse Tables |
WA_SURVEY_ID |
Character / 10 |
R |
WA_IND_MAP_DFN.WA_SURVEY_ID WA_FIN_MAP_DFN.WA_SURVEY_ID WA_UNIT_MAP_DFN.WA_SURVEY_ID WA_JOB_MAP_DFN.WA_SURVEY_ID WA_COMP_MAP_DFN.WA_SURVEY_ID WA_GEO_MAP_DFN.WA_SURVEY_ID WA_SURVALUE_F00.WA_SURVEY_ID (ETL2) |
|
WA_SUR_JOBCODE |
Character / 10 |
R |
WA_JOB_MAP_DFN.WA_SUR_JOBCODE |
|
WA_SUR_COMPCODE |
Character / 10 |
R |
WA_COMP_MAP_DFN.WA_SUR_COMPCODE |
|
WA_SUR_GEO_ID |
Character / 10 |
R |
WA_GEO_MAP_DFN.WA_SUR_GEO_ID |
|
WA_SUR_IND_ID |
Character / 10 |
R |
WA_IND_MAP_DFN.WA_SUR_IND_ID |
|
WA_SUR_FINCODE |
Character / 10 |
R |
WA_FIN_MAP_DFN.WA_SUR_FINCODE |
|
WA_SUR_UNITCODE |
Character / 10 |
R |
WA_UNIT_MAP_DFN.WA_SUR_UNITCODE |
|
EFFDT |
Date / 8 / MMDDYYYY |
R |
WA_SURVALUE_F00.EFFDT |
|
COMP_FREQUENCY |
Character / 1 |
Yes |
R |
WA_SURVALUE_F00.COMP_FREQUENCY |
WA_MEASURE_ID |
Character / 3 |
Yes |
R |
WA_SURVALUE_F00.WA_MEASURE_ID |
WA_MEASURE_VAL |
Number / 15.6 |
R |
WA_SURVALUE_F00.WA_MEASURE_VAL |
|
CURRENCY_CD |
Character / 3 |
Yes |
R |
WA_SURVALUE_F00.CURRENCY_CD |
WA_UNITS |
Character / 1 |
Yes |
R |
ETL uses this to determine whether value conversion is required. |
WA_SAMPLE_CO_COUNT |
Number / 8.0 |
R |
WA_SURVALUE_F00.WA_SAMPLE_CO_COUNT |
|
WA_SAMPLE_EE_COUNT |
Number / 8.0 |
R |
WA_SURVALUE_F00.WA_SAMPLE_EE_COUNT |
The following example shows six rows of sample data, in comma delimited format, to help you create your Compensation Survey file:
Example rows of Compensation Survey data in flat file format
The following table contains the Benchmark Survey flat file definition.
Note. For the WA_BENCHMRK_F00.PF_METRIC_ID field, you should use the EPM measure IDs that are delivered with the system. To add more requires modifications to the system. Also, note that the WA_BENCHMRK_F00.WA_MEASURE_VAL field is for the actual value of the metric, not the ID code.
Field Name |
Field Type/ Len./ Format |
Valid Value |
Required or Optional |
ETL |
Target Warehouse Tables |
WA_SURVEY_ID |
Character / 10 |
R |
1,2 |
WA_IND_MAP_DFN.WA_SURVEY_ID WA_FIN_MAP_DFN.WA_SURVEY_ID WA_UNIT_MAP_DFN.WA_SURVEY_ID WA_GEO_MAP_DFN.WA_SURVEY_ID WA_BENCHMRK_F00.WA_SURVEY_ID (ETL2) |
|
WA_SUR_GEO_ID |
Character / 10 |
R |
1 |
WA_GEO_MAP_DFN.WA_SUR_GEO_ID |
|
WA_SUR_IND_ID |
Character / 10 |
R |
1 |
WA_IND_MAP_DFN.WA_SUR_IND_ID |
|
WA_SUR_FINCODE |
Character / 10 |
R |
1 |
WA_FIN_MAP_DFN.WA_SUR_FINCODE |
|
WA_SUR_UNITCODE |
Character / 10 |
R |
1 |
WA_UNIT_MAP_DFN.WA_SUR_UNITCODE |
|
EFFDT |
Date / 8 / MMDDYYYY |
R |
2 |
WA_BENCHMRK_F00.EFFDT |
|
PF_METRIC_ID |
Character / 3 |
Yes |
R |
2 |
WA_BENCHMRK_F00.PF_METRIC_ID |
WA_MEASURE_VAL |
Number / 15.6 |
R |
2 |
WA_BENCHMRK_F00.WA_MEASURE_VAL |
|
CURRENCY_CD |
Character / 3 |
Yes |
R |
2 |
WA_BENCHMRK_F00.CURRENCY_CD |
WA_UNITS |
Character / 1 |
Yes |
R |
2 |
ETL uses this to determine whether conversion of value is required. |
WA_SAMPLE_CO_COUNT |
Number / 8.0 |
R |
2 |
WA_BENCHMRK_F00.WA_SAMPLE_CO_COUNT |
|
WA_SAMPLE_EE_COUNT |
Number / 8.0 |
R |
2 |
WA_BENCHMRK_F00.WA_SAMPLE_EE_COUNT |
The following example shows six rows of sample data, in comma delimited format, to help you create your Benchmark Survey file:
Example rows of Benchmark Survey data in flat file format
The following table contains the Employee Survey flat file definition.
Field Name |
Field Type / Length/ Format |
Valid Values |
Required or Optional |
Target Warehouse Tables |
WA_SURVEY_ID |
Character / 10 |
R |
WA_EESURVEY_F00.WA_SURVEY_ID |
|
EFFDT |
Date / 8 / MMDDYYYY |
R |
WA_EESURVEY_F00.EFFDT |
|
EFF_STATUS |
Character / 1 |
Yes |
R |
WA_EESURVEY_F00.EFF_STATUS |
WA_RM_FACTOR_ID |
Character / 10 |
R |
WA_EESURVEY_F00.WA_RM_FACTOR_ID |
|
PF_OBJ_ID |
Character / 20 |
R |
WA_EESURVEY_F00.PF_OBJ_ID |
|
PF_OBJ_TYPE |
Character / 4 |
Yes |
R |
WA_EESURVEY_F00.PF_OBJ_TYPE |
WA_FACTOR_WEIGHT |
Number / 5.0 |
R |
WA_EESURVEY_F00.WA_FACTOR_WEIGHT |
|
WA_SAMPLE_EE_COUNT |
Number / 8.0 |
R |
WA_EESURVEY_F00.WA_SAMPLE_EE_COUNT |
The following tables in this section provide lists of the valid values for selected fields in the file definitions that must use specified valid values.
Effective Status
EFF_STATUS Valid Values |
A = Active |
I = Inactive |
Country Code
Valid values are any of the country codes in COUNTRY_TBL.
Survey Dimension Type
WA_SUR_DIM_TYPE Valid Values |
C = Compensation |
F = Financials |
G = Geography |
I = Industry |
J = Job |
U = Unit |
Job Type
JOB_TYPE Valid Values |
A = Administrative |
E = Executive |
M = Management |
N = Nonexempt |
O = Outside Salesperson |
P = Professional |
X = None |
Currency Code
Valid values are any currency code in the CURRENCY_CD_TBL.
Compensation Frequency
COMP_FREQUENCY Valid Values |
A = Annual |
B = Biweekly |
D = Daily |
H = Hourly |
M = Monthly |
S = Semimonthly |
W = Weekly |
Note. HCM Warehouse Workforce Insight analysis templates, as delivered, are set up to use compensation-frequency values of Annual only. Modification is needed if you want your analysis templates to use any other compensation frequency values.
Units
WA_UNITS Valid Values |
1 = Ones |
2 = Hundreds |
3 = Thousands |
4 = 10 Thousands |
5 = 100 Thousands |
6 = Millions |
7 = 10 Millions |
8 = 100 Millions |
9 = Billions |
Object Type
PF_OBJ_TYPE Valid Values |
1000 = Employee |
2000 = Department |
2100 = Business Unit |
9000 = Job Code |
Measure ID and Metric ID Codes
WA_MEASURE_ID / PF_METRIC_ID Valid Values |
Description |
Survey Type |
001 |
Y-Intercept- Natural |
Compensation Survey |
002 |
Slope- Natural |
Compensation Survey |
003 |
Standard Error |
Compensation Survey |
004 |
R-Squared |
Compensation Survey |
005 |
Actual 10th Percentile Amount |
Compensation Survey |
006 |
Actual 20th Percentile Amount |
Compensation Survey |
007 |
Actual 25th Percentile Amount |
Compensation Survey |
008 |
Actual 30th Percentile Amount |
Compensation Survey |
009 |
Actual 40th Percentile Amount |
Compensation Survey |
010 |
Actual 50th Percentile Amount |
Compensation Survey |
011 |
Actual 60th Percentile Amount |
Compensation Survey |
012 |
Actual 70th Percentile Amount |
Compensation Survey |
013 |
Actual 75th Percentile Amount |
Compensation Survey |
014 |
Actual 80th Percentile Amount |
Compensation Survey |
015 |
Actual 90th Percentile Amount |
Compensation Survey |
016 |
Actual Average Amount |
Compensation Survey |
017 |
Actual 10th Percentile Percent |
Compensation Survey |
018 |
Actual 20th Percentile Percent |
Compensation Survey |
019 |
Actual 25th Percentile Percent |
Compensation Survey |
020 |
Actual 30th Percentile Percent |
Compensation Survey |
021 |
Actual 40th Percentile Percent |
Compensation Survey |
022 |
Actual 50th Percentile Percent |
Compensation Survey |
023 |
Actual 60th Percentile Percent |
Compensation Survey |
024 |
Actual 70th Percentile Percent |
Compensation Survey |
025 |
Actual 75th Percentile Percent |
Compensation Survey |
026 |
Actual 80th Percentile Percent |
Compensation Survey |
027 |
Actual 90th Percentile Percent |
Compensation Survey |
028 |
Actual Average Percent |
Compensation Survey |
029 |
Actual % of Eligible Employees |
Compensation Survey |
030 |
Target 10th Percentile Amount |
Compensation Survey |
031 |
Target 20th Percentile Amount |
Compensation Survey |
032 |
Target 25th Percentile Amount |
Compensation Survey |
033 |
Target 30th Percentile Amount |
Compensation Survey |
034 |
Target 40th Percentile Amount |
Compensation Survey |
035 |
Target 50th Percentile Amount |
Compensation Survey |
036 |
Target 60th Percentile Amount |
Compensation Survey |
037 |
Target 70th Percentile Amount |
Compensation Survey |
038 |
Target 75th Percentile Amount |
Compensation Survey |
039 |
Target 80th Percentile Amount |
Compensation Survey |
040 |
Target 90th Percentile Amount |
Compensation Survey |
041 |
Target Average Amount |
Compensation Survey |
042 |
Target 10th Percentile Percent |
Compensation Survey |
043 |
Target 20th Percentile Percent |
Compensation Survey |
044 |
Target 25th Percentile Percent |
Compensation Survey |
045 |
Target 30th Percentile Percent |
Compensation Survey |
046 |
Target 40th Percentile Percent |
Compensation Survey |
047 |
Target 50th Percentile Percent |
Compensation Survey |
048 |
Target 60th Percentile Percent |
Compensation Survey |
049 |
Target 70th Percentile Percent |
Compensation Survey |
050 |
Target 75th Percentile Percent |
Compensation Survey |
051 |
Target 80th Percentile Percent |
Compensation Survey |
052 |
Target 90th Percentile Percent |
Compensation Survey |
053 |
Target Average Percent |
Compensation Survey |
054 |
Target % of Eligible Employees |
Compensation Survey |
055 |
Y-Intercept- Common |
Compensation Survey |
056 |
Slope- Common |
Compensation Survey |
057 |
R |
Compensation Survey |
058 |
Standard Error- Logarithmic |
Compensation Survey |
100 |
Productivity Factor |
Benchmark Survey |
101 |
Efficiency Factor |
Benchmark Survey |
102 |
Effectiveness Factor |
Benchmark Survey |
103 |
Employee Compensation Return on Investment (ROI) |
Benchmark Survey |
104 |
Workforce Compensation ROI |
Benchmark Survey |
105 |
Employee Workforce ROI |
Benchmark Survey |
106 |
Contingent Workforce ROI |
Benchmark Survey |
107 |
Total Compensation Revenue Ratio |
Benchmark Survey |
108 |
Cash Compensation Revenue Ratio |
Benchmark Survey |
109 |
Benefits Revenue Ratio |
Benchmark Survey |
110 |
Total Compensation Expenses Ratio |
Benchmark Survey |
111 |
Cash Compensation Expenses Ratio |
Benchmark Survey |
112 |
Benefits Expenses Ratio |
Benchmark Survey |
113 |
Executive Ratio |
Benchmark Survey |
114 |
Supervisory Ratio |
Benchmark Survey |
115 |
Executive Compensation Ratio |
Benchmark Survey |
116 |
Supervisory Compensation Ratio |
Benchmark Survey |
117 |
Executive Compensation Factor |
Benchmark Survey |
118 |
Supervisory Compensation Factor |
Benchmark Survey |
119 |
Employee Compensation Factor |
Benchmark Survey |
120 |
Contingency Workforce Ratio |
Benchmark Survey |
121 |
Contingency Revenue Ratio |
Benchmark Survey |
122 |
Contingency Expenses Percent |
Benchmark Survey |
123 |
Contingency Expenses Ratio |
Benchmark Survey |
124 |
Total Labor Cost Revenue Ratio |
Benchmark Survey |
125 |
Total Labor Cost Expense Ratio |
Benchmark Survey |
126 |
Transfer Ratio |
Benchmark Survey |
127 |
Promotion Ratio |
Benchmark Survey |
128 |
Hire Ratio |
Benchmark Survey |
129 |
Exempt Hire Ratio |
Benchmark Survey |
130 |
Non-Exempt Hire Ratio |
Benchmark Survey |
131 |
Increase Hire Ratio |
Benchmark Survey |
132 |
Replacement Hire Ratio |
Benchmark Survey |
133 |
Separation Ratio |
Benchmark Survey |
134 |
Exempt Separation Ratio |
Benchmark Survey |
135 |
Non-Exempt Separation Ratio |
Benchmark Survey |
136 |
Open Position Ratio |
Benchmark Survey |
137 |
Staffing- Hire Ratio |
Benchmark Survey |
138 |
Staffing- Exempt Hire Ratio |
Benchmark Survey |
139 |
Staffing- Non-Exempt Hire Ratio |
Benchmark Survey |
140 |
Staffing- Increase Hire Ratio |
Benchmark Survey |
141 |
Staffing- Replacement Hire Ratio |
Benchmark Survey |
142 |
Staffing- Open Position Ratio |
Benchmark Survey |
143 |
Time to Fill Factor |
Benchmark Survey |
144 |
Exempt Time to Fill Factor |
Benchmark Survey |
145 |
Non-Exempt Time to Fill Factor |
Benchmark Survey |
146 |
Hire to Separation Ratio |
Benchmark Survey |
147 |
Hiring Expenses Factor |
Benchmark Survey |
148 |
Exempt Hiring Expenses Factor |
Benchmark Survey |
149 |
Non-Exempt Hiring Expenses Factor |
Benchmark Survey |
150 |
Hiring Expenses Ratio |
Benchmark Survey |
151 |
Tenure Profile |
Benchmark Survey |
152 |
Exempt Tenure Profile <=1 Year |
Benchmark Survey |
153 |
Exempt Tenure Profile <=3 Years |
Benchmark Survey |
154 |
Exempt Tenure Profile <=5 Years |
Benchmark Survey |
155 |
Exempt Tenure Profile <=10 Years |
Benchmark Survey |
156 |
Exempt Tenure Profile >10 Years |
Benchmark Survey |
157 |
Non-Exempt Tenure <=1 Year |
Benchmark Survey |
158 |
Non-Exempt Tenure <=3 Years |
Benchmark Survey |
159 |
Non-Exempt Tenure <=5 Years |
Benchmark Survey |
160 |
Non-Exempt Tenure <=10 Years |
Benchmark Survey |
161 |
Non-Exempt Tenure >10 Years |
Benchmark Survey |
162 |
Voluntary Separation Tenure <=1 Year |
Benchmark Survey |
163 |
Voluntary Separation Tenure <=3 Years |
Benchmark Survey |
164 |
Voluntary Separation Tenure <=5 Years |
Benchmark Survey |
165 |
Voluntary Separation Tenure <=10 Years |
Benchmark Survey |
166 |
Voluntary Separation Tenure >10 Years |
Benchmark Survey |
167 |
Exempt Voluntary Separation Tenure <= 1 Year |
Benchmark Survey |
168 |
Exempt Voluntary Separation Tenure <= 3 Years |
Benchmark Survey |
169 |
Exempt Voluntary Separation Tenure <= 5 Years |
Benchmark Survey |
170 |
Exempt Voluntary Separation Tenure <=10 Years |
Benchmark Survey |
171 |
Exempt Voluntary Separation Tenure >10 Years |
Benchmark Survey |
172 |
Non-Exempt Voluntary Separation Tenure <=1 Year |
Benchmark Survey |
173 |
Non-Exempt Voluntary Separation Tenure <=3 Years |
Benchmark Survey |
174 |
Non-Exempt Voluntary Separation Tenure <=5 Years |
Benchmark Survey |
175 |
Non-Exempt Voluntary Separation Tenure <=10 Years |
Benchmark Survey |
176 |
Non-Exempt Voluntary Separation Tenure >10 Years |
Benchmark Survey |
178 |
Involuntary Separation Ratio |
Benchmark Survey |
179 |
Voluntary Separation Ratio |
Benchmark Survey |
180 |
Exempt Separation Ratio |
Benchmark Survey |
181 |
Exempt Involuntary Separation Ratio |
Benchmark Survey |
182 |
Exempt Voluntary Separation Ratio |
Benchmark Survey |
183 |
Non-Exempt Separation Ratio |
Benchmark Survey |
184 |
Non-Exempt Involuntary Separation Ratio |
Benchmark Survey |
185 |
Non-Exempt Voluntary Separation Ratio |
Benchmark Survey |
186 |
Employee Lost Time Factor |
Benchmark Survey |
187 |
FTE Lost Time Factor |
Benchmark Survey |
188 |
Workers' Compensation Expenses Ratio |
Benchmark Survey |
189 |
Employee Workers' Compensation Factor |
Benchmark Survey |
190 |
Workforce Workers' Compensation Factor |
Benchmark Survey |
191 |
HR Readiness- Support Ratio |
Benchmark Survey |
192 |
Exempt Ratio |
Benchmark Survey |
193 |
Investment Factor |
Benchmark Survey |
194 |
HR Readiness- Expenses Ratio |
Benchmark Survey |
195 |
Employee Investment Factor |
Benchmark Survey |
196 |
Trained Employee Investment |
Benchmark Survey |
197 |
Training- Expenses Ratio |
Benchmark Survey |
198 |
Training- Support Ratio |
Benchmark Survey |
199 |
Healthcare Factor |
Benchmark Survey |
200 |
Benefits Compensation Expenses Ratio |
Benchmark Survey |