Understanding the Age Band dimension in HR Analytics

Age Band dimension:

To enable data analysis based on Employee ages, Oracle BI HR Analytics  provides an option to configure multiple Age Bands which correspond to ranges of years or months grouped under a common name.   For example, out of the box there is an Age Band covering all Employees under the age of 21 where the range of months would be entered as 0 through 252.


The band data configuration is stored in data warehouse dimension tables.    For the Person Age band, the dimension table is W_AGE_BAND_D.  The table has two levels:

    LEVEL_ID = AGE_BAND. This level defines the age bands.
    LEVEL_ID = AGE. This level defines the age (in months) for a person.

Each band dimension uses a CSV file to define the band ranges. The CSV files that you need to configure for the Age Band dimension is: file_age_band.csv. All CSV files for the band dimensions, including the common file, are stored in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).


Note:
If these bands are changed after the initial ETL run, the data in subsequent ETL runs will be inaccurate. Also, any changes to these files will not retroactively change data that has already run through the system.

All band dimensions use one common CSV file, file_row_gen_band.csv, to generate the individual elements that fall into each band. No configuration is required of this common file.

To configure the Age Band file, open the CSV file in a text editor and provide values for each column below for each band required.

Column: AGE_BAND_CODE
  
A unique short name for the band.  For example: "30_to_40"

Column: AGE_BAND_DESC
  
A more descriptive name for the band.  For example: "Age 30 thru 40".  This is the value that would normally be displayed on reports for users.

Column: BAND_MIN_MONTHS

This is the minimum number of months to qualify for the band (inclusive). If this field is blank, min years is required. If both min months and min years are present, min years is ignored.

Column: BAND_MAX_MONTHS

This is the maximum number of months to qualify for the band (non inclusive). If this field is blank and max years is blank then 1800 is implied, if both max months and max years are present, max years is ignored.

Column: BAND_MIN_YEARS

This is the minimum number of years to qualify for the band (inclusive). If this field is blank, min months is required, if both min months and min years are present, min years is ignored.

Column: BAND_MAX_YEARS

This is the maximum number of years to qualify for the band (non inclusive). If this field is blank and max months is blank then 150 is implied, if both max months and max years are present, max years is ignored.

When defining the data in the CSV file, ensure that:

•     The first band starts with a value of 0 for BAND_MIN_MONTHS or BAND_MIN_YEARS.
•     The last band does not have a value for BAND_MAX_MONTHS or BAND_MAX_YEARS.
•     The bands are contiguous to avoid losing rows in reports, which display by Age Band

ETL Processing for Age Band:

W_AGE_BAND_D is loaded using mapping SIL_AgeBandDimension which reads the CSV file and populates W_AGE_BAND_D.

The Age Band dimension is then used during the loading of the Workforce Event Fact (W_WRKFC_EVT_MONTH_F).   First, it is combined with the W_WRKFC_EVT_F base fact table to generate rows in a table called W_WRKFC_EVT_AGE_F.   

The age fact contains an initial row for each employee row plus one row each time an employee's assignment crosses over from one age band to the next band in W_AGE_BAND_D.  

This fact is refreshed for an employee whenever there is a change to birth date on
the original hire record (or the first job record if the hire occurred before the fact initial extract date).


Initial Load Sessions
 PLP_WorkforceEventFact_Age_Full (loads new records)
 
Incremental Load Sessions

PLP_WorkforceEventFact_Age_Mntn (deletes records to be refreshed or obsolete)
PLP_WorkforceEventFact_Age (loads changed records) 

After the age fact table is loaded, it is then merged  with the Workforce Event Fact and the Period of Work Band fact to create W_WRKFC_EVT_MERGE_F which is then used to populate the W_WRKFC_EVT_MONTH_F table.