Create a Hierachy Flatten
A Model Object of type Hierarchy Flatten can be created in the Business Vault Layer.
What is a Hierarchy Flatten
In a Data Vault model, a Hierarchy Flatten transforms complex, multi-level source hierarchies into a single, flat target table.
This approach resolves the "Bill of Material (BOM) explosion" problem by storing all hierarchical relationships in a simplified structure, making data easier to query and manage.
It eliminates the need to repeatedly traverse nested levels, improving performance and clarity in reporting.
The Hierarchy Flatten Model Object Type is only available for the Spark Generators. It will be available soon for all Generators.
Use case example
In the Adventure Works example database, the table HumanRessource.Employee contains the hierarchy of employees within the OrganizationNode field.
With a Hierarchy Flatten, we can display this hierarchy in a simplified structure.
Source data
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 a hierarchyid type.
- 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)
Data vault model
In this example, we need to create:
- A Hierarchical Link in the Raw Vault layer:
- See the use case described in the article Hierarchical Link to create it
- A Link Satellite in the Raw Vault layer based on the following SQL code:
-- Link Satellite
SELECT
[emp].[BusinessEntityID] AS [FK_Employee_Hierarchy_FK_Employee_BusinessEntityID]
,[man].[BusinessEntityID] AS [FK_Employee_Hierarchy_FK_Employee_Manager_BusinessEntityID]
,CAST(CAST(cast([emp].[OrganizationNode] as varchar(4000)) AS hierarchyid) as nvarchar(4000)) AS [EmployeeNode]
,[emp].[OrganizationLevel] AS [EmployeeLevel]
FROM [MS_SQL_HierarchyFlatten].[SA].[STG_ST_Employee_Result] AS [emp]
LEFT OUTER JOIN [MS_SQL_HierarchyFlatten].[SA].[STG_ST_Employee_Result] AS [man]
ON 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')
WHERE [emp].[BusinessEntityID] Is not null
- A Current View in the Raw Vault layer based on a combination of the Hierarchical Link and the Link Satellite. It is based on the following code:
-- Current View
SELECT
[s1].[FK_Employee_BusinessEntityID] AS [FK_Employee_BusinessEntityID]
,[s1].[FK_Employee_Manager_BusinessEntityID] AS [FK_Employee_Manager_BusinessEntityID]
,[s2].[EmployeeNode] AS [EmployeeNode]
,[s2].[EmployeeLevel] AS [EmployeeLevel]
FROM [MS_SQL_HierarchyFlatten].[RDV].[RDV_HLNK_Employee_Hierarchy_Result] AS [s1]
JOIN [MS_SQL_HierarchyFlatten].[RDV].[RDV_LSAT_Employee_Hierarchy_Result] AS [s2]
-- The join expression is generated by the generator
ON ([s2].[Link_HK] = [s1].[Link_HK])
AND ([s2].[BG_ValidToTimestamp] = '99991231')
- The Hierarchy Flatten in the Business Vault layer is based on the Current View. The code is:
SELECT
[emp].[FK_Employee_BusinessEntityID] AS [FK_Employee_BusinessEntityID]
,ISNULL([man].[FK_Employee_Manager_BusinessEntityID],0) AS [FK_Employee_Manager_BusinessEntityID]
FROM [MS_SQL_HierarchyFlatten].[RDV].[RDV_Current_Employee] AS [emp]
LEFT OUTER JOIN [MS_SQL_HierarchyFlatten].[RDV].[RDV_Current_Employee] AS [man]
ON [emp].[FK_Employee_BusinessEntityID] = [man].[FK_Employee_BusinessEntityID]
Load result
After modeling and loading the different Model Objects in this use case, the Hierarchy Flatten table contains the following values for the 7 example employees:
SELECT TOP (7) [BG_LoadTimestamp]
,[FK_Employee_BusinessEntityID]
,[FK_Employee_Manager_BusinessEntityID]
FROM [BV].[BDV_FlattenHierarchy_Flatten_Employee]
order by 2
With this result, it is straightforward to construct a hierarchy diagram with a reporting tool, similar to the diagram displayed in the source data analysis chapter.
Creation steps
There is one possibility for creating a Hierarchy Flatten Model Object:
- Create a Model Object manually:
- Create a Model Object of type Hierarchy Flatten in the Gold Layer
- Create a Dataflow Set
- Add a Model Object to the Dataflow Set: add twice the Current View Model Object
-
-
- Add a Join expression between the two Business Keys
- Automap:
- The child Business Key from the first Current View
- The parent Business Key from the second Current View
-
Properties
A Hierarchy Flatten Model Object can be configured through the following Properties:
- File format (Only for Spark Generator)
Default Terms
A Hierarchy Flatten Model Object will include the following Default Terms: