Calculate once and reference | Model Optimization Team series

modeloptimizationteam · May 30

Hey! Welcome to the Model Optimization team's monthly blog post. This is an eight-part series, following the topics we see occurring most frequently in slow customer models. Our aim is to inform and guide how to build with best practice intentions for these model building topics.

Each month, two of our analysts will take you through how to spot and improve these formula structures! We hope these can help with your model performance.

All the best - Cianna Bramley (Model Optimization Manager)

……

This month's blog is brought to you by Mark Lawrence Del Mundo and Mai Hashad.

In an optimized model, unique calculations are done only once. This may seem like a daunting task, but with practice and mindfulness, this can be achieved. With this, you are on your way to improving your model’s performance.

This concept is so integral to Anaplan that it is discussed in PLANS (the standard for Anaplan modeling), specifically in the Necessary subtopic. Additionally, it is also one of the guidelines in The Zen of Anaplan.

The problem with repeated calculations

Repetitive calculations can slow down performance as the calculation engine is having to recalculate the same items multiple times. Imagine having these calculations as part of longer formulas, in multiple line items, and for multiple list members. This redundancy can quickly increase the number of unnecessary calculations, making your model slower and less efficient. It is better for your model’s performance when the calculation is run once and referenced many times.

Essentially, it is easier for Anaplan to pick up a result that is already calculated than to calculate again. This difference becomes exponentially greater if the formula is complex or spans a large number of cells.

Identifying repeated calculations

There are two types of repeated calculations:

Repeated calculation on a single line item.
You can identify this by going into the blueprint view on a module. Then checking the line items and seeing if there are formulas/formula fragments that appear more than once within the same formula. Here is an example of this:
Repeated calculation on multiple line items.
You can identify this by going into blueprint view on a module/s. Then check the line items and see if there are formulas/formula fragments that appear more than once across multiple line items in the same or different Modules. Here is an example of this:

The Anaplan approach: calculate once and reference

1. Optimizing repeated calculation on a single line item.

Repeated calculations model:

This example shows multiple terms being multiplied by the same item. We will go through a better way to build this formula, however ‘repeated calculations’ may show up in other ways; which would require other forms of optimization (most likely creating new line item(s) to calculate these once, over necessary dimensions, and then referencing in the original formula).

Grouping the repeated calculation

this formula becomes:

(Salary + Bonus + Car Costs + Phone Cost + Medical Cost) * (1 + YEARVALUE(Inflation Rate accrued))

You may have noticed in the module screenshot, the part of the formula that is in bold is the same as the formula in ‘Total Employee Expenses’. Referencing an existing line item instead of doing the calculation again further optimizes this line item /model.

The final optimized formula will be:

Total Employee Expenses * (1 + YEARVALUE(Inflation Rate accrued))

See screenshot below for more details.

Calculate once and reference model:

In this example, the part of the formula:

is only calculated once, as opposed to the repeated calculations model, where it was computed five times. Together with this, the part of the formula that references the existing line item “Total Employee Expenses” instead of summing the individual line items Salary, Bonus, Car Costs, Phone Cost and Medical Cost further optimizes this model’s performance.

2. Optimizing repeated calculation on multiple line items.

Repeated calculations model:

Create a separate line item for the common conditional statement. In this example, that is Start Date <= 'SYS00 Time Settings'.First day of Month AND NOT (Leave Date <= 'SYS00 Time Settings'.Last day of Month AND ISNOTBLANK(Leave Date))

We first create a Started? line item with the following formula:

Next we create another line item Left? with the following formula:

Next, we combine these two line items to achieve the same Boolean calculation as the original model. We’ll name the new line item Employed? with the following formula:

Calculate once and reference model

The optimized model would look like this (using the new ‘Employed?’ line item):

In this example the Boolean argument

Start Date <= 'SYS00 Time Settings'.First day of Month AND NOT (Leave Date <= 'SYS00 Time Settings'.Last day of Month AND ISNOTBLANK(Leave Date))

Will just be calculated once and its result will be saved in the line item Employed?. Succeeding line items like Headcount, Salary, Bonus, Car Cost, Phone Costs and Medical Costs will no longer need to recalculate the same Boolean check and instead will reference the result saved in the Line Item Employed?. This type of model building will provide a better performance as compared to the Repeated Calculation Model.

Performance metrics comparison

Model calculation time head-to-head:

The repeated calculation model’s total calculation time is 3.81 s while the calculate once and reference model’s total calculation time is 3.69 s — an improvement of 3.15%!

Keep in mind that the improvement percentages provided in this example may seem small due to the small list and test model sizes. However, the improvement in performance become exponentially more significant if the formula is complex or spans a large number of cells as is the case with most models.

Summary

Minimizing redundancy in calculations can lead to significant performance improvements. The best practice “calculating once and referencing many times” streamlines your calculations, making your model faster and more efficient. This principle — albeit simple — can significantly improve your model, especially in models with complex formulas or large models.

Questions? Leave a comment!