Skip to content
  • There are no suggestions because the search field is empty.

Use parent-child relationships

biGENIUS-X allows the creation of complex parent-child relationships for all modeling approaches.

In this article, we describe a use case based on the Microsoft SQL Server AdventureWorks sample database for each modeling approach.

Parent-child relationship use case

In the Adventure Works example database, the table HumanRessource.Employee contains the employee hierarchy in the OrganizationNode field.

Let's explore our source data:

SELECT TOP (7) [BusinessEntityID]
      ,[OrganizationNode]
      , CAST([OrganizationNode] as VARCHAR(4000)) as DecryptedNode
      ,[OrganizationLevel]
      ,[JobTitle]
  FROM [AdventureWorks2019].[HumanResources].[Employee]

The OrganizationNode field is of type hierarchyid.

  • Example: 0x5ADE

To understand it, we cast it in VARCHAR(4000).

  • Example: /1/1/3/

The hierarchy in this example is the following:

To construct it from the OrganizationNode field value, let's take an example:

  • Node = /1/1/3/. 2 parts:
    • /1/1/ = Node of the manager (3- Engineering Manager here)
    • /3/ = counter of the employee (third employee of the manager)

The purpose of building a parent-child relationship is to have a flattened view of the employee-manager relation:

Data Vault implementation

To implement the parent-child relationship with a Data Vault modeling, we need four Model Objects:

  • An Employee Stage
  • An Employee Hub
  • An Employee Satellite to store employee last name, first name, ...
  • A Hierarchical Link, which represents the parent-child relationship

Stage

Create an Employee Stage with the wizard from the Employee source.

Hub

Create an Employee Hub with the wizard from the Employee Stage:

  • Business Key = BusinessEntityID

Satellite

Create an Employee Satellite with the wizard from the Employee Stage:

  • Relationship to Employee Hub

  • Map the Foreign Key with the term BusinessEntityID

  • Select the attributes :
    • FirstName
    • LastName
    • JobTitle

Hierarchical Link

Create an Employee_Hierarchy Hierarchical Link with the wizard from the Employee Stage:

  • Relationship to Employee Hub - Set the Relationship No to 2
  • Rename the Relationship 2 Role to Manager
  • Map the Employee Key with the term BusinessEntityID

Then manually:

  • Rename the Alias of the Employee Stage to emp (for Employee)

  • Add a second time the Employee Stage as Source:

    • Alias = man (for Manager)
    • Join Operator = Left Outer Join
    • Join Expression =
CASE 
    WHEN emp.OrganizationLevel IS NULL THEN NULL
    WHEN emp.OrganizationLevel = 1 THEN '0'
    ELSE CONCAT(LEFT(CAST(CAST(emp.OrganizationNode AS hierarchyid) as nvarchar(4000)), 
        LEN(CAST(CAST(emp.OrganizationNode AS hierarchyid) as nvarchar(4000))) - CHARINDEX('/', 
        REVERSE(CAST(CAST(emp.OrganizationNode AS hierarchyid) as nvarchar(4000))), 2)),'/')
     END
   = ISNULL(CAST(CAST(man.OrganizationNode AS hierarchyid) as nvarchar(4000)),'0')

Use case view

After generating, replacing the placeholders, deploying, and loading the data, the following statement can be executed to have the flattened view of the employee-manager relation:

SELECT hubEmp.BusinessEntityID as EmployeeBusinessEntityID,
satemp.FirstName as EmployeeFirstName,
satEmp.LastName as EmployeeLastName,
satEmp.JobTitle as EmployeeJobTitle,
hubMan.BusinessEntityID as ManagerBusinessEntityID,
satMan.FirstName as ManagerFirstName,
satMan.LastName as ManagerLastName,
satMan.JobTitle as ManagerJobTitle
FROM [RDV].[RDV_HUB_Employee] hubEmp
LEFT JOIN [RDV].[RDV_SAT_Employee] satEmp ON hubEmp.Hub_HK = satEmp.Hub_HK
LEFT JOIN [RDV].[RDV_HLNK_Employee_Hierarchy] link ON link.Employee_Employee_HK = hubEmp.Hub_HK
LEFT JOIN [RDV].[RDV_HUB_Employee] hubMan ON link.Employee_Manager_HK = hubMan.Hub_HK
LEFT JOIN [RDV].[RDV_SAT_Employee] satMan ON hubMan.Hub_HK = satMan.Hub_HK
WHERE hubEmp.BusinessEntityID <> 0 --singleton
ORDER BY hubEmp.BusinessEntityID

Dimensional implementation

The dimensional implementation supports complex hierarchies, including mixed SCD configurations.

To implement the parent-child relationship with a Dimensional modeling, we need two Model Objects:

  • An Employee Stage
  • An Employee Entity

Stage

Create an Employee Stage with the wizard from the Employee source.

Entity

Create an Employee Entity with the wizard from the Employee Stage:

  • Business Key = BusinessEntityID
  • Relationship to Entity Employee
  • Rename the Role to Manager
  • Don't map the Foreign Key

Then manually:

  • Rename the Alias of the Employee Stage to emp (for Employee)

  • Add a second time the Employee Stage as Source:

    • Alias = man (for Manager)
    • Join Operator = Left Outer Join
    • Join Expression =
CASE 
    WHEN emp.OrganizationLevel IS NULL THEN NULL
    WHEN emp.OrganizationLevel = 1 THEN '0'
    ELSE CONCAT(LEFT(CAST(CAST(emp.OrganizationNode AS hierarchyid) as nvarchar(4000)), 
        LEN(CAST(CAST(emp.OrganizationNode AS hierarchyid) as nvarchar(4000))) - CHARINDEX('/', 
        REVERSE(CAST(CAST(emp.OrganizationNode AS hierarchyid) as nvarchar(4000))), 2)),'/')
     END
   = ISNULL(CAST(CAST(man.OrganizationNode AS hierarchyid) as nvarchar(4000)),'0')

Use case view

After generating, replacing the placeholders, deploying, and loading the data, the following statement can be executed to have the flattened view of the employee-manager relation:

SELECT emp.EmployeeBusinessEntityID,
emp.EmployeeFirstName,
emp.EmployeeLastName,
emp.EmployeeJobTitle,
man.EmployeeBusinessEntityID as ManagerBusinessEntity,
man.EmployeeFirstName as ManagerFirstName,
man.EmployeeLastName as ManagerLastName,
man.EmployeeJobTitle as ManagerJobTitle
FROM [COR].[COR_EN_Employee] emp
LEFT JOIN [COR].[COR_EN_Employee] man ON emp.Employee_Manager_SK = man.Employee_SK
WHERE emp.EmployeeBusinessEntityID <> 0 --singleton
ORDER BY emp.EmployeeBusinessEntityID