Unbalanced hierarchies

An Unbalanced hierarchy is a special type of Entity which supports parent-child relationships between its members. In an Unbalanced hierarchy, data is aggregated on the fly along the tree. 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:

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

This type of Entity is similar to the Rollup Entity, but without the requirement that the member's code needs to be hierarchical along the rollup. Rollup Entities continue to be supported for back-compatibility, but Unbalanced hierarchies are more flexible than Rollup Entities, which is why we highly recommend to use Unbalanced hierarchies.

Enabling Unbalanced hierarchies

To enable an Unbalanced hierarchy, go to the Entities section of a Data model, select an Entity and check the "Unbalanced hierarchy" checkbox in the properties panel.

This flag is available for any Entity regardless of its existing relationships within one or more trees. Unbalanced hierarchies are not available for custom Time Entities.

Creating the parent-child relationship

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.

Members with no parent Entity will be considered root Entities, i.e. Entities at the most-aggregate level.

In the following Unbalanced hierarchy, "World" is the root Entity.

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 child 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 relationships.

In addition to defining the parent-child relationship, you will also be able to review the hierarchy as a tree structure from this interface.

Now the 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 at least 1 Entity containing an Unbalanced hierarchy in its dimensions.

The Cube will contain data coming only from the leaves of the tree. Any data not coming from the leaf elements will be ignored when aggregating data along an Unbalanced hierarchy.

By executing a Layout with this Cube, data will be aggregated following the same logic.

This aggregation logic will be applied in the following cases:

1. The Entity that contains the Unbalanced hierarchy is the most-nested in the Layout (i.e. in the rightmost position in the "By Row" field)

2. A selection on the unbalanced Entity is executed and the unbalanced Entity is not in the Layout axes

The aggregation logic mentioned above won’t be applied in the following cases:

1. The Entity that contains the Unbalanced hierarchy is not the most-nested in the Layout (i.e. it is not in the rightmost position in the "By Row" field)

2. The Data Block aggregation has been disabled with the “Disable Unbalanced hierarchy” checkbox under the “Rules” menu in the Block settings panel

3. The Layout is used in a Dataflow

Unbalanced hierarchy in the “By Row” field

Once the Entity that contains the Unbalanced hierarchy is added in the “By Row” field in a Layout, values are aggregated on the fly along the 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 don't vary if you Select or Exclude 1 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 3 members:

In this scenario, Margin is parent of both Cost and Sales.

The physically saved data within the Cube are the following:

• Cost -100

• Sales +1000

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.

Entity in a Select

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.

Drill down

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 to the deepest level of the hierarchy, the leaf level.

Select

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:

1. Descendants

2. Children

3. Leaves

4. Ancestors

5. Parent

6. Siblings

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 popup Selector. They can also be used in Layout Selects, Procedure Selects, Screen Selects, and Security Selects.

Descendants

This option selects all descendants (children) of the selected member.

The value next to the option defines the depth of the selection:

• ALL: All descendants on any level will be selected

• 1: All descendants that are 1 level away from the selected member will be selected

• 2: All descendants that are 2 levels away from the selected member will be selected

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.

Children

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.

Leaves

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.

Ancestors

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:

• ALL: All ancestors on any level will be selected

• 1: All ancestors that are 1 level above the selected member will be selected

• 2: All ancestors that are 2 levels above the selected member will be selected

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.

Parent

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.

Siblings

This option selects all siblings of the selected member.

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

Sorting Unbalanced hierarchies

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.

Data entry

When the Entity that contains an Unbalanced hierarchy is added in the “By Row” field, you will be able to perform Data entry actions on parent members: data will be proportionally distributed on children members following the Split & Splat logic, while a Data entry action on a leaf will be aggregated before saving it and then sent to the Data model to update it.

Only the leaf data are saved.

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 data will be redistributed among all its other descendants

Entity in a Tree

When the Entity that contains an Unbalanced hierarchy is added in the “By Row” field of a Layout associated with a Tree Object and is the most-nested, the Tree will reflect the Unbalanced hierarchy.