How to Handle Unbalanced and Ragged Hierarchies?

Most BI tools support standard hierarchies where a field value is available on each level of the hierarchy. However, in real business scenarios, all hierarchies are not perfect, and querying such hierarchies becomes difficult. A common challenge that a lot of organizations encounter is managing ragged or unbalanced hierarchies.

Another thing that adds to the problem is scale. As data size increases, complex data modeling gets tougher. Using Kyvos, you can define a hierarchy to the lowest level of granularity and fully aggregate it at the time of cube building, using simple drag and drop operations.

In this blog, I will talk about ragged and unbalanced hierarchies and how you can easily handle these in Kyvos.

First, let us understand these hierarchies.

Understanding hierarchies

What is Balanced hierarchy

A balanced hierarchy has the same number of levels in each branch, and each branch of the hierarchy goes down to exactly the same level.

One of the most common examples of a balanced hierarchy is the time hierarchy, where the depth of each level (year, quarter, and month) is same.

In this use case, all branches extend to the lowest level, and each child is linked to that level.

Figure 1: Balanced Hierarchy

What is Unbalanced hierarchy

An unbalanced hierarchy consists of at least one branch that does not go down to the lowest level. This means some branches of the hierarchy will have more levels than the others.

A simple organization chart is a perfect example of this type of hierarchies. While each member maintains a logical parent-child relationship, some branches in the hierarchy have more (or less) levels than others.

In the following example, the CEO is at the top level, followed by the Director of Marketing and the Chief Operation Officer (COO). As the branches under the COO extend further than those under the Director of Marketing, the hierarchy is unbalanced.

Figure 2:Unbalanced Hierarchy

What is ragged hierarchy

A ragged hierarchy consists of entities where the parents skip a level. In such hierarchies, some of the branches may not have certain levels. In this case, a parent may not be available at the level just above its child.

Let us look at an example, where we have two branches. The North America branch descends consistently through each level (Continent, Country, State, and City). However, you will notice that the Asia branch does not have the State level. It moves directly to the City level.

Like unbalanced hierarchies, the branches of a ragged hierarchy can also descend to varying levels.

Figure 3: Ragged Hierarchy

Approaches for modifying drilldown navigation in a ragged hierarchy

Imagine trying to find a way out of a maze without a map. The users feel exactly like that when a drill-down operation on a ragged hierarchy displays unexpected values. However, there are some strategies that can be adopted to fix these problems. A standard hierarchy structure can be used with the application of HideMemberIf property at each level, controlling the visibility of missing levels to the user. This also requires adjusting the MDXCompatibility in the connection string to modify the default navigation behaviors.

Drill-down navigation in ragged hierarchies can also be modified by implementing a parent-child hierarchy to manage level members explicitly. However, this approach allows only one parent-child hierarchy per dimension. Also, it often leads to performance issues during the calculation of aggregations for intermediate members.

The HideMemberIf setting helps resolve drill-down navigation in dimensions featuring more than one ragged hierarchy. Further modifications to the physical data models, such as creating distinct tables for each hierarchy level, facilitate better navigation.

After adjusting the HideMemberIf setting for a hierarchy level, it is necessary to set the MDX Compatibility property in the client application’s connection string. This setting dictates the application of the HideMemberIf property. The default setting for applications like Excel, SSDT and SSMS is ‘Show a placeholder value’. It helps retrieve placeholder values when drilldowns are performed on empty cells in a ragged hierarchy. Drilling down further into child nodes is possible by clicking on these placeholder values.

Hiding placeholders while displaying relevant levels and nodes is preferred for ragged hierarchies.

Resolving ragged hierarchies in Data Modeling

Irregular structures can complicate data retrieval, reporting and analytics. Several strategies, each with its own merits and challenges, provide different ways to streamline data models for enhanced performance and clarity. Here’s how these strategies can be applied to resolve the intricacies of ragged hierarchies:

Snowflaking: Normalizing the hierarchy into a snowflake can work wonders in specific scenarios. Consider a hierarchy of products, product segment and product category transitioned into a snowflake model. This process involves segregating products into a product dimension, product category into a separate table and product segment into a segment table. Here, it is crucial to maintain the parent and grandparent IDs while loading these dimensions so that the product dimension references the category and segment tables as needed, whereas the category table links back to the segment dimension.

Flattening the hierarchy: Similar to simple dimensional hierarchy, this can be achieved by copying the grandparent-level data to the parent level. This transfer of pseudo data across levels where the hierarchy is missed aims to balance any skipped hierarchy level. The pseudo data might also carry a flag to indicate that the connection between the child and parent is to be bypassed, directing the connection to move up to the next level, i.e., the grandparent. This method is particularly useful for consistent reporting across different hierarchy levels where values for sub-type tier are absent.

Recursion: Recursion is the process of establishing a relationship of the child record with the parent record as an attribute of the child record. This method offers a simple and flexible solution to the ragged hierarchy dilemma. However, recursion comes with its own set of challenges, notably impacting query performance and compatibility with a limited range of reporting tools capable of executing recursive queries.

Hierarchical bridge table: The use of hierarchical bridge tables presents a robust solution for managing hierarchies. For instance, within a standard organizational chart, a hierarchical bridge table can simplify identifying all teams that fall under a specific department or identify the management chain of a given department. This table includes records of each department’s association with all teams, alongside indicators of hierarchy depth and position within the hierarchy. The creation of this table enables swift retrieval of hierarchical relationships through simple select statements. Hierarchical bridge tables allow for the use of simple joins and eliminate the need for using recursion, ultimately leading to better performance.

The Kyvos Solution

Kyvos allows you to handle both ragged and unbalanced hierarchies without the need for complex data modeling processes. Users with no understanding of the underlying data sources can easily drag and drop data from their hierarchy.

They can see the correct aggregated values at each level, and drill down and up across the hierarchy to further see the breakdown under each branch.

Now, let us understand this through a use case.

Consider a hierarchy: Sales Territory → Sales Territory Country → Sales Territory Group

Figure 4: Sample Hierarchy

Certain members such as France, are repeated throughout the hierarchy. Here is how it is displayed in MS Excel visualization.

Figure 5: Repeated Member in a Hierarchy

As the member France is being repeated, the corresponding Sales Amount is also displayed redundantly. While designing the cube, you can hide these redundant levels using Hide Member property in Kyvos.

Figure 6: Hide Member in Kyvos

You can select any of the following:

• Never: Does not hide any level member.
• OnlyChildWithNoName: Hides a level member if it is the only child of its parent, and its name is null or an empty string.
• OnlyChildWithParentName: Hides a level member if it is the only child of its parent, and both the parent and child have the same name.
• NoName: Hides a level member if its name is empty.
• ParentName: Hides a level member if its name is same as that of its parent.

After changing the property for the dimension level Country, the following result is displayed in MS Excel.

Figure 7: Updated Hierarchy View in Excel

Stay tuned

In my next blog, I will discuss how you can handle custom rollups and recursive and alternate hierarchies in Kyvos.