Most BI tools support standard hierarchies such that 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. The best part is that when you define your hierarchies in Kyvos, it stays outside your BI tool and users across multiple tools can access the same semantic model and get consistent results.
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.
A balanced hierarchy has an equal number of levels in each branch, and each branch of the hierarchy descends to the exact 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 consistent.
In this use case, all branches extend to the lowest level, and each leaf (also known as a child) is linked to that level.
Figure 1: Balanced Hierarchy
An unbalanced hierarchy has at least one branch that does not reach down to the lowest level. The levels are logically inconsistent while still maintain a consistent parent-child relationship. 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 Sales and the Chief Operation Officer (COO). As the branches under the COO extend further than those under the Director of Sales, the hierarchy is unbalanced.
Figure 2: Balanced Hierarchy
A ragged hierarchy is characterized by having entities whose parents ‘skip a level’. Some branches may be missing certain levels so that at least one parent will not be at the level immediately above its child.
Let us take the following 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: Level members are never hidden.
- OnlyChildWithNoName: A level member is hidden when it is the only child of its parent, and its name is null or an empty string.
- OnlyChildWithParentName: A level member is hidden when it is the only child of its parent, and its name is the same as the name of its parent.
- NoName: A level member is hidden when its name is empty.
- ParentName: A level member is hidden when its name is identical to 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
In my next blog, I will discuss how you can handle custom rollups and recursive and alternate hierarchies in Kyvos.