An Unbalanced Hierarchy is a new special type of Entity which supports defining parent-child relations between its members. In an Unbalanced Hierarchy, data is aggregated on the fly along the hierarchy. These structures are typically used in financial reporting or in organization structures and are common to many other business models.
Example of an unbalanced hierarchy:
All items are members of the same entity. Each item can may have multiple children but one parent only. Items that don't have any children below are called leaves and can store data. Items that don't have parents are called roots.
This new type entity is similar to the Roll-up Entity, but without the requirement that member's code need to be hierarchical along the roll-up. Roll-up entities continue to be supported for back-compatibility but Unbalanced Hierarchies Entities are more powerful than Roll-up entities. It is therefore recommended to use Unbalanced Hierarchies.
To enable an Unbalanced Hierarchy, go to Entities, select an entity and check the "Unbalanced hierarchy" checkbox.
This flag is available to any entity regardless of its existing relationships within one or more trees. Unbalanced Hierarchies are not available in custom time-entities.
When the Unbalanced Hierarchy functionality is enabled, the target entity will always contain all members of the hierarchy itself, regardless of whether they are parent elements or child elements.
To import all members of the hierarchy into the selected entity, you can use a data reader. A different data reader will then assign each member its parent entity.
Members with no parent entity will be considered root entities, i.e. entities at the most aggregate level.
To configure the data reader that will structure parent-child relationships, head to the Data reader section and build a new data reader. In the mapping table, the entity that contains the unbalanced hierarchy will show an additional “parent” field for its members, in addition to the usual “code” and “description” fields.
You will need to map the child code to the code field and the parent code to the parent field.
Alternatively, you can manually define parent members from the relationships section of the Data model: to do this, you will need to view the relationship between the entity and itself.
To establish the parent-child relationship, click on a member and select which other member of the hierarchy will be the parent in the tree.
In order to load an Unbalanced Hierarchy from an external table or file, first load all parents and children items in the entity through a Data Reader (or manually), then read the parent-child table or file that contains the relations.
In addition to defining the parent-child relationship, from this interface you will also be able to review the hierarchy as a tree structure.
Now the unbalanced entity is ready to be used to aggregate data according to the defined hierarchy and to make specific selections.
Now, let’s consider a cube that has, between its dimensions, at least one entity that contains an unbalanced hierarchy.
The cube will contain data coming only from the leaves of the tree diagram. Any data not coming from the leaf elements will be ignored when aggregating data through Unbalanced Hierarchies.
By executing a layout to this cube, data will be aggregated following the same logic.
This aggregation logic will be applied in the following cases:
The aggregation logic mentioned above won’t be applied in the following cases:
Once the entity that contains the unbalanced hierarchy is added in the “BY ROW” in a Layout, values are aggregated on-the-fly along the roll-up hierarchy.
The items displayed in the Layout are those selected in the Select filters. Note that the on-the-fly aggregation is not affected by the Select filters therefore the values of parent items doesn't vary if you Select or Exclude one or more children. The Select filters only affect which rows are returned by the Layout.
For example, let’s consider a hierarchy made up of three members:
In this scenario, Margin is parent of both Cost and Sales.
The physically saved data within the cube are the following:
The corresponding Data View will show the following values:
By selecting Margin and Cost only, the Data View will show the following values:
When the entity that contains the unbalanced hierarchy is added in the “BY ROW” field in a layout, it is also possible to apply Rules and Nexel formulas to it.
Suppose the entity that contains the unbalanced hierarchy is not in the “BY ROW” field in a layout, but you've selected a parent using a Select: the layout will show the total sum of that parent’s children values.
In the previous example, by selecting Margin, the Data View will show the total sum of 900.
When an entity contains an unbalanced hierarchy, you can drill-down to the next level down the hierarchy by drilling-down on the entity itself. This process can be repeated down to the deepest level of the hierarchy, the leaf level.
In the selection window, the entity that contains the unbalanced hierarchy will be shown as a tree diagram.
Click on the small arrow next to a node to expand it, then tick the checkbox next to the entity member that you want to add to the selection.
The search function in the upper right corner of the window will display the searched member and all parent-child relationships involving it.
There are also special Select options that can be used on a single entity member of an unbalanced hierarchy:
These selections are dynamic: any modification to an unbalanced hierarchy will be reflected in all affected Selects.
For example, if the number of children of a particular member changes and a Select includes those children, the Select will self-update automatically to include or exclude members accordingly.
All these features are also available in the vertical and pop-up selector. They can also be used in Layout Selects, Procedures Select, Screen Selects and Security Selects.
This option selects all descendants (children) of the selected member.
The value next to the option defines the depth of the selection:
And so on…
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.
This option selects direct descendants (children) of the selected member. Descendants of the selected member’s descendants won’t be selected.
The “Inclusive” checkbox defines whether or not to include the selected member in the selection.
This option selects leaf members that are descendants of the selected member. Nodes are excluded from the selection.
The “Inclusive” checkbox defines whether or not to include the selected member in the selection.
This option selects all parent members of the selected member, up to the root member.
The value next to the option defines the depth of the selection:
And so on…
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 root member.
This option selects the direct parent member of the selected member.
The “Inclusive” checkbox defines whether or not to include the selected member in the selection.
This option selects all siblings of the selected member.
The “Inclusive” checkbox defines whether or not to include the selected member in the selection.
Row sorting follows the tree structure of the hierarchy, so members will not be sorted by code or description, but they will always be displayed as a tree diagram. When sharing the same parent, members will be sorted according to the option chosen in the entity's “Sort By” field.
Layout sorting options can be used to change the sort order on the individual layout.
When the entity that contains an unbalanced hierarchy is added in the “BY ROW” field, you will be able to perform Data Entry on parent members: data will be proportionally distributed on children members following the split and splat logic, while a Data Entry on a leaf will be aggregated before saving it and then sent to the Data Model to update it.
Only leaf data are saved.
When cells are locked, the following rules apply:
When the entity that contains an unbalanced hierarchy is added in the “BY ROW” field of a Tree Layout and is the most nested one, the Tree will reflect the unbalanced hierarchy.
