Unbalanced Hierarchies - Allowing "selection mode" configuration in dataflow and export
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 :
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
Comments
-
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,
Lucas3 -
Brilliant
1 -
Excellent. This should be extremely helpful in multiple use cases.
2 -
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
2 -
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.
5 -
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.
2 -
Yes, there is serious issue / strange behavior related to unbalance hierarchy when used in tree
We have made few years ago a dedicated debug capsule & DB with all the issue but bugs are still there in the recent board version
1 -
Hi Kevin,
Thank you for formalizing these requests. They will be immensely helpful across various use cases and are sure to add significant value.1 -
Thank you everyone for your votes, feedbacks and your comments.
I would like to make additional comments about the current functionality in Board 12.6 and Board 14.1. It's possible from a security perspective to apply "unbalanced selection mode" in a "custom selection script".
Links :Database Security Section
Add Special Select Options for Unbalanced entitiesFunctionName :
InclusiveBoolean : this parameter will allow to include or exclude the MemberCode.
HierarchyLevel : this parameter will include data only related to ancestor or descendant members
IncludeNonLeafBoolean : This parameter is a boolean (True or False) that you can use to include or exclude non-leaf members of the Entity on which you are applying the selection
Select Entity = FunctionName(MemberCode1,"MemberCode,;{}[]@=()2"...*
Select EntityName = FunctionName1(MemberCode1;InclusiveBoolean...) Select EntityName = FunctionName2(MemberCode2;InclusiveBoolean...)
Select EntityName = FunctionName(@var(Metadata);InclusiveBoolean)
It's also possible to use : "Leaves(MemberCode,true)"
Kind Regards,
Kevin0
