Staging Data for Workforce Surveys
Before data from Workforce Communications surveys can be analyzed, it must be extracted in raw form from the tables where it is stored, transformed by retrieving and calculating various dimension values, and loaded into a table that is used for reporting purposes. This extract, transform, and load (ETL) process improves the performance of the Workforce Survey List pagelet and the Workforce Survey Report. It also allows a “snapshot” of a person's HR attributes (such as job, location, and department) to be stored, since these attributes change over time.
Workforce Communications uses an Application Engine program named Stage Data for Reporting to implement the ETL process. The program can be run on demand or scheduled to run periodically (for example, nightly). You should run the program with the Incremental Refresh option when survey responses come in, and with the Refresh Dimension References option when you make changes to your setup data (for example Age Group, Score Bracket, or Years Employed definitions).
Workforce survey data is staged by the WFC System Administrator.
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
RY_EE_STG |
Transforms survey response data and stores it in tables used for reporting. |
Use the Stage Data for Reporting page (RY_EE_STG) to transforms survey response data and stores it in tables used for reporting.
Navigation
Image: Stage Data for Reporting page
This example illustrates the fields and controls on the Stage Data for Reporting page. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| Incremental Refresh |
When the Application Engine program is run with this option, only the previously unprocessed rows in the Online Marketing tables holding survey responses will be processed and added to the reporting table. This is the default option. |
| Refresh Dimension References |
When this option is chosen, the Age Group, Years Employed, and Score Bracket IDs in each row in the reporting table are updated with the latest values if changes were made to any of these since the last Application Engine program was run in this mode. The Application Engine program must be run in this mode after changes are made to the Age Groups, Years Employed, or Score Brackets setup information so that the reporting table correctly reflects the changes made. |
When Incremental Refresh mode is selected and the Stage Data for Reporting program is run, the following actions occur.
Determine Rows to be Processed
The program first determines which rows in the survey data table need to be processed. Only rows belonging to dialogs that have the following considerations are eligible for processing:
The Objective value is associated with the Survey Type ofWorkforce.
At least one Survey document exists in the dialog.
The survey response row has a Status of Complete.
The program also checks whether a person has submitted the same survey multiple times. If two or more unprocessed rows in completed status are found for the same survey and for the same person in a single Quarter (as defined in the Time Frames setup), then only the latest row is processed. When adding this row to the reporting table, the program checks to ensure that a row for the same person, for the same survey, and for the same Quarter does not already exist due to previous processing. If such a row does exist, then that row is replaced with the new row.
Determine Information About Respondent
Next, the system checks to see if the respondent can be identified (that is, if a BO_ID exists in the database) or is anonymous. If the respondent is not anonymous, the following information is determined:
Department
Job Title
Location
Gender
Country
Date of Birth
Hire Date
Role (Worker or Person of Interest)
If the respondent is anonymous, these values are considered to have a default value of Unknown. This is also true of any dimensions that are not supplied for a particular identifiable respondent (for example, if a respondent with the Role ofPerson of Interest does not have aDepartment value).
Next, the respondent's Age Group is calculated by using the Date of Birth and then computing the Age Group into which he or she falls. If the respondent is anonymous, the Age Group is given the value of Unknown.
Similarly, the respondent's Years Employed tier is calculated using the most recent Hire Date on the person's record and then computing the Years Employed tier into which this value falls. As with Age Group, this value is set to Unknown for anonymous respondents.
Next, the Score Bracket into which the survey score falls is calculated by comparing the respondent's answers with the Score Brackets that were set up for the survey.
Finally, the Quarterly and Annual time periods into which the survey completion date falls is calculated.
All of the calculated data is stored as a row in the reporting table.
When Refresh Dimension References mode is selected and the Stage Data for Reporting program is run, the following actions occur:
Determine whether changes were made to any of the following setup values since the last time the Application Engine program was run:
Age Groups
Years Employed
Score Bracket
If the Age Group setup values were changed, the Age Group IDs in the reporting table are updated to match the new setup values.
If the Years Employed setup values were changed, the Years Employed Tier IDs are updated on all rows of the reporting table.
If the Score Bracket setup values for any survey were changed, then the Score Bracket ID is updated in the reporting table, but only for those surveys affected. Surveys that were not affected remain as they were.