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 RPD 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

Description of the illustration GUID-38C63861-ED7F-472E-8358-7518DA9267B2-default.png

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.