Historicisation of relationships

Center of Excellence · April 22

1. Abstract

In Board, hierarchies are without time reference as standard. This simplifies data modeling in many cases. In some cases, however, it is necessary to map relationships between different nodes in a time-dependent manner and thus to historicize the values. There are different methods for this, which are to be preferred depending on the use case. They will be worked out in the following and provided with practical examples and technical solutions.

2. Context

In general, whenever relationships are to be displayed in a time-dependent manner.

In particular, the method depends on the use case. Use cases can be, for example, the relationship of a representative to the customer (Method 1: Additional Cube Dimension), the relationship of an account to the P&L reporting line or the entire P&L account hierarchy should be time-dependent (Method 2: Additional key(s) as nodes).

The best practices described can also be abstracted to all other use cases.

In the following use cases, we compare one month with the following month (month 1 and month 2). In our example, the months are 202201 and 202202. This can be abstracted to other months even if they are not consecutive.

3. Content

3.1 Method 1: Additional Cube Dimension

3.1.1 Use Case

A good example of this would be a sales rep being responsible for the customer at transaction time who subsequently has moved to another area of responsibility. The current status of the hierarchy, however, would place another agent in this role. The current information therefore would no longer reflect the situation at the time of the transaction.

3.1.2 Technical Implementation

Please refer to the article “How to structure cubes with slow-moving hierarchy”.

3.1.3 Conclusion and differentiation from the other methods

The technical prerequisite for using this method is that there are very few relationships to be historicized. The cubes in which the dimension to be historicized is added must have enough "free space" available to be able to add further dimensions. In large data models, the performance aspect must always be considered.

This method does not allow a "Like for Like" view in comparison to Method 2: Additional key(s) as nodes.

3.2 Method 2: Additional key(s) as nodes

3.2.1 Regular Hierarchy – Change-based

Use Case

The P&L hierarchy contains accounts and various nodes that can change the relationship over time. Both an "as was" and a "like for like" view should be made available to be able to view the accounts and their hierarchy in one or the other way, depending on requirements.

Starting point

The following flat account structure is the starting point. It shows the "like for like" view only.

The source contains the following entries for month 1.

And the following entries for month 2.

So there is a movement from P&L Lvl 1 "A" to P&L Lvl 2 "B" and a movement from account "10" to P&L Lvl 1 "C" from month 1 to month 2.

Technical Implementation

Below we show how to combine the "Like for Like" view with the "As Was" view.

A technical key and a second branch need to be added to have the “As Was” view.

The target structure of the hierarchy is as follows.

The lower nodes represent the "as was" view. The upper nodes represent the “like for like” view. The cubes must be structured on “Account_as was (key)”

The source data must be structured as follows for month 1.

Showing the “Like for Like” view in a data view:

And showing the “As Was” view in a data view:

For the first month, both are the same.

For month 2 the source data is structured as follows:

There is still the same movement from P&L Lvl 1 "A" to P&L Lvl 2 "B" and a movement from account "10" to P&L Lvl 1 "C" from month 1 to month 2.

Showing the “Like for Like” view in a data view:

And showing the “As Was” view in a data view:

The data view shows the code of the hierarchy container for better visualization. For better usability, the description can be selected as in the "Like for Like" view or contain a concatenation of code and description and be displayed instead of the code.

Each time the hierarchy is loaded and new master data is created, the nodes are reallocated. This allows you to control when historicization should take place.

Conclusion and differentiation from the other methods

This method is preferred if there are several trees or nodes that are to be historicized.

Where required, you can now choose between the two views in the front end. The two views cannot be mixed in one object. We also recommend not combining them on one screen for better usability. Due to the different entities and possible selectors, the end user can otherwise quickly get confused.

This method can lead to confusion and poor usability with large trees or when used in several hierarchies, but it is preferable to Method 1: Additional Cube Dimension when multiple historization of nodes is used, as it is more performant.

It would also be possible to create the keys on a time-based basis. However, "change-based" is preferable to "time-based" when creating the keys, as significantly fewer elements are created in the entities in this way. This only happens if there is actually a change in a relationship. If the key were to be created on a time-based basis, all basic elements would be multiplied every month, even if there is no new relationship in the source data. A key with a time reference would look like this, for example: "1_202201", "1_202202", ...