Skip to content
Rime Documentation

Hierarchy Models

Many datasets contain parent-child relationships: an employee reports to a manager, a product belongs to a subcategory within a category, a cost centre rolls up to a division. Querying these relationships across multiple levels — “all employees under this VP” or “all products in the Electronics tree” — requires recursive logic that is tedious to write and maintain by hand.

Hierarchy models in Rime generate recursive structures from parent-child source data, producing a flat table that you can join directly in dimensions, facts, and marts.

What hierarchy models solve

A typical parent-child table looks like this:

idnameparent_id
1CEOnull
2VP Engineering1
3VP Sales1
4Engineering Manager2
5Senior Engineer4
6Sales Lead3

To answer “who are all the reports under VP Engineering, at any depth?”, you need a recursive query. Rime generates this for you.

Output structure

A hierarchy model produces a table with the following columns:

ColumnDescription
node_idThe ID of the current node
node_labelThe display label (e.g., employee name, category name)
parent_idThe parent node’s ID (null for root nodes)
root_idThe ID of the top-level ancestor
levelDepth in the hierarchy (root = 0)
pathFull path from root to this node (e.g., CEO > VP Engineering > Engineering Manager)
is_leafWhether this node has any children (true or false)

This flat structure lets you filter by level, find all descendants of a node, or build breadcrumb paths without recursive queries.

Configuration

To create a hierarchy model:

  1. In your transformation project, go to the Hierarchy Models tab
  2. Click Add Hierarchy Model
  3. Enter a name (e.g., hierarchy_org_chart or hierarchy_product_categories)
  4. Select the source table — the staging table containing the parent-child relationship
  5. Configure the required fields:
FieldWhat to select
Child keyThe column that uniquely identifies each node (e.g., employee_id, category_id)
Parent keyThe column that references the parent node (e.g., manager_id, parent_category_id). Null values indicate root nodes.
LabelThe column to use as the display name (e.g., employee_name, category_name)
Max depthThe maximum number of levels to traverse. This prevents runaway recursion if the data contains unexpected cycles. Default is 10.
  1. Optionally, add extra columns — additional attributes from the source table to carry through to the hierarchy output (e.g., department, location, status)
  2. Click Save

Max depth

The max depth setting controls how many levels the recursive query will traverse. Set this to a value that comfortably exceeds your deepest expected hierarchy:

  • Organisational charts: 6-10 levels is typical
  • Product category trees: 3-5 levels for most retail
  • Geographic regions: 4-6 levels (country > region > city > suburb)

If a branch exceeds max depth, it is truncated at that level. The deepest included node will not have is_leaf = true unless it genuinely has no children — truncated branches are noted in the run log.

Root nodes

Root nodes are identified by having a null parent_key value. Every hierarchy must have at least one root node. If your data has no null parent keys, the hierarchy model will produce an empty output and report a warning.

Some datasets use a sentinel value instead of null for root nodes (e.g., parent_id = 0 or parent_id = -1). In these cases, add a null-handling rule in the staging model to convert the sentinel value to null before the hierarchy model processes it.

Multiple roots

A single source table can contain multiple independent hierarchies (e.g., multiple companies’ org charts, or separate category taxonomies). Rime handles this naturally — each null-parent row becomes a root, and the root_id column lets you filter to a specific tree.

Path generation

The path column contains the full traversal from root to the current node, using the label column values joined by >. For the org chart example:

node_idpath
1CEO
2CEO > VP Engineering
4CEO > VP Engineering > Engineering Manager
5CEO > VP Engineering > Engineering Manager > Senior Engineer

This is useful for breadcrumb display, search, and filtering. If a label contains the > character, Rime escapes it to avoid ambiguity.

Use cases

Organisational hierarchies

Source: an HR system with employees and their managers. Output: a table you can join to any employee-related fact to roll up metrics (headcount, cost, performance) at any level of the org chart.

Product categories

Source: an e-commerce product catalogue with categories and subcategories. Output: a table that lets you aggregate sales at any category level — individual product, subcategory, category, or department.

Geographic regions

Source: a location table with country, region, city, and suburb relationships. Output: a hierarchy that supports drill-down reporting from country level to suburb level.

Chart of accounts

Source: a financial system with account groups and sub-accounts. Output: a hierarchy for rolling up financial data at any level of the account structure.

Reporting lines

Source: a project management system with tasks and subtasks. Output: a hierarchy for tracking progress, dependencies, and rollup completion percentages.

Using hierarchy models in dimensions and facts

Hierarchy models produce standalone tables, but they are most useful when joined into your dimensional models:

  • As a dimension — use the hierarchy output directly as a dimension table in a Kimball project. The node_id serves as the key, and level, path, root_id, and is_leaf are attributes.
  • In a fact table join — reference the hierarchy dimension from a fact table to enable roll-up aggregations. For example, join fct_sales to hierarchy_product_categories on product_category_id to allow aggregation at any category level.
  • In a Data Vault satellite — include hierarchy-derived fields (level, path) as attributes in a satellite for audit and history tracking.

Handling data issues

Cycles. If the data contains a cycle (A is parent of B, B is parent of A), the max depth setting prevents infinite recursion. Rime logs a warning identifying the nodes involved in the cycle. Fix the source data to resolve the issue.

Orphaned nodes. If a node references a parent_id that does not exist in the child_key column, that node is treated as a root. Rime logs a warning listing orphaned nodes so you can investigate whether the parent data is missing.

Duplicate keys. If the child_key column contains duplicates, the hierarchy model will fail with an error. Ensure your staging model deduplicates the source data before the hierarchy model processes it.

Next steps