To control how the HR Generic Hierarchy is loaded, there are several parameters that you must set. This topic explains those parameters.
The following parameters control how the HR Generic Hierarchy is loaded:
The chosen key. The HR Generic Hierarchy may be configured to load from the Department Hierarchy (default configuration) or from the HR Position Hierarchy or a custom hierarchy. The chosen key must be indicated to enable facts to join with a custom hierarchy.
The parameter that controls what hierarchies get loaded into the HR Generic Hierarchy. This is used for automatic loading from Department or HR Position Hierarchies for reporting.
The parameter that controls whether transfers are processed from the Live (Workforce Deployment) or Frozen Snapshot fact.
Optional or Mandatory
This is a mandatory step, if the HR Generic Hierarchy is used. If this step is skipped, then the default behavior is to load all Department Hierarchies (current versions only) into the HR Generic Hierarchy.
Applies to
All adapters.
Task description in detail
The HR Generic Hierarchy consists of one logical table in the repository which enables reporting and drilling down the chosen hierarchy. Underlying the model is a number of physical tables, some of which may need to be custom loaded if a custom hierarchy is to be used instead of the standard Department or HR Position Hierarchy as delivered.
Parameter: HR Generic Hierarchy Load Type
The following table illustrates the parameter choices. The amount of customization required may vary depending on the parameter selection. Customization is not required for the parameter options for loading the delivered Department or HR Position Hierarchies.
In the table, the text in bold font indicates parameter options where ETL automatically handles the requirement, without the need for customization. The remaining options in the table require customization.
For custom-loaded hierarchies, there is a choice between loading the parent-child relationships (where the existing ETL flattens the hierarchy from those parent-child relationships) or directly loading the flattened hierarchy. In each case, the required fact key to reference the hierarchy must be specified by the parameter selection.
Parameter Value | Fact Key* | ETL Configuration | ||
---|---|---|---|---|
Attributes | Parent-Child | Hierarchy | ||
Department (default) |
Department |
Loaded from respective dimension |
Loaded from respective dimension |
Flattened from parent child table |
HR Position |
HR Position |
|||
Custom Department Relationships |
Department |
Customer must load Node/Hierarchy/Version Attributes |
Customer must load the Node Parent-Child Relationships |
Flattened from parent child table |
Custom HR Position Relationships |
HR Position |
|||
Custom HR Assignment Relationships |
HR Assignment |
|||
Custom HR Person Relationships |
HR Person |
|||
Custom Other Relationships |
Custom |
|||
Custom Department Flattened |
Department |
Customer must load Node/Hierarchy/Version Attributes |
Not Required |
Customer must load the Flattened Hierarchy Table |
Custom HR Position Flattened |
HR Position |
|||
Custom HR Assignment Flattened |
HR Assignment |
|||
Custom HR Person Flattened |
HR Person |
|||
Custom Other Flattened |
Custom |
* The fact keys will be the corresponding dimension type 1 key
Parameter: HR Generic Hierarchy List
The hierarchy list allows a subset of hierarchies to be loaded into the generic hierarchy tables by passing a comma separated list of hierarchies.
Select the relevant types to be extracted.
To load all hierarchies pass the default value 'ALL'
For Department Hierarchies the hierarchy code used is
Fusion: Tree Code
E-Business Suite: Hierarchy Structure Id
Peoplesoft: Tree Name
For HR Position Hierarchies the hierarchy code used is
Fusion: Tree Code
E-Business Suite: Hierarchy Structure Id
Peoplesoft: Tree Name
For custom loaded hierarchies this parameter is not required as the custom ETL task should take care of which hierarchies to load
Parameter: HR Generic Hierarchy Transfer Source
The transfer source determines whether the transfers fact for Generic Hierarchy 1 is loaded from the live or frozen workforce fact. Valid values are LIVE or FROZEN.
Physical Model
If the hierarchy is sourced from the dimensions delivered (Department or HR Position), then these physical tables are automatically loaded. However if the hierarchy must be sourced from a custom hierarchy, some amount of customization may be required to load these physical tables.
The following table gives a description and example ETL task for loading each part of this model. A more detailed model is given in a later table, with grain information and column descriptions.
Table | Description | Example ETL Task |
---|---|---|
W_HR_GEN01_REL_D |
Parent-child node relationships for each hierarchy. |
PLP_HRGenericHierarchy_HierarchyRelationships |
W_HR_GEN01_RH |
Row flattened hierarchy table. |
PLP_HRGenericHierarchy_Flatten (no example for direct load) |
W_HR_GEN01_HRCHY_D |
Hierarchy attributes, such as Hierarchy Name and Primary Structure Flag. |
PLP_HRGenericHierarchy_Hierarchy |
W_HR_GEN01_HRCHY_D_TL |
Translated hierarchy attributes. |
PLP_HRGenericHierarchy_Hierarchy_Translate |
W_HR_GEN01_VERSION_D |
Hierarchy version information such as version number, date from/to. |
PLP_HRGenericHierarchy_Version |
W_HR_GEN01_NODE_D |
Hierarchy node attributes, such as Name. Several flex placeholder columns available for additional details. |
PLP_HRGenericHierarchy_HierarchyNodes |
W_HR_GEN01_NODE_D_TL |
Translated hierarchy node attributes. |
PLP_HRGenericHierarchy_HierarchyNodes_Translate |
W_WRKFC_EVT_F* |
References the hierarchy with HR_GEN01_WID and HR_GEN01_PRV_WID for current and previous nodes. |
SIL_WorkforceEventFact |
*If the custom hierarchy nodes are keyed by something other than the department/HR Position/HR Assignment/HR Person dimension type 1 keys, then the workforce fact must be altered to reference the correct hierarchy node.
Parent Child Table
Table Name: W_HR_GEN01_REL_D
Grain: One row per node per hierarchy per period (or version).
Columns:
Column | Description |
---|---|
USER_WID/GUID | Optional user key, if using list style hierarchy. |
ROLE_WID/CODE | Optional role code, if using list style hierarchy (future use only). |
NODE_WID | Type 1 key for node. |
PARENT_NODE_WID | Type 1 key for parent node. |
HIERARCHY_WID | Allows for multiple hierarchies. |
HIERARCHY_VERSION_WID | Informational only. Versions should be driven by effective dates. |
EFFECTIVE_FROM_DT EFFECTIVE_TO_DT |
Synched to version from/to dates as for Department, HR Position. Rapidly changing as for Supervisor |
System columns | Standard OBIA system columns. |
Row Flattened Hierarchy Table
Table Name: W_HR_GEN01_RH
Grain: One row per node per ancestor node per hierarchy per period (or version).
Columns
Column | Description |
---|---|
USER_WID/GUID | Optional user key, if using list style hierarchy. |
ROLE_WID/CODE | Optional role code, if using list style hierarchy (future use only). |
NODE_WID | Type 1 key for node. |
PARENT_NODE_WID | Type 1 key for parent node. |
NODE_LEVEL | Hierarchy level. |
PARENT_NODE_LEVEL | Parent level |
RELATIVE_LEVEL | Difference between Node and Parent Levels. |
PARENT_SUB1–15_NODE_WID | Type 1 key for all subordinate nodes at each level relative to the parent node. |
HIERARCHY_WID | Allows for multiple hierarchies. |
HIERARCHY_VERSION_WID | Informational only. Versions should be driven by effective dates. |
EFFECTIVE_FROM_DT EFFECTIVE_TO_DT |
Synched to version from/to dates as for Department, HR Position. Rapidly changing as for Supervisor |
IS_LEAF | 1/0 for whether or not node is a leaf. |
System columns | Standard OBIA system columns. |
Hierarchy Attributes Table
Table Name: W_HR_GEN01_HRCHY_D
Grain: One row per node.
Columns:
Column | Description |
---|---|
ROW_WID | Type 1 key for hierarchy. |
HIERARCHY_CODE | Source identifier for hierarchy. |
PRIMARY_FLAG | Whether hierarchy is primary.. |
System columns | Standard OBIA system columns. |
Hierarchy Attributes Translation Table
Table Name: W_HR_GEN01_HRCHY_D_TL
Grain: One row per node.
Columns:
Column | Description |
---|---|
ROW_WID | Type 1 key for hierarchy. |
HIERARCHY_NAME | Name. |
HIERARCHY_DESCRIPTION | Description. |
System columns | Standard OBIA system columns. |
Version Attributes Table
Table Name: W_HR_GEN01_VERSION_D
Grain: One row per node.
Columns:
Column | Description |
---|---|
ROW_WID | Type 1 key for version.. |
HIERARCHY_WID | Type 1 key for hierarchy. |
HIERARCHY_VERSION_CODE | Name. |
HIERARCHY_VERSION_NUMBER | Version number. |
System columns | Standard OBIA system columns. |
Node Attribute Base Table
Table Name: W_HR_GEN01_NODE_D
Grain: One row per node.
Columns:
Column | Description |
---|---|
ROW_WID | Type 1 key for node. |
NODE_CODE | Source identifier for node. |
NODE_NAME | Name. |
NODE_DESCRIPTION | Description. |
NODE_NUMBER | Character number, for example, Organization, Position, Person, or Assignment number. |
NODE_ATTR1-30_CHAR NODE_ATTR1-20_NUM NODE_ATTR1-10_DATE |
Flex attributes. |
EFFECTIVE_FROM_DT EFFECTIVE_TO_DT |
Synched to version from/to dates as for Department, HR Position. Rapidly changing as for Supervisor |
System columns | Standard OBIA system columns. |
Node Attribute Translation Table
Table Name: W_HR_GEN01_NODE_D_TL
Grain: One row per node.
Columns:
Column | Description |
---|---|
ROW_WID | Type 1 key for node. |
NODE_NAME | Name. |
NODE_DESCRIPTION | Description. |
NODE_ATTRI-30_CHAR_NAME | Flex attributes. |
LANGUAGE_CODE SRC_LANGUAGE_CODE |
Language. |
System columns | Standard OBIA system columns. |
Dependency
None.