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:
- Create a Model Object with the Wizard:
- Recommended possibility
- Use the Wizard Create a Hierarchical Link from a Stage or a Stage 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 Stage 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:
-
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:
Business Rules
The following Business Rules are checked during the Model Object creation:
- Dataflows amount: min = {1}, max = {null}
- Dataflow Sets amount: min = {1}, max = {null}
- Dataflow Set Model Objects amount: min = {1}, max = {null}
- Relationships amount: min = {2}, max = {null}
- Terms amount: exclude Default Terms = {true}, exclude Business Keys = {true}, exclude Identities = {false}, exclude Foreign Keys = {false}, exclude unmapped Terms = {false}, min = {null}, max = {0}
- Supported Term Data Types: exclude Default Terms = {true}, exclude Business Keys = {false}, exclude Identities = {false}, exclude Foreign Keys = {false}, exclude unmapped Terms = {false}
- Business Key Terms amount: min = {1}, max = {null}
- Identity Terms amount: min = {1}, max = {1}
- Identity Terms not nullable
- Supported Implementation Types: supported Implementation Types = {Permanent}
- Supported Deduplication Methods: supported deduplication Methods = {None, Distinct, Partition}
- Supported Load Cachings: supported Load Cachings = {Hashing}