Skip to content
  • There are no suggestions because the search field is empty.

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

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

    Default Terms

    A Hierarchy Flatten  Model Object will include the following Default Terms: