Mart Hierarchy Flatten

A Mart Hierarchy Flatten resolves a parent-child hierarchy in the Mart layer into one row per start row and level. Use it when you report along a hierarchy of unknown depth, for example an employee and every manager above. After each load, its table holds the complete paths of the selected start rows.

Fabric Warehouse does not support Mart Hierarchy Flatten. Validation reports the warning Model object has an unsupported model object type for the Fabric Warehouse platform., whose details name the missing recursive common table expressions. Mart Hierarchy Flatten is available since Generator 1.10, on Oracle since 2.0 and on Databricks dbt since 2.2.

Sources and readers

A Mart Hierarchy Flatten reads one source twice:

• Data Mart: a Mart Composite of the project, or a linked Current View, Business Current View or BusinessVault Composite of a Data Vault project.
• Vault and Mart: Current View, Business Current View, BusinessVault Composite, Vault Out or Mart Composite.
• Dimensional and Mart (not on Databricks dbt and Oracle): Mart Composite.

A BusinessVault Composite as source raises the validation warning ID-21002, see Composite model object from another layer (Business Rule). Its message refers to the Vault Out.

A Mart Hierarchy Flatten has no relationships. Mart Dimension, Mart Fact, Mart Composite and Mart Out can read it.

Create a Mart Hierarchy Flatten

There is no wizard for this type. Create it manually:

1. Create a model object of type Mart Hierarchy Flatten in the Datamart column.
2. Create a dataflow set and add the source twice: s1, the first source of the dataflow set, stands for the row of the current level, s2 for its parent.
3. Join s2 to s1 on the parent key, for example s2.EMPLOYEE_ID = s1.MANAGER_ID.
4. Optionally, set a filter on s1 in the dataflow set. It selects the start rows.
5. Map the terms. Take the key of the start row from s1 and the values of the next level from s2. Give each term the name of a column of the source, because the start rows read the columns by name.

How the hierarchy is resolved

The source view is a recursive query:

• Start rows: every row of the source that passes the filter. Each term takes the column of the same name from the source, without the mapping expression.
• Next level: each row of the previous level takes the place of s1 and is joined to its parent in s2. The term mappings calculate the new row. The filter does not apply to the parents.
• The query stops when no parent is found. Each start row therefore appears once per level of its path.

Terms that you do not map, such as BG_SourceSystem, are empty in the rows of the next levels.

Properties

• Implementation Type: Cached only. The loader empties the table and inserts all rows of the source view on every load.

Default Terms

• BG_LoadTimestamp: the load that wrote the rows.
• BG_SourceSystem: the value of the start row; empty in the next levels.

Generated objects

These are the names on Microsoft SQL Server for a Mart Hierarchy Flatten employee_hierarchy:

The loader is a stored procedure, the flattened table a table and the other objects are views. The dependencies are:

source -> _Source -> _Loader
_Loader -> DM_FlattenHierarchy_employee_hierarchy -> _Result

Example

The Mart Hierarchy Flatten employee_hierarchy on Microsoft SQL Server reads the Mart Composite employee_manager twice. Its terms are mapped as follows:

• EMPLOYEE_ID (business key): s1.EMPLOYEE_ID.
• LAST_NAME: CAST(CONCAT(s1.LAST_NAME, ' - ', s2.LAST_NAME) AS NVARCHAR(1000)), the path. The cast keeps NVARCHAR(1000), the data type of LAST_NAME in the Mart Composite, because a recursive query needs the same data types on every level.
• MANAGER_ID: s2.MANAGER_ID, the parent of the level reached. The next level joins on it.

The join is s2.EMPLOYEE_ID = s1.MANAGER_ID, and the filter s1.EMPLOYEE_ID in (401, 405, 416) selects the start rows. The Mart Composite holds employee 401 Keller without a manager, 405 Huber reporting to 401, 416 Meier reporting to 405 and 420 Brunner reporting to 416. The Mart Composite is loaded before the Mart Hierarchy Flatten.

Check the result

Query the result view, here in the schema mart:

SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, BG_LoadTimestamp
FROM mart.DM_FlattenHierarchy_employee_hierarchy_Result
ORDER BY EMPLOYEE_ID, LEN(LAST_NAME);

This is the result after the load on 2026-10-28 at 10:00, with a shortened timestamp:

EMPLOYEE_ID  LAST_NAME                MANAGER_ID  LoadTs
401          Keller                   NULL        2026-10-28 10:00
405          Huber                    401         2026-10-28 10:00
405          Huber - Keller           NULL        2026-10-28 10:00
416          Meier                    405         2026-10-28 10:00
416          Meier - Huber            401         2026-10-28 10:00
416          Meier - Huber - Keller   NULL        2026-10-28 10:00

• Employee 416 has three rows: the start row and one row for each manager above.
• Employee 420 is not a start row and does not appear.
• The table has no level column; the query orders the levels by the length of the path.

Before the load at 12:00, employee 405 changes the last name to Huber-Graf, and employee 416 now reports to 401. After that load, the result is:

EMPLOYEE_ID  LAST_NAME                MANAGER_ID  LoadTs
401          Keller                   NULL        2026-10-28 12:00
405          Huber-Graf               401         2026-10-28 12:00
405          Huber-Graf - Keller      NULL        2026-10-28 12:00
416          Meier                    401         2026-10-28 12:00
416          Meier - Keller           NULL        2026-10-28 12:00

The loader rebuilt the table, so the path of 416 skips 405 and no row of the first load remains.

Platform differences

• Snowflake: names are in capitals, and the flattened table is a transient table. The load control task LC_DM_MARTFLATTENHIERARCHY_<MODEL OBJECT NAME>_TASK calls the loader.
• Databricks and Fabric Lakehouse: there is no source view (the Source row of the table does not apply). The loader notebook DM_FlattenHierarchy_<model object name>_Loader resolves the levels itself. The property File Format applies.
• Databricks dbt: the generator creates dm_flattenhierarchy_<model object name>_source (view with WITH RECURSIVE), dm_flattenhierarchy_<model object name> (table) and dm_flattenhierarchy_<model object name>_result (view, only with Create Result Views on).
• Oracle: there are no physical table properties for this type.

Limitations

• A Mart Hierarchy Flatten has exactly one dataflow with one dataflow set and no aggregation.
• It has no relationships and at least one term that is not a Default Term.
• Every term name must exist as a column of the source.

Related articles

• Previous: Mart Composite
• Next: Mart Dimension, Mart Fact
• Reference: Implementation Type (Model Object), File Format (Model Object), BG_LoadTimestamp, BG_SourceSystem, Composite model object from another layer (Business Rule)
• See also: Hierarchy Flatten, Vault Out, Mart Out, Model a Data Mart Solution