# Handling Ragged and Unbalanced Data Hierarchies

> Sisense offers several solutions for resolving complex hierarchies. These solutions range from simple table modelling using a dashboard hierarchy to transforming the data with SQL.

*Source: https://docs.sisense.com/main/SisenseLinux/handling-ragged-and-unbalanced-data-hierarchies.htm*

---

Last updated: June 10, 2026

|  |  |
| --- | --- |
| [Tier](https://www.sisense.com/pricing/#pricing) | [Deployment](https://docs.sisense.com/main/SisenseLinux/introduction-to-sisense-cloud-managed-services.md#ComparisonofManagedCloudandSelfHosted) |
| Launch     Grow    Enterprise | Cloud     On-Prem |

Business Intelligence tools are designed to ingest data that is organized in standard hierarchies: hierarchies that
have a field on each level of the hierarchy. Complex data hierarchies, such as ragged and unbalanced hierarchies, can
be difficult to model and query. If your data is ragged and unbalanced, you will have to get it into a form that BI
tools can ingest before you can use it in a dashboard.

Depending on the structure of the data, Sisense offers several solutions for resolving complex hierarchies. These
solutions range from simple table modelling using a dashboard hierarchy to transforming the data with SQL. This page
focuses on several specific types of complex hierarchies. Use the approach delineated below to resolve complex
hierarchies.

## Types of Data Hierarchies

A *balanced hierarchy* has an equal number of levels in each branch and is easiest to handle. The example
shown below is a natural hierarchy of **years > quarters > months**.

![Balanced hierarchy](https://docs.sisense.com/main/Resources/Images/balanced-hierarchy_870x429.png)

An *unbalanced hierarchy* has at least one branch which does not reach down to the lowest level. The below
example of a company org chart is typical of an unbalanced hierarchy. Some divisions have more levels than others.

[![Unbalanced hierarchy](https://docs.sisense.com/main/Resources/Images/unbalanced-hierarchy_849x612.png)](https://docs.sisense.com/main/Resources/Images/unbalanced-hierarchy.png)

A *ragged hierarchy* is characterized by having entities whose parents **'skip a level'**. The
below screenshot exemplifies this. The city Washington DC and the Greek cities of Athens and Thessaloniki roll
directly to the Country instead of to a State.

![Ragged hierarchy](https://docs.sisense.com/main/Resources/Images/ragged-hierarchy_762x425.png)

Hierarchies can also be both *ragged and unbalanced*, adding an additional level of complexity.

## The Scenario

In the below scenario, data hierarchy is represented by the following diagram. Note that it is both unbalanced (some
branches do not reach down to the lowest level) and ragged (some branches skip a level).

![Ragged unbalanced hierarchy](https://docs.sisense.com/main/Resources/Images/ragged-unbalanced-hierarchy.jpg)

The initial hierarchy is defined recursively, as shown below:

![Hierarchy defined recursively](https://docs.sisense.com/main/Resources/Images/hierarchy-defined-recursively_701x526.png)

## The Solution

1. To address this scenario, in Sisense, create placeholder values where a level is skipped in a ragged hierarchy.

   To start with, you create a new table to identify skipped entities. A new table **'Hierarchy1'** has
   been created for this purpose using the SQL below. For simplicity, numbers are hard-coded to correspond to the
   levels, and only single levels are skipped.

   This SQL can be easily modified for other scenarios using the same core logic.

   ![Ragged hierarchy sql](https://docs.sisense.com/main/Resources/Images/ragged-hierarchy-sql.png)
2. Consolidate all the branching entities that roll to the same placeholder parent to the same entity.

   ![Ragged hierarchy](https://docs.sisense.com/main/Resources/Images/ragged-hierarchy-2.png)
3. Union the placeholder values back into the main hierarchy table, and join and replace the appropriate parent IDs.
   This can be done in either order, but the screenshot below shows the union, then the join.

   ![Ragged hierarchy](https://docs.sisense.com/main/Resources/Images/ragged-hierarchy-3.png)

   The non-ragged hierarchy now logically looks as below. It is ready now to resolve using a standard flattening
   approach. The Entity Name field is used in place of IDs for simplicity.

   ![Ragged unbalanced hierarchy resolved](https://docs.sisense.com/main/Resources/Images/ragged-unbalanced-hierarchy-resolved.jpg)

   The SQL to do so is as follows:

   ![Ragged unbalanced hierarchy resolved sql](https://docs.sisense.com/main/Resources/Images/ragged-unbalanced-hierarchy-resolved-sql.png)

   **Note:**

   The N/As in the table account for the unbalanced branches where the placeholder values account for the
   ragged hierarchy.
4. Now it is a simple matter of mapping the new resolved hierarchy table back into the data model. If the facts all
   roll to the lowest level of the hierarchy, the above logic and resulting table will work as-is. If the facts need
   to be attributed across different levels of the hierarchy, you would split the structure into different tables.
   The resolved parent IDs can be used to link across the hierarchy (see example below).

   ![Split data model to tables](https://docs.sisense.com/main/Resources/Images/split-data-model-to-tables_1014x363.png)

   Roll-ups should now work seamlessly when using these new dimensions, and this should be easily consumed on a
   dashboard.