Importing External Survey Data to Data Warehouse Tables

This chapter provides an overview of external survey data and discusses how to:

Review sources of external survey data.
Review external survey tables and 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.

Understanding External Survey Data

External survey data is workforce-related data 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 Ascential DataStage ETL tool.

Reviewing Sources of External Survey Data

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 have the option of importing, 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 will have to create your own flat files and ETL jobs. Our data model will support this, and you will have access to Ascential DataStage to create the jobs and run the ETLs.

Reviewing External Survey Tables and ETL Setup

The external survey data setup and import process is similar for each type of survey data. We deliver the tools to enable you to import external survey data into EPM.

We've worked closely with third-party survey providers to develop the file definitions, ETL jobs and data warehouse tables delivered with Workforce Analytics. We 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.

Reviewing the Data Warehouse Tables and ETL Jobs

Before you begin, first 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 delivered with EPM. Determine how you want to structure your system to best fit the needs of your organization.

Evaluate the ETL jobs delivered and determine if they are properly configured to meet the needs of your organization.

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.

Reviewing the Survey File Definitions

Prior to ETL processing, you must ensure 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, we deliver 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 we deliver, you must work with your survey provider to ensure 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 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 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 you receive from your provider to get 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.

Viewing and Editing the Survey Data Warehouse Tables

Once 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. Since 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, we won't discuss these pages in any detail here.

Pages Used to View and Edit Survey Data

Page Name	Object 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 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.

Completing Survey Mapping Definitions

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

Understanding Survey Mapping Definitions

When you run the setup batch for salary surveys and benchmark surveys, the system partially populates the following Operational Warehouse - Enriched (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	To access the survey mapping pages specify a setID.
Survey Instance	To access the survey mapping pages specify a survey instance.

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 once you have run the Setup batch of ETLs.

Survey	In the Survey box the system displays the Survey Author ID associated with this Survey Instance.
Description	Click the Description button to access the Mapping Notes page, where you can enter a long description or more detailed mapping notes.
View Tree	In the Workforce Analytics box click the View Tree button 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.

Pages Used to Map Survey Data

Page Name	Object Name	Navigation	Usage
Workforce Trees	WA_MAP_TREE_TBL	Workforce Analytics, Workforce Analytic Setup, Setup Workforce Trees, Workforce Trees	Specify the setID, effective date, and names of the trees 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	Access this page from these other pages: Job Code Map, Compensation Code Map, Geography ID Map, Industry Map, Financial Code Map, Unit Code Map. Access the Mapping Notes page by clicking the Description button.	Enter a long description or more detailed mapping notes for the following primary pages: Job Code Map, Compensation Code Map, Geography ID Map, Industry Code Map, Financial Code Map, or Unit Code Map, whichever is applicable.
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 ID's 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 ID's 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.

Setting Up Workforce Trees

Access the Workforce Trees page.

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, and all the way 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.

Mapping Job Codes

Access the Job Code Map page.

Survey

In the Survey box the system displays the Description, FLSA Status, and Total Points from the WA_SUR_JOB_VW table.

Workforce Analytics

In the Workforce Analytics box, select the Job Code Set to which you want to map the Survey Job Code.

Job Code Set

Job Code Set prompts 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.

Mapping Survey Compensation Codes

Access the Compensation Code Map page.

Survey	In the Survey box the system displays the Description from the WA_SUR_COMP_VW table.
Workforce Analytics	In the Workforce Analytics box, select the Compensation Code to which you want to map the Survey Compensation Code.
Compensation Code	Compensation Code prompts from the COMPCODE_VW table.

Mapping Geography IDs

Access the Geography ID Map page.

Survey	In the Survey box the system displays the Description from the WA_SUR_GEO_VW table.
Workforce Analytics	In the Workforce Analytics box, select the Geography ID to which you want to map the Survey Geography ID.
Geography ID	Geography ID prompts from the GEOGRAPHY_VW table.

Mapping Industry Codes

Access the Industry Code Map page.

Survey	In the Survey box the system displays the Description from the WA_SUR_IND_VW table.
Workforce Analytics	In the Workforce Analytics box, select the Industry ID to which you want to map the Survey Industry ID.
Industry ID	Industry ID prompts from the WA_INDUSTRY_VW table.

Mapping Financial Codes

Access the Financial Code Map page.

Survey	In the Survey box the system displays the Description, From Value and To Value from the WA_SUR_FIN_VW table.
Workforce Analytics	In the Workforce Analytics box, select the Financial Code to which you want to map the Survey Financial Code.
Financial Code	Financial Code prompts from the WA_FINCODE_VW table. When you Tab through the field the system displays the associated From Value and To Value.

Mapping Unit Codes

Access the Unit Code Map page.

Survey	In the Survey box the system displays the Description, From Value and To Value from the WA_SUR_UNIT_VW table.
Workforce Analytics	In the Workforce Analytics box, select the Unit Code to which you want to map the Survey Unit Code.
Unit Code	Unit Code prompts from the WA_UNITCODE_VW table. When you Tab through the field the system displays the associated From Value and To Value.

Reviewing Survey Mapping Definitions Online

Once you've entered your survey mapping definitions, you can view the survey mapping definitions at any time, using the following pages.

Pages Used to Review Survey Mapping Definitions Online

Page Name	Object 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.

Generating External Survey Data Reports

Once 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.

Pages Used to Generate External Survey Data Reports

Page Name	Object 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	Run 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	Run 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	Run 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	Run 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	Run 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	Run 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	Run 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	Run 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	Run the Employee Survey Data Map Crystal report (WAC0035).

Reviewing the External Survey Flat File Definitions

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 valid values for selected field in the file definitions.

Understanding External Flat File Definitions

In this section, we present the external survey flat file definitions you use to work with your survey providers. Work with your providers to ensure the survey data you receive from them fits the format specified in these file definitions. At the end of this section, we also provide 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, we recommend you enter data in those fields. If you don't enter data in the optional fields, the system gives you error messages when you run the ETL job sequencers, and data is not populated in those fields in the data warehouse tables.

Reviewing the Author File Definition

The following table contains the Author flat file definition.

Note. The Author file is needed when you have new author identification information. In other words, it is only needed 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 assist you in creating your author file:

Example row of Author file data in flat file format

Reviewing the Dimension File Definition

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. The Dimension file is needed 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 'J'
WA_POINTS	Number / 3.0		R	Target record is WA_POINTS	Required only when type 'J'
WA_FROM_VALUE	Number / 15.0		R	Target record is WA_FROM_VALUE	Required only when type 'F' or type 'U'
WA_TO_VALUE	Number / 15.0		R	Target record is WA_TO_VALUE	Required only when type 'F' or type 'U'
DESCR	Character / 30		R	Target record is DESCR
DESCRLONG	Long / 100		R	Target record is DESCRLONG	Required only when type 'J'

The following example shows six rows of sample data, in comma delimited format, to assist you in creating your Dimension file:

Example rows of Dimension file data in flat file format

Reviewing the Survey Instance File Definition

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 assist you in creating your Survey Instance file:

Example row of Survey Instance data in flat file format

Reviewing the Compensation Survey File Definition

The following table contains the Compensation Survey flat file definition.

Note. For the WA_SURVALUE_F00.WA_MEASURE_ID field, it is important you use the EPM Measure IDs 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 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 assist you in creating your Compensation Survey file:

Example rows of Compensation Survey data in flat file format

Reviewing the Benchmark Survey File Definition

The following table contains the Benchmark Survey flat file definition.

Note. For the WA_BENCHMRK_F00.PF_METRIC_ID field, it is important you use the EPM measure IDs 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 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 assist you in creating your Benchmark Survey file:

Example rows of Benchmark Survey data in flat file format

Reviewing the Employee Survey File Definition

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

Reviewing the Valid Values for Selected Fields in the File Definitions

In the following tables in this section, we 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 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