Understanding the Workforce Rewards Data MartThis chapter provides an overview of the Workforce Rewards Data Mart and discusses the:
Data Mart data model.
Source ETL jobs.
Data Mart dimensions.
Data Mart facts.
Understanding the Workforce Rewards Data MartWorkforce Rewards Data Mart is a packaged set of processes, tables, and pages that provide decision-support analysis of the output of the Workforce Rewards analytic application. Workforce Rewards Data Mart consists of the following delivered elements:
Dimension and fact tables
Application Engine processes
Inquiry pages
You can use the Data Mart with the delivered inquiry pages or with analysis templates.
Using the Data Mart with Inquiry Pages
To use the delivered Data Mart with the delivered inquiry pages:
Run the scenario-generating processes in Workforce Rewards (Market-Based Pricing, Retention Assignment, and Workforce Simulation). The Data Mart building processes are called from each of these, and the system builds the dimension and fact tables automatically.
Access the related inquiry pages to evaluate the results of the scenarios. These pages retrieve and display data from the underlying Data Mart to help facilitate your analysis.
Using the Delivered Data Mart with Analysis Templates
We do not deliver an embedded reporting tool. You have the option of licensing a reporting tool separately for use with the Workforce Rewards Data Mart. Additionally, we do not deliver any preformatted, sample analysis templates for use with the Workforce Rewards Data Mart. You have the option of creating your own analysis templates. The analysis templates you create will depend on the separately licensed reporting tool you select. Refer to the documentation for your selected reporting tool.
Data Mart Data Model
The Workforce Rewards Data Mart is based on a logical and physical data model. We designed the model to present scenario results from Workforce Rewards in the best manner to support decision analysis. The model features a hierarchical structure for many of the delivered dimensions, and supports multidimensional analyses such as OLAP and ROLAP.
Data Mart Source ETL Jobs
For Workforce Rewards Data Mart, the required ETL (extract, transform and load) jobs are premapped from your HRMS tables. You use these to import data into the Operational Warehouse source tables (OWS). From the OWS tables you use ETL to load the data into the operational warehouse enriched (OWE) tables (F00s, D00s, and R00s). Some of the Data Mart dimensions use the imported information from these data warehouse tables. You also use your Workforce Rewards system to enrich and transform this imported data into new data, such as scenario results. This new data is also stored in data warehouse tables, and some of the Data Mart dimensions use this new information.
Warning! Any changes you make to the delivered data warehouse tables may affect Application Engine processes, PeopleCode processing, and pages throughout your system. You must evaluate and execute these types of changes very carefully.
ETL jobs are delivered with your Workforce Rewards system in an IBM WebSphere DataStage Project. We designed these jobs with, and they are intended for use with, IBM WebSphere DataStage. IBM WebSphere DataStage is the ETL tool delivered with EPM. If you choose to use an ETL tool other than IBM WebSphere DataStage, use the delivered ETL jobs as templates and modify them as needed.
See Also
Your IBM WebSphere DataStage documentation.
Data Mart Dimensions
The Workforce Rewards Data Mart contains the following dimensions:
|
Dimension Name |
Comment |
Dimension Table Name |
Application Engine Name |
|
Age |
List of all age groups as defined by user. This dimension enables analysis of your workforce by grouping calculated ages by durations. |
WFR_AGE_DIM |
WFI_AGE_DIM |
|
Business Unit |
Logical units created within your organization for reporting purposes. You can have one or many business units. They do not have any predetermined restrictions or requirements. This flexible structure enables you to define a higher level of reporting for your employees and group them according to functional or administrative tasks or for your business purposes. You can define business units that reflect the specific functional needs of your internal human resources departments, or reflect the actual business structure of your enterprise. |
WFR_BU_DIM |
WFI_BU_DIM |
|
Compensation |
Provides logical groupings of compensation types such as base pay, short term variable compensation, deferred long term compensation, and so on. The hierarchy is the Compensation tree. |
WFR_COMP_DIM |
WFI_COMP_DIM |
|
Compensation Plan Rule |
Provides information about compensation plan rules. There is no hierarchy. |
WFR_CMPPLRU_DIM |
WFR_CPRU_DIM |
|
Cost Rule |
Provides information about retention cost rules. There is no hierarchy. |
WFR_COSTRU_DIM |
WFR_CORU_DIM |
|
Currency Code |
Provides a way to classify financial amounts, for example USD, FFR, and so on. |
WFR_CURCODE_DIM |
|
|
Decision |
Provides information about retention decision rules. There is no hierarchy. |
WFR_DECISON_DIM |
WFR_DECI_DIM |
|
Department Service |
List of department service groups as defined by user. This dimension enables analysis of your workforce by groupings of the calculated service duration your employees have been with the department. There is no hierarchy. |
WFR_DEPTSVC_DIM |
WFI_DESE_DIM |
|
Education Level |
Enables you to analyze your workforce by highest education levels such as Bachelor level degree, Master level degree, and so on. There is no hierarchy. |
WFR_EDULVL_DIM |
WFI_EDLV_DIM |
|
Geography |
Represents the hierarchical relationship of geographic areas and locations for reporting purposes. The hierarchy is the Geography tree. |
WFR_GEO_DIM |
WFI_GEO_DIM |
|
Group |
Provides information about workforce groups. There is no hierarchy. |
WFR_GROUP_DIM |
WFR_GRUP_DIM |
|
Job |
Job dimension represents how unique an employee is with their job. There is no hierarchy for the Job dimension. |
WFR_JOB_DIM WFR_JOBATTR_DIM |
WFI_JOB_DIM |
|
Job Code |
Represents the categorization of jobs into types; such as executive, technical, administrative services, and so on. The hierarchy is the Job Code Tree. |
WFR_JCD_DIM WFR_JCDATTR_DIM |
WFI_JOCD_DIM |
|
Job Service |
List of service duration groups in a job code as defined by the user. This dimension is used to enable grouping of workforce by amount of time in the job. Job Service duration group can be defined in days, months or years. |
WFR_JOBSVC_DIM |
WFI_JOSE_DIM |
|
Last Pay Change |
Represents predefined brackets of calculated time in service since the last pay change, for comparison purposes. There is no hierarchy. |
WFR_PAYCHG_DIM |
WFI_PACH_DIM |
|
Last Promotion |
Represents predefined brackets of calculated time in service since the last promotion, for comparison purposes. There is no hierarchy. |
WFR_PROMO_DIM |
WFI_PROM_DIM |
|
Measure Fit |
Provides information about measures. There is no hierarchy. |
WFA_MEAFIT_DIM |
WFR_MEFT_DIM |
|
Metric |
Provides information about metrics. There is no hierarchy. |
WFR_METRIC_DIM |
WFR_METR_DIM |
|
Organization |
Represents the relationship of departments from an organizational reporting perspective. The hierarchy is the Organization Tree. |
WFR_ORG_DIM WFR_MGR_DIM |
WFI_ORG_DIM |
|
Personal |
Identifies the attributes of a person. There is no hierarchy for the Personal dimension. |
WFR_PERSON_DIM WFR_PERSNAT_DIM |
WFI_PER_DIM |
|
Previous Job Code |
Represents the employee's previous Job Code. The hierarchy is the Job Code Tree. |
WFR_PRJCD_DIM WFR_PRJCDAT_DIM |
WFI_PRJC_DIM |
|
Previous Organization |
Represents the employee's previous Organization. The hierarchy is the Organization Tree. |
WFR_PRORG_DIM WFR_PRMGR_DIM |
WFI_PROR_DIM |
|
Previous Salary Grade |
Represents the employee's previous Salary Grade. There is no hierarchy. |
WFR_PRSLGRD_DIM |
WFI_PRSG_DIM |
|
Retain Action |
Provides information about retain actions. There is no hierarchy. |
WFR_RETACT_DIM |
WFR_RTAC_DIM |
|
Retention Rule |
Provides information about retention rules. There is no hierarchy. |
WFR_RETENRU_DIM |
WFR_RERU_DIM |
|
Review Rating |
Represents the employee's performance rating from their salary/performance review. There is no hierarchy. |
WFR_REVRTG_DIM |
WFI_RERA_DIM |
|
Risk Rule |
Provides information about retention risk rules. There is a hierarchy from Risk Rule to Retention Factor to Retention Factor Type. |
WFR_RSKRU_DIM |
WFR_RIRU_DIM |
|
Salary Grade |
Represents the employee's salary grade. There is no hierarchy. |
WFR_SALGRD_DIM |
WFI_SAGR_DIM |
|
Salary Grade Duration |
Represents predefined brackets of calculated time in service in the present salary grade, for comparison purposes. There is no hierarchy. |
WFR_SLGDDUR_DIM |
WFI_SAGD_DIM |
|
Service |
Represents predefined brackets of calculated time in service for comparison purposes. There is no hierarchy for Service Group. |
WFR_SERVICE_DIM |
WFI_SERV_DIM |
|
Scenario |
Provides information about scenarios. There is no hierarchy. |
WFR_SENARIO_DIM |
WFR_SCEN_DIM |
|
Target Rate |
Provides information about compensation target rates. There is no hierarchy. |
WFR_TGTRATE_DIM |
WFR_TARA_DIM |
|
Time (Period) |
Includes the accounting periods for which data is reported. The hierarchy is Year, Quarter, Month, Week and Day. |
WFR_YEAR_DIM WFR_QTR_DIM WFR_MONTH_DIM WFR_DAY_DIM |
WFI_TIME_DIM |
|
Track Decision |
Provides information about retention track decisions. There is no hierarchy. |
WFR_TRCRSN_DIM |
WFR_TCRN_DIM |
|
Value Rule |
Provides information about retention value rules. There is no hierarchy. |
WFR_VALRU_DIM |
WFR_VARU_DIM |
Each dimension, and its corresponding table, is built by an Application Engine (AE) library called by the various Workforce Rewards scenario-building Application Engine processes (Market-Based Pricing, Retention Assignment, Workforce Simulation, and Compensation Planning and Simulation).
Data Mart Facts
The Workforce Rewards Data Mart contains six fact output tables.
|
Data Mart Fact Name |
Description |
Input Tables |
Output Tables |
|
WA_MC_SCEN_FACT |
The Market Compensation fact table is generated by an Application Engine program (WA_MC_SCEN), that is called by the Market-Based Pricing Application Engine process (WA_MBP), or run by itself. The purpose of WA_MC_SCEN is to populate the WA_MC_SCEN_FACT table in the Data Mart for use in decision reporting. |
WA_MKT_SCEN_TBL, WA_MKT_RATE_TBL , JOB_F00, WA_COMP_HST_F00, JOBCODE_D00, LOCATION_D00, SAL_GRADE_D00 |
WA_MC_SCEN_FACT, WA_MC_EMPL_FACT |
|
WA_EMPL_FACT |
The Employee fact table is generated by an Application Engine Library (WA_EMPL_FACT), that can be called by any program, but cannot be run by itself. The purpose of WA_EMPL_FACT is to populate the WA_EMPL_FACT table in the Data Mart. Each time this engine is called, it adds employee details to the WA_EMPL_FACT table, for any employees that the calling engine has. This optional table can then be used later in the reporting process to provide all the employee dimensions and attributes. |
WA_JOB_S00, JOBCODE_D00, LOCATION_D00, SAL_GRADE_D00, PERSONAL_D00, JOB_INTRDY_F00, EMPL_REVW_F00 |
WA_EMPL_FACT |
|
WA_RM_FACT |
The Retention fact table is generated by an Application Engine program (WA_RM_FACT) that is called by the Retention Assignment Application Engine process (WA_RM_ASSIGN). The purpose of WA_RM_FACT is to populate the two tables in the Data Mart for use in decision reporting. |
Uses TEMP tables of the following: WA_RM_SUM_F00, WA_RM_DET_C_F00, WA_RM_DET_R_F00, WA_RM_DET_V_F00, WA_RM_TRACK_F00, WA_RM_RETN_F00, WA_RM_RDCE_F00, WA_JOB_S00, LOCATION_D00 |
WA_RM_ASMT_FACT, WA_RM_DECN_FACT |
Each fact, and its corresponding table, is built by an Application Engine (AE) library that is called by the various Workforce Rewards scenario-building Application Engine processes (Market-Based Pricing, Retention Assignment).