How to use Unbalanced Hierarchy
1. Abstract
A common requirement when structuring hierarchies in Board is to create an unbalanced hierarchy, Board facilitates this with a dedicated feature “Unbalanced Hierarchies”.
2. Context
Unbalanced hierarchies can be a very useful addition to a Board solution; however, how to define and maintain them requires some understanding of how an unbalanced hierarchy works and what facilities are provided in Board for their maintenance.
3. Content
3.1 Creating an Unbalanced Hierarchy
3.1.1 How to Create the Object
Creating an unbalanced hierarchy object in Board is as simple as creating any other entity, in fact you do so by creating a new entity.
The steps to follow are these:
- Go to the Data Model (Figure 1)
- Go to the Entities (Figure 2)
- Create a new entity, giving it a suitable name, code size, etc. (Figure 3)
- Tick the checkbox marked Unbalanced Hierarchy (Figure 3)
3.1.2 Populating Data into an Unbalanced Hierarchy
Populating data into an Unbalanced Hierarchy requires two steps:
- loading in all the different node values,
- and loading in the relationships between nodes.
It is important to note that the node values must always be loaded before defining the unbalanced relationships. You cannot create new nodes in the data reader which defines the relationships.
3.1.2.1 Loading in Node Values
Loading a node value into an Unbalanced Hierarchy is no different from loading values into a normal entity, it can be achieved by any of the usual methods:
- Data reader (Figure 4)
- Either typing manually or copying and pasting from Excel into the Add New Members tab of the entity (Figure 5)
- Using the Entity Editor component in an ATO-enabled screen or panel (Figure 6)
3.1.2.2 Defining Relationships in an Unbalanced Hierarchy
Defining relationships in an Unbalanced Hierarchy can be achieved in two ways: using a data reader or manually defining the relationships in the Relationships section of Board.
3.1.2.2.1 Defining Relationships with a Data Reader
Defining Unbalanced Hierarchy relationships with a data reader is as simple as loading in a list of the parent/child relationships you want to define, however, the data reader itself looks slightly different from a normal data reader due to the inclusion of the Unbalanced Hierarchy relationship object (Figure 7). This object is used to define the parent, the normal entity code object is used to define the child (Figure 8).
3.1.2.2.2 Defining Relationships Manually
Relationships in an Unbalanced Hierarchy can also be defined manually in the Relationships section of the data model following these steps:
- Go to the Relationships section of the data model
- Select the Unbalanced Hierarchy
- Under ‘related with’ select the Unbalanced Hierarchy again, this will show you its internal mappings (Figure 9)
- To remap a child node simply click on it, and a new dialog will appear allowing you to select the new parent (Figure 10)
3.2 Using an Unbalanced Hierarchy
3.2.1 Loading Data against an Unbalanced Hierarchy
Cubes can be dimensioned by an unbalanced hierarchy in the same way as they are dimensioned by any other entity (Figure 11), the key difference is that even though an unbalanced hierarchy may contain a large number of nodes, data should only be loaded against the ‘leaf’ nodes.
A leaf node has no children and can therefore be considered the most granular level of that branch of the unbalanced hierarchy.
Non-leaf nodes will automatically display the rolled-up values of their leaf nodes, but they should never actually hold data (Figure 12).
Even though the data could be loaded also against non-leaf nodes, it is best practice to avoid that and exploit the roll-up functionality instead.
Loading data against non-leaf members will make the setup difficult to use and it is not the main use case covered by the Unbalance hierarchy feature.
3.2.2 Data Entry with an Unbalanced Hierarchy
When entering data against an Unbalanced Hierarchy by row you will be able to perform Data entry actions on parent members: data will be proportionally distributed on children members following the split and splat logic, while a Data entry action on a leaf will be saved as normal and aggregated to its parents as expected.
Only leaf-level values are physically stored.
When cells are locked, the following rules apply:
- if the locked cell is a descendant of the cell on which the Data Entry is performed, it will be ignored when redistributing data, and its value will be retained;
- if the locked cell is an ancestor of the cell on which the Data Entry is performed, its value will be retained and any change will be redistributed among all its other non-locked descendants.
3.2.3 Relating an Unbalanced Hierarchy to Other Entities
An unbalanced hierarchy can be related to other entities in the same way as a normal entity, however, when defining these relationships, it is important to keep in mind how you intend to use the connected entities.
3.2.3.1 Connecting Parent Entities to an Unbalanced Hierarchy
If you connect an unbalanced hierarchy to a parent entity it is best practice to map all nodes and not just the leaf nodes, this ensures that any selection made on the parent entity will correctly select all required nodes of the unbalanced hierarchy (Figure 13), like any other Parent-Child relationship.
Of course, the best way to configure the parent entity depends on the specific functional requirement.
3.2.3.2 Connecting a child entity to an unbalanced hierarchy
When connecting a child entity to an unbalanced hierarchy (Figure 14), values for ancestors will be retrieved from the descendant elements within the unbalanced hierarchy and therefore from their connected members from the child entity.
This means that it is not mandatory to connect ancestors to specific child elements unless this is functionally required.
In our example below, the “England” ancestor member does not have any relationship with Cost Centre, however, the values (6000) are correctly retrieved via the Unbalanced descendants (Leeds – 2000, Manchester – 4000).
This is supported also when a selection is performed only on the specific “England” member.
3.2.4 Making Selections with an Unbalanced Hierarchy
Making selections on an unbalanced hierarchy is a little different from making selections on a normal entity, as significantly more options are available (Figure 15). The different selection options work as follows:
- Descendants: selects the node itself and all descendants of the selected node, regardless of the number of levels below it they reside.
- Children: selects only the node itself and the direct children of the selected node, it does not select any levels further down.
- Leaves: selects all leaf-level descendant nodes of the selected node.
- Ancestors: selects the selected node and all parent nodes of the selected node, regardless of how many levels above they sit.
- Parent: selects the select node and its direct parent only.
- Siblings: selects the selected node and all other nodes which sit at the same level and have the same parent, no ancestors or descendants are selected.
3.2.5 Using an Unbalanced Hierarchy in a Layout
3.2.5.1 General behavior
The Unbalanced Hierarchy can be used in combination with the Rule.
This is the most common setup to achieve for example P&L reports.
In this case, the execution’s order is Unbalanced roll-up first and then the Rule.
If the Rule includes “Reverse Rule” formulas which writeback data in other elements of the entity, it is important to note that this feature uses only values “physically stored” (in our example Leeds – 2000, Manchester – 4000) into cubes and therefore it will not read rolled-up values from the Unbalanced hierarchy (in our example England – 6000).
3.2.5.2 Limitations
When used in a layout an unbalanced hierarchy must be the last entity specified on either the vertical or horizontal axis. If it is not the last one the rollup will not be executed.
3.2.5.3 Options
Where needed an unbalanced hierarchy can be displayed with its unbalanced nature disabled, by selecting the option Disable Unbalanced Hierarchy under the Rules section of the data block.
3.2.6 Drilling Down on an Unbalanced Hierarchy
Drilling down on an Unbalanced Hierarchy works similarly to any other drill, except that to drill down from a parent node to its descendants you drill the Unbalanced Hierarchy by itself rather than a second entity, e.g., when you click Drill Anywhere, the list will contain the same Unbalanced Hierarchy that you’re drilling on, selecting that will show its direct descendants.
3.3 Limitations of Unbalanced Hierarchies
3.3.1 When Managing an Unbalanced Hierarchy
When managing an unbalanced hierarchy, the following limitations apply:
- There is currently not out of the box way to manage relationships in an unbalanced hierarchy via capsule screen, the ability to manually map relationships only exists in the database area at present.
3.3.2 When Using an Unbalanced Hierarchy in a Layout
When using an unbalanced hierarchy in a layout the limitations are as follows:
- The unbalanced hierarchy must always be the last entity on the axis, it cannot be followed by another entity.
- If you need to disable the use of an unbalanced hierarchy in a layout, it is important to note that, if the Unbalanced entity is not in the axis, the disable option works only if activated for all the blocks of the layout. This is a by-design choice since the rollup is executed one time only for the entire layout.
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.
3.3.4 When Using an Unbalanced Hierarchy in a Procedure
- When allowing the user to make an interactive selection on an Unbalanced Hierarchy via procedure, currently the different selection options normally available (Descendants, Ancestors, etc.) are not available, but only a basic selection.
Comments
-
Thanks for this article, very useful.
I add a comment about connecting parent entities to the unbalanced hierarchy, when it comes to attributes, it is very often that the attribute concerns only the leaf level, so it is better to map only the leaf level and not all nodes, because in that case, functionally, an aggregated node must not be mapped to any value because it doesn't have any sense, or it must be mapped to all of the values of its descendants which is not possible in a 1:N relationship.
Thanks1
