Rule Consolidation

Center of Excellence

1. Abstract

The rule is a key functionality that allows on-the-fly calculations between members of the same entity. The consolidation is a further step to physically store the result in the data model. This step is needed only in specific cases, usually when the calculated rows need to be reused as a source for following calculations.

2. Content

2.1 Rule Consolidation

The main advantage of using Rule is the possibility to use formulas, simple or complex, where factors and results are part of the same entity. Rules are largely used for financial reports to calculate on-the-fly metrics (ie. NET MARGIN, EBIT).

All the calculations and formulas applied through rules are calculated on-the-fly directly on the report, on the block, where the Rule is set up. The option of storing the result of the Rule physically on the Data Model is given through the rule consolidation process.

To perform the consolidation, it’s necessary to

• Create a cube with the same dimensions as the cubes containing the raw data;
• Create a dataflow with two blocks: the first block is the cube containing the raw data with the Rule applied, the second block is the target cube, the new cube created, that will store the consolidation result.
• In the dataflow option, extend the domain to the entity in which the rule is applied.

By executing the dataflow, the Rule, applied to the first block, will calculate all the metrics, and the results will be stored in the target cube.

Please consider consolidating only the portion of the rule needed or selecting only the entity members that contain the formula and so the result of the rule.

For example, if you have the following case:

010 Revenue

020 Cost of Goods Sold

030 Gross Profit

Where [030] is [010]-[020]

The dataflow to consolidate the rule can be executed by selecting only the 030 Gross Profit. The result will be properly calculated and stored without selecting the factor members 010 Revenue and 020 Cost of Goods Sold.

In general, the consolidation process consumes many system resources, especially when performed on complex cubes, with many dimensions or largely populated. In addition, by consolidating a rule, data became physically stored in the cube increasing the overall Data Model size. For these reasons, the suggestion is to avoid consolidating a rule unless strictly necessary.

So, the question naturally arises, when is consolidation needed and when is it possible to be avoided?

2.1.1 When can a rule be used without any consolidation?

Reports & Dashboard (KPIs): As explained,rules are mainly used for financial reports to calculate on-the-fly metrics. Rules allow simulation, it is possible to change any of the factors of the Rule and see the result immediately recalculated. It’s possible to change the selection without affecting how the Rule works. The results are always recalculated based on the new perimeter.

It’s also possible to restrict the selection and visualize only the result of the Rule and so the specific KPI. For example, it is possible to create a KPI that shows the calculated margin without displaying its components: turnover and costs.

All this means that the rules can be widely used for reporting and dashboarding purposes without consolidating the result.

Note that the entity where the Rule is built must always be by row in the configuration layout of the object used to display the result (data view for table-like report or label to display KPI)

Charts: It’s possible to use rules in any charts and display the calculated fields without any consolidation process; it’s enough to apply the rule in the specific block and it will work as in the data view object.

Note that the waterfall chart is particularly useful to visualize the financial reports data. This object displays totals and major subtotals as full bars that start from the baseline of the chart, while sub-components, and so the contributors, of the incoming and outgoing streams, are represented by color-coded floating bars.

Data Export: It’s possible to export data calculated through a rule without consolidation. This is possible using the procedure step “Export Dataset” or “Export Dataview to File” by configuring the layout with the Rule applied to the related block.

Note that by using the “Extract cube” step, only the data stored in the cube are exported.

2.1.2 When must a rule be consolidated in a cube?

Drill-down: To have an effective drill-down on a field calculated through a rule, it’s necessary to have the rule calculated at the most granular level and the result stored in a cube. Without the consolidation, the drill down on the calculated field will return an empty cell.

Source for Dataflows or any other application flows: To use the resultofthe rule as a source for another calculation it’s necessary to have it stored in a cube. It is not possible to move data that are the result of a rule in another cube if the information is not consolidated.

Only in these two scenarios, the rule consolidation is inevitable to have the result expected. Note that the consolidation process must be executed every time the factors and so the result changed to make sure to always have the stored data aligned with the on-the-fly calculation.

Nicolas BENDONGUE

Thank you for this Best pratice.
However, you've mentionned that we need two cubes which have exactly the same structure in order to perform the Rule consolidation. What if we use the same cube? for instance block A and block B in the dataflow will be the same cube, and block A contains the Rule to be applied. Also, I used to do not extend the domain on the entity in which the rule is applied and I get the proper result.

I just want to understand the impact between your recommandation and the logic I used to apply (this one was a recommendation of an older Board TT.

thanks and regards,

Federica Antonelli

Hello @Nicolas BENDONGUE

The approach you are describing to use the same cube as source and target is very common and used across the community.

Normally is better to avoid circular references in the layout and so avoid to have the same cube as source and target. The impact is on the performance and that's why we are suggesting the approach with two cubes. It's possible based on your context (number of dimensions of the cube, active selection in the procedure…) that you do not see a direct impact but as general best practice is better the approach suggested.

In general, the suggestion is to limit ad much as possible data materialization and keep the rule applied at the layout level. When this is not possible just consolidate the portion of data you need and not the entire rule. With that in mind, we are suggesting the two cubes approach.

About your comment on the extend option. Probably the entity is already set as dense in the system. Let us know!

Nicolas BENDONGUE

Hello @Federica Antonelli

thank you for your feedback. About the extend option, the Entity on which the rule is applied is not set as dense in the system.

Leone Scaburri

Working on Board 12 the sparse structure does not have any impact on the calculation domain of the dataflows, rule consolidation included, dataflows work only on the existing combination of the calculation domain and don’t consider the existing combinations in the sparsity.

So if the target of your rule calculation are already existing tuples of one of the cube involved in the calculation the extend option is not required, but if tomorrow you want to create new tuples you want to make sure the extend option is flagged.

Federica Antonelli

Hello @Nicolas BENDONGUE

Yes @Leone is right. When you consolidate a rule in most of the cases you are writing on combinations that do not exist. That's why the indication is to apply the extend option.

You can also refer to the manual for that:

https://www.boardmanual.com/2021/summer/data-modeling/2_Database_design_functions/6_Rules/using-rule-dataflow.htm