Hierarchy Flatten

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 or a BusinessVault Composite that holds the parent-child pairs, usually a Current View on a Hierarchical Link 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 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 of type Hierarchy Flatten in the Business Vault layer.
2. Create a dataflow set and add the source 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: 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: Cached only. Each load empties the table and fills it again.
• File Format: on Databricks, Databricks dbt and Fabric Lakehouse.

Default Terms

• BG_LoadTimestamp: the load that filled the table; the same for all rows.
• BG_SourceSystem.
• BG_DataQuality_ID, BG_ErrorCount and BG_ErrorDescription: 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:

• 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_<model object name>_TASK calls it.
• Databricks and Fabric Lakehouse: there is no source view. The loader notebook BDV_FlattenHierarchy_<model object name>_Loader calculates the hierarchy and fills the table bdv_flattenhierarchy_<model object name>.
• Databricks dbt and Snowflake dbt: the Hierarchy Flatten becomes the dbt models bdv_flattenhierarchy_<model object name>_source, with WITH RECURSIVE, and bdv_flattenhierarchy_<model object name>. The model bdv_flattenhierarchy_<model object name>_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, Link Satellite, Current View
• Next: Vault Out
• Reference: Implementation Type (Model Object), File Format (Model Object), BG_LoadTimestamp
• See also: Use parent-child relationships, BusinessVault Composite, Model a Data Vault Solution