Create a Hierarchical Link

A Model Object of type Hierarchical Link can be created in the Silver Layer.

What is a Hierarchical Link

In a Data Vault model, a Hierarchical Link is a many-to-many recursive relationship structure, housing a unique list of associations across two or more business keys at different levels of data. It represents hierarchies inside the same Hub.

Use case example

In the Adventure Works example database, the table HumanRessource.Employee contains the hierarchy between employees in the field OrganizationNode. With a Hierarchical Link, we can, for example, model the Relationship between an employee and his manager.

In this example, we need first to create a Raw Vault Composite which will be the Source of the Hierarchical Link with the following result:

--SQL code of the Raw Vault Composite
SELECT 
    man.BusinessEntityID as ManagerBusinessEntityID
    , man.JobTitle as ManagerJobTitle
    , pman.FirstName as ManagerFirstName
    , pman.LastName as ManagerLastName
    , emp.BusinessEntityID as EmployeeBusinessEntityID
    , emp.JobTitle as EmployeeJobTitle
    , pemp.FirstName as EmployeeFirstName
    , pemp.LastName as EmployeeLastName
FROM [AdventureWorks2019].[HumanResources].[Employee] emp
LEFT JOIN [AdventureWorks2019].[HumanResources].[Employee] man 
    ON CASE 
          WHEN emp.OrganizationLevel IS NULL THEN NULL
           WHEN emp.OrganizationLevel = 1 THEN '0'
           ELSE CONCAT(LEFT(CAST(emp.OrganizationNode as nvarchar(4000)), 
                LEN(CAST(emp.OrganizationNode as nvarchar(4000))) - CHARINDEX('/', 
                REVERSE(CAST(emp.OrganizationNode as nvarchar(4000))), 2)),'/')
       END
   = ISNULL(CAST(man.OrganizationNode as nvarchar(4000)),'0')
LEFT JOIN [AdventureWorks2019].[Person].[Person] pman 
    ON pman.BusinessEntityID = man.BusinessEntityID
LEFT JOIN [AdventureWorks2019].[Person].[Person] pemp 
    ON pemp.BusinessEntityID = emp.BusinessEntityID

Creation steps

There are two possibilities for creating a Hierarchical Link Model Object:

Create a Model Object with the Wizard:
- Recommended possibility
- Use the Wizard Create a Hierarchical Link from a Stage or a Raw Vault Composite that contains the following steps:
  - Create Model Object
  - Create Dataflow
  - Create Relations: number = {null}
  - Map Foreign Key Terms
  - Add to View

Create a Model Object manually:
- Not recommended possibility
- Create a Model Object of type Hierarchical Link in the Silver Layer
- Create a Dataflow Set
- Add a Model Object to the Dataflow Set: add the Stage or Raw Vault Composite Model Object

- Add a Relationship to the first corresponding Hub (here, the Hub Employee)
- Edit the Foreign Key Terms: set them as Business Key
- Add a second relationship to the same Hub (here, the Hub Employee)
- Edit the Role names (See Have several Relationships to the same Model Object) with:
  - First Relationship with the Hub: Employee
  - Second Relationship with the same Hub: Manager
- Map the Foreign Key Terms: select the target mode and manually map the Foreign Key Terms to the corresponding Source Terms:
  - FK for the Role of Employee with the EmployeeBusinessEntityID
  - FK for the Role of Manager with the ManagerBusinessEntityID

In both cases, as some manager employees do not have managers, please set the FK for the Role Manager as Nullable.

Properties

A Hierarchical Link Model Object can be configured through the following Properties:

Implementation Type
Deduplication Method or Optimization Method (Only for Spark Generator)
Deduplication Partition or Optimization Method Columns (Only for Spark Generator)
File format (Only for Spark Generator)

Default Terms

A Hierarchical Link Model Object will include the following Default Terms: