HR Generic Hierarchy Load Types Data Load Parameters
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.