Unbalanced Hierarchies - Allowing "selection mode" configuration in dataflow and export

Kevin Kowalski · October 24

Hello everyone,

Yes, this is my first post on the community. Following internal advices and multiple external feedbacks I'm sharing this Idea that seems to never been raised.

My idea is to allow a developer to configure the default behavior expected on an unbalanced entity selection, only by configurating option mode to the "selection" without changing the fundamental basics of an unbalanced entity.

Current situation :

Unbalanced hierarchy basics

All items in the tree are members of the same Entity
Each item may have multiple children but only one parent
Items that don't have any children are called "leaves" and can store data
Items that don't have parents are called roots

When selecting a member of a entity with an "Unbalanced hierarchy" we are expecting a specific behavior depending the option mode selected.

Right now we do have these options available to the user :

Descendants / Children / Leaves / Ancestors / Parent / Siblings

The “Inclusive” checkbox defines whether or not to include the selected member in the selection.

The “Hierarchy” checkbox defines whether or not to include the levels between the selected member and the member at the maximum depth of the selection.

Limitations (from CoE) :

3.3.3 When Using an Unbalanced Hierarchy in a Data Flow

When using an unbalanced hierarchy in a data flow the following limitations apply:

Only leaf nodes can have values flowed to them, non-leaf nodes are purely used to roll up the data flowed to leaf nodes.
When flowing from a cube dimensioned by an unbalanced hierarchy only the leaf nodes will flow data, the aggregated data at the parent node levels is not physically stored and so will not be flowed.

My Idea :

3 new steps in procedures following the current basics from the unbalanced hierarchy.

In this post I will display example from "Territory Structure" entity directly from the board manual page.

"World" is the root

"Europe" is level 1 parent

"EU" is level 2 parent

"Italy, Spain, Benelux" are level 3 parent

The remaining members are leaves members of the entity, that means that data is by default stored at this level exclusively.

On Board the data can be stored on leaves members by default, or a member (by using the option "Disabled Unbalanced Hierarchy").

On an unbalanced the default behavior is : data is stored on a "member" of the entity. Only an "option mode" can affect the behavior of the selection of the member.

"Select based on step with unbalanced modes"

Allowing the developer to configure a specific selection behavior in a step.

This is from my perspective the most powerful proposal in an unbalanced.

Right now it's impossible to link the "node" with the "leaves", my step will allow to dynamically retrieve the leaves (or siblings, children, ancestors…) from a "selection" on a node.

The selection of the member can be from a layout (by row) or from a "select based"/"dynamic selection" in a procedure. Then the selection of the member (node or leaf) will be applied to the next step of the procedure.

A example about how to open members in an unbalanced :

Result :

Leaves members under "EU" will be populated by dataflow (in my example the value "100" is pasted everywhere), there is multiple cases where a more complex target formula will be useful.

A example about reallocation :

Step 6 can be stored in a TEMP cube if necessary if multiple "option mode" cannot be activated at the same time.

Step 6 → Select "Siblings"

Step 7 → Save selection in a temp cube - TEMP 001

Step 8 → Remove selection

Step 9 → Select based on cube - TEMP 001

Step 10 → Select "Leaves"

Result :

"Export Dataset/Dataview with unbalanced totals"

Allowing the developer to extract dataview or dataset with a specific behavior selection mode of the unbalanced entity.

The extraction will follow the option mode configured to the selection.

Example : Developer/Users needs extract/flags with nodes.

"Extract Unbalanced tree"

Allowing the "extract entity" with a full view of the unbalanced entity.

View of the extract (members) :

This view will allow to track the descendants of an unbalanced, and can be reused by a Data Reader to populate specific flags as all hierarchy is displayed.

View of the extract (root) :

This view is another view of the unbalanced that could useful for multiple cases, this format is used by external system.

My proposal will enhanced the use of unbalanced entities :

Enhancement of the unbalanced hierarchies with powerful/useful features for dataflow and extract
Unused of unnecessary development to retrieve the behavior of the option modes.
Additional possibilities for a developer
Workaround to split & splat on node
Better user and developer experience : the only way to use "options modes" are by a "Selector" and require an action (multiple clicks) from an user.
Used of the extracted files for external/internal applications.
Dynamically filtering elements on unbalanced.
Additional condition can be apply. (lock/flag cubes, cube visibility, security etc.…)
Use of "If then else" on a node with the option mode selection

More Articles :

https://community.board.com/discussion/16748/how-to-use-unbalanced-hierarchy

Current Unbalanced features :

In 14.1 the "Unbalanced selection mode" is allowing the default behavior of the selector.

From the 12.6, in database security, a custom selection script is allowing the developer to use metadata from the subscription hub to assign the option mode "descendants" on a unbalanced entity.

Thank you for reading,

Kind Regards

Kevin

Solution Architect - Board France
kkowalski@board.com

Lucas Charello · October 25

Hello Kevin,

Thank you for bringing up and formalizing this modification request. We faced problems due to limitations of Unbalanced Entites quite a few times lately and I'm sure this will greatly help. We will be able to save a considerable dev time with these new possibilities.

I will definitely share this post with my coworkers.
Best,
Lucas

Karel Kpamegan · October 28

Brilliant

Othmane Chetouani · October 28

Excellent. This should be extremely helpful in multiple use cases.

Thierry Benard · October 30

Hi,

Thank you Kevin for this post. We really need this kind of features for some of our current projects. It is not simple to manage dataflows when most of hierarchies are unbalanced in case of reallocation or any matrix based calculation based on ancestors.

We really need to manage flows like we would do with classical hierarchies.

Thank you for considering this new feature

Fethi ZERARA · October 30

Thank you, @Kevin Kowalski

your observations are very relevant!

We've also noticed limitations when using unbalanced hierarchies across two branches of a single tree (Relationships Tree), especially when combined with filters other filters from the same tree ⇒ Same goes for the security application.

Additionally, there are some restrictive limitations in filtering within procedures and drill-to-screen functionalities.

Kenan · November 4

Thank you, it's brilliant!

Your insights on the impact of this modification are spot-on! We’ve also encountered challenges with the current setup, particularly around managing nested entities in multi-level hierarchies. It’s been limiting, especially when applying dynamic filters across different branches.

Implementing these changes will be a game-changer, streamlining workflows and reducing friction in our processes. I’ll make sure to circulate this with our team.