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
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.
FAQs
What is the difference between ragged and unbalanced hierarchy?
An unbalanced hierarchy has at least one branch not going down to the lowest level, meaning some branches of the hierarchy will have more levels than others. On the other hand, a ragged hierarchy may have entities where the parents skip a level, meaning some branches may not have certain levels, and a parent may not be available at the level just above the child.
