Reviewing External Survey Tables and ETL Setup
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.
Reviewing the Data Warehouse Tables and ETL Jobs
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.
Reviewing the Survey File Definitions
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 topic, 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.