Model Object Types
      Back to home
      1. biGENIUS-X Help Center
      2. Generators
      3. Model Object Types

      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 Stage Composite which will be the Source of the Hierarchical Link with the following result:

      --SQL code of the Stage 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:

          • 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: 

        Default Terms

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

        Business Rules

        The following Business Rules are checked during the Model Object creation: