--- title: Hierarchy Flatten description: A Hierarchy Flatten resolves a parent-child hierarchy into one row per ancestor and descendant, at every level, in a table of the Business Vault layer. --- [Skip to content](https://knowledge.bigenius-x.com/hierarchy-flatten#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. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) # Hierarchy Flatten **Applies to** Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Oracle Architecture: Data Vault A Hierarchy Flatten resolves a parent-child hierarchy into one row per ancestor and descendant, at every level, in a table of the Business Vault layer. Use it to report on multi-level hierarchies such as bills of materials or organization charts without recursive queries. After each load, the table holds the complete flattened hierarchy of the current data. Fabric Warehouse does not support Hierarchy Flatten. Validation reports the warning `Model object has an unsupported model object type for the Fabric Warehouse platform.` and generates no objects for it. ### Sources and relationships A Hierarchy Flatten reads a [Current View](https://knowledge.bigenius-x.com/current-view?hsLang=en) or a [BusinessVault Composite](https://knowledge.bigenius-x.com/businessvault-composite?hsLang=en) that holds the parent-child pairs, usually a Current View on a [Hierarchical Link](https://knowledge.bigenius-x.com/hierarchical-link?hsLang=en) and its Link Satellite. It has one dataflow with one dataflow set, and the dataflow set contains the same source twice. Business Hub, Business Link, Business Satellite, Business Link Satellite, BusinessVault Composite and [Vault Out](https://knowledge.bigenius-x.com/vault-out?hsLang=en) can read it; in a Vault and Mart project, Mart Dimension, Mart Fact and Mart Composite can read it too. ### Create a Hierarchy Flatten No wizard creates a Hierarchy Flatten. Create it manually: 1. [Create a model object](https://knowledge.bigenius-x.com/create-a-model-object?hsLang=en) of type Hierarchy Flatten in the Business Vault layer. 2. [Create a dataflow set](https://knowledge.bigenius-x.com/create-a-data-flow-set?hsLang=en) and [add the source](https://knowledge.bigenius-x.com/add-a-model-object-into-a-data-flow-set?hsLang=en) twice, as `s1` and `s2`. 3. Set the join of `s2`: its parent key equals the child key of `s1`, for example `s2.common_item_parent_HK = s1.common_item_child_HK`. 4. [Map the terms](https://knowledge.bigenius-x.com/map-a-source-term-with-a-target-term?hsLang=en): the parent terms from `s1`, the child terms and the other terms from `s2`. The generator builds the flattening from this dataflow set: - Each row of `s1` is a starting pair, taken as it is. A where clause of the dataflow set filters these starting rows. - For each pair found so far, the join to `s2` finds the children of its child, again and again until no level is left. - Each found row keeps the parent of the starting pair and takes the child and the other mapped terms from `s2`. The result has one row for every ancestor and each of its descendants. ### Properties - [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en): `Cached` only. Each load empties the table and fills it again. - [File Format](https://knowledge.bigenius-x.com/file-format-model-object?hsLang=en): on Databricks, Databricks dbt and Fabric Lakehouse. ### Default Terms - [`BG_LoadTimestamp`](https://knowledge.bigenius-x.com/bg_loadtimestamp?hsLang=en): the load that filled the table; the same for all rows. - [`BG_SourceSystem`](https://knowledge.bigenius-x.com/bg_sourcesystem?hsLang=en). - [`BG_DataQuality_ID`](https://knowledge.bigenius-x.com/bg-dataquality-id?hsLang=en), [`BG_ErrorCount`](https://knowledge.bigenius-x.com/bg_errorcount?hsLang=en) and [`BG_ErrorDescription`](https://knowledge.bigenius-x.com/bg_errordescription?hsLang=en): hidden terms of the data quality objects. `BG_DataQuality_ID` is the identity term. ### Generated objects These are the names on Microsoft SQL Server for the Hierarchy Flatten `flatten_bom`: | Role | Generated name | Purpose | | --- | --- | --- | | Source | `BDV_FlattenHierarchy_flatten_bom_Source` | Flattens the hierarchy | | Loader | `BDV_FlattenHierarchy_flatten_bom_Loader` | Empties and fills the table | | Storage | `BDV_FlattenHierarchy_flatten_bom` | Hierarchy Flatten table | | Result | `BDV_FlattenHierarchy_flatten_bom_Result` | Query view | - The source view is a recursive query (`WITH` common table expression). The loader is a stored procedure, the table a table. - There is no delta view: the loader truncates the table and inserts all rows of the source view. A load runs this chain: ``` Current View (s1, s2) -> _Source -> _Loader _Loader -> BDV_FlattenHierarchy_flatten_bom -> _Result ``` In the native load control, the loader depends on the loaders of the Hierarchical Link and the Link Satellite behind the Current View, so it runs after them. ### Example The Hierarchy Flatten `flatten_bom` on Microsoft SQL Server reads the Current View `bom_current` twice. That view combines the Hierarchical Link `bom` between parent and child items of the Hub `common_item` with the Link Satellite `bom_lsat`, which stores the `QUANTITY`. The bill of materials is: - Item 1, a burger menu, contains item 2, a burger, and item 3, fries. - Item 2 contains item 4, a bun, and item 5, a patty. - Load 1 on 2026-10-28 08:00 delivers quantity 1 for every pair. Load 2 on 2026-10-29 08:00 changes the patty quantity of the burger to 2. Load 3 on 2026-10-30 08:00 delivers the same data. ### Check the result Query the result view, here in the schema `bv`: ``` SELECT FK_common_item_parent_ITEM_ID AS PARENT_ID, FK_common_item_child_ITEM_ID AS CHILD_ID, QUANTITY, BG_LoadTimestamp FROM bv.BDV_FlattenHierarchy_flatten_bom_Result ORDER BY PARENT_ID, CHILD_ID; ``` This is the result after load 3: ``` PARENT_ID CHILD_ID QUANTITY BG_LoadTimestamp 1 2 1 2026-10-30 08:00 1 3 1 2026-10-30 08:00 1 4 1 2026-10-30 08:00 1 5 2 2026-10-30 08:00 2 4 1 2026-10-30 08:00 2 5 2 2026-10-30 08:00 ``` - The four pairs of the bill of materials appear as they are. Item 1 also gets its indirect children 4 and 5. - `QUANTITY` comes from `s2`, the last level: the pair 1 and 5 shows 2, the quantity of the patty in the burger. Quantities are not multiplied across levels. - Every load replaces all rows, so all rows carry the timestamp of load 3. ### Platform differences - Snowflake: names are in capitals; the table is a transient table and the loader a stored procedure. The load control task `LC_BDV_FLATTENHIERARCHY__TASK` calls it. - Databricks and Fabric Lakehouse: there is no source view. The loader notebook `BDV_FlattenHierarchy__Loader` calculates the hierarchy and fills the table `bdv_flattenhierarchy_`. - Databricks dbt and Snowflake dbt: the Hierarchy Flatten becomes the dbt models `bdv_flattenhierarchy__source`, with `WITH RECURSIVE`, and `bdv_flattenhierarchy_`. The model `bdv_flattenhierarchy__result` exists only when Create Result Views is true. - Oracle: names are in capitals, and the loader is a PL/SQL package. - Fabric Warehouse: not supported. ### Limitations - A Hierarchy Flatten has exactly one dataflow and one dataflow set, no aggregation, at least one term besides the Default Terms and exactly one identity term, `BG_DataQuality_ID`. Validation reports an error otherwise. - The dataflow set contains exactly two source model objects, `s1` and `s2`. - The table holds no history; each load replaces it. ### Release differences - Generator 2.2: Hierarchy Flatten is available on Databricks dbt. - Generator 2.4: Hierarchy Flatten is available on Snowflake dbt, the first release of that generator. ### Related articles - Previous: [Hierarchical Link](https://knowledge.bigenius-x.com/hierarchical-link?hsLang=en), [Link Satellite](https://knowledge.bigenius-x.com/link-satellite?hsLang=en), [Current View](https://knowledge.bigenius-x.com/current-view?hsLang=en) - Next: [Vault Out](https://knowledge.bigenius-x.com/vault-out?hsLang=en) - Reference: [Implementation Type (Model Object)](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en), [File Format (Model Object)](https://knowledge.bigenius-x.com/file-format-model-object?hsLang=en), [BG\_LoadTimestamp](https://knowledge.bigenius-x.com/bg_loadtimestamp?hsLang=en) - See also: [Use parent-child relationships](https://knowledge.bigenius-x.com/use-parent-child-relationships?hsLang=en), [BusinessVault Composite](https://knowledge.bigenius-x.com/businessvault-composite?hsLang=en), [Model a Data Vault Solution](https://knowledge.bigenius-x.com/model-a-data-vault-solution?hsLang=en) - [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) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [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) All rights reserved © 2025, biGENIUS AG