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 
    emp.BusinessEntityID as EmployeeBusinessEntityID
    , emp.JobTitle as EmployeeJobTitle
    , pemp.FirstName as EmployeeFirstName
    , pemp.LastName as EmployeeLastName
    , man.BusinessEntityID as ManagerBusinessEntityID
    , man.JobTitle as ManagerJobTitle
    , pman.FirstName as ManagerFirstName
    , pman.LastName as ManagerLastName
FROM [AdventureWorks2019].[HumanResources].[Employee] man
LEFT JOIN [AdventureWorks2019].[HumanResources].[Employee] emp 
    ON CAST(man.OrganizationNode as nvarchar(4000)) = SUBSTRING(CAST(emp.OrganizationNode as nvarchar(4000)),1,3)
LEFT JOIN [AdventureWorks2019].[Person].[Person] pman 
    ON pman.BusinessEntityID = man.BusinessEntityID
LEFT JOIN [AdventureWorks2019].[Person].[Person] pemp 
    ON pemp.BusinessEntityID = emp.BusinessEntityID
WHERE CAST(man.OrganizationNode as nvarchar(4000)) <> CAST(emp.OrganizationNode as nvarchar(4000))

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

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:  

• BG_LoadTimestamp

• BG_SourceSystem

• Link_HK