---
title: Use parent-child relationships
description: "biGENIUS-X allows the creation of complex parent-child relationships for all modeling approaches.\nIn this article, we describe a use case based on the Microsoft SQL Server AdventureWorks sample database for each modeling approach.\nParent-child relationship use case\nIn the Adventure Works example database, the table HumanRessource.Employee contains the employee hierarchy in the OrganizationNode field."
---

[Skip to content](https://knowledge.bigenius-x.com/use-parent-child-relationships#main-content)

[Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en)

[![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/)

Open main navigation

Close main navigation

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new)
- [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal)

 How can we help you?

- There are no suggestions because the search field is empty.

1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en)
2. [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en)
3. [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)

# Use parent-child relationships

biGENIUS-X allows the creation of complex parent-child relationships for all modeling approaches.

In this article, we describe a use case based on the Microsoft SQL Server AdventureWorks sample database for each modeling approach.

### Parent-child relationship use case

In the Adventure Works example database, the table *HumanRessource.Employee* contains the employee hierarchy in the *OrganizationNode* field.

Let's explore our source data:

```
SELECT TOP (7) [BusinessEntityID]      ,[OrganizationNode]      , CAST([OrganizationNode] as VARCHAR(4000)) as DecryptedNode      ,[OrganizationLevel]      ,[JobTitle]  FROM [AdventureWorks2019].[HumanResources].[Employee]
```

![](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-07-2025-01-34-46-9929-PM.png?width=670&height=159&name=image-png-Oct-07-2025-01-34-46-9929-PM.png)

The *OrganizationNode* field is of type *hierarchyid*.

- Example: 0x5ADE

To understand it, we cast it in *VARCHAR(4000)*.

- Example: /1/1/3/

The hierarchy in this example is the following:

![](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-07-2025-01-40-02-5569-PM.png?width=418&height=186&name=image-png-Oct-07-2025-01-40-02-5569-PM.png)

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)

The purpose of building a parent-child relationship is to have a flattened view of the employee-manager relation:

![](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jan-08-2026-09-41-01-4884-AM.png?width=670&height=114&name=image-png-Jan-08-2026-09-41-01-4884-AM.png)

### Data Vault implementation

To implement the parent-child relationship with a Data Vault modeling, we need four Model Objects:

- An Employee **Stage**
- An Employee **Hub**
- An Employee **Satellite** to store employee last name, first name, ...
- A **Hierarchical Link**, which represents the parent-child relationship

#### Stage

Create an Employee **[Stage](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en)**with the wizard from the Employee source.

#### Hub

Create an Employee **[Hub](https://knowledge.bigenius-x.com/create-a-hub?hsLang=en)** with the wizard from the Employee Stage:

- Business Key = BusinessEntityID

#### Satellite

Create an Employee **[Satellite](https://knowledge.bigenius-x.com/create-a-satellite?hsLang=en)** with the wizard from the Employee Stage:

- Relationship to Employee Hub
- Map the Foreign Key with the term BusinessEntityID
- Select the attributes : 
    - FirstName
    - LastName
    - JobTitle

#### Hierarchical Link

Create an Employee\_Hierarchy [Hierarchical Link](https://knowledge.bigenius-x.com/create-a-hierachical-link?hsLang=en) with the wizard from the Employee Stage:

- Relationship to Employee Hub - Set the Relationship No to 2
- Rename the Relationship 2 Role to Manager
- Map the Employee Key with the term BusinessEntityID

Then manually:

- Rename the Alias of the Employee Stage to emp (for Employee)
- Add a second time the Employee Stage as Source:
  
    - Alias = man (for Manager)
    - Join Operator = Left Outer Join
    - Join Expression =

```
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')
```

#### Use case view

After generating, replacing the placeholders, deploying, and loading the data, the following statement can be executed to have the flattened view of the employee-manager relation:

```
SELECT hubEmp.BusinessEntityID as EmployeeBusinessEntityID,satemp.FirstName as EmployeeFirstName,satEmp.LastName as EmployeeLastName,satEmp.JobTitle as EmployeeJobTitle,hubMan.BusinessEntityID as ManagerBusinessEntityID,satMan.FirstName as ManagerFirstName,satMan.LastName as ManagerLastName,satMan.JobTitle as ManagerJobTitleFROM [RDV].[RDV_HUB_Employee] hubEmpLEFT JOIN [RDV].[RDV_SAT_Employee] satEmp ON hubEmp.Hub_HK = satEmp.Hub_HKLEFT JOIN [RDV].[RDV_HLNK_Employee_Hierarchy] link ON link.Employee_Employee_HK = hubEmp.Hub_HKLEFT JOIN [RDV].[RDV_HUB_Employee] hubMan ON link.Employee_Manager_HK = hubMan.Hub_HKLEFT JOIN [RDV].[RDV_SAT_Employee] satMan ON hubMan.Hub_HK = satMan.Hub_HKWHERE hubEmp.BusinessEntityID <> 0 --singletonORDER BY hubEmp.BusinessEntityID
```

![](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jan-08-2026-09-44-04-7879-AM.png?width=670&height=115&name=image-png-Jan-08-2026-09-44-04-7879-AM.png)

### Dimensional implementation

The dimensional implementation supports complex hierarchies, including mixed SCD configurations.

To implement the parent-child relationship with a Dimensional modeling, we need two Model Objects:

- An Employee **Stage**
- An Employee **Entity**

#### Stage

Create an Employee **[Stage](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en)**with the wizard from the Employee source.

#### Entity

Create an Employee [Entity](https://knowledge.bigenius-x.com/create-an-entity?hsLang=en) with the wizard from the Employee Stage:

- Business Key = BusinessEntityID
- Relationship to Entity Employee
- Rename the Role to Manager
- Don't map the Foreign Key

Then manually:

- Rename the Alias of the Employee Stage to emp (for Employee)
- Add a second time the Employee Stage as Source:
  
    - Alias = man (for Manager)
    - Join Operator = Left Outer Join
    - Join Expression =

```
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')
```

#### Use case view

After generating, replacing the placeholders, deploying, and loading the data, the following statement can be executed to have the flattened view of the employee-manager relation:

```
SELECT emp.EmployeeBusinessEntityID,emp.EmployeeFirstName,emp.EmployeeLastName,emp.EmployeeJobTitle,man.EmployeeBusinessEntityID as ManagerBusinessEntity,man.EmployeeFirstName as ManagerFirstName,man.EmployeeLastName as ManagerLastName,man.EmployeeJobTitle as ManagerJobTitleFROM [COR].[COR_EN_Employee] empLEFT JOIN [COR].[COR_EN_Employee] man ON emp.Employee_Manager_SK = man.Employee_SKWHERE emp.EmployeeBusinessEntityID <> 0 --singletonORDER BY emp.EmployeeBusinessEntityID
```

![](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jan-08-2026-09-51-23-4745-AM.png?width=670&height=127&name=image-png-Jan-08-2026-09-51-23-4745-AM.png)

 

- [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content)

    - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines)
    - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working)
    - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account)
- [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en)
- [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en)
- [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content)

    - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration)
    - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions)
    - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features)
    - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects)
    - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches)
    - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections)
    - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview)
    - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps)
    - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms)
    - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping)
    - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships)
    - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality)
    - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes)
    - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling)
    - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts)
    - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings)
    - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace)
- [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content)

    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)
    - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders)
    - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)
    - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment)
    - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline)
    - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)
    - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment)
    - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)
    - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow)
    - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type)
    - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)
    - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms)
- [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content)

    - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations)
- [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content)

    - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches)
    - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)
    - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules)
    - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules)
- [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en)
- [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content)

    - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application)
    - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application)
    - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators)
- [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content)

    - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs)
    - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits)

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en)
- [Customer Portal](https://knowledge.bigenius-x.com/customer-portal)
- [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login)

[![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG