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

Description of the illustration hr_generic_hierarchy_physical_model.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.