OEG Best Practice: Formula optimization in Anaplan

Taking a few simple steps can give you significant performance improvements in your Anaplan model. Anaplan was designed to be built by business users, with simple formula constructs and no coding required. Formulas should not need to be long, complex or hard to create and understand. All too often, we see formulas that run to pages and pages, with inefficient and repeated constructs.

My previous article outlines the benefits of breaking down formulas in to manageable chunks, making them easier to understand, but more importantly, allowing the Anaplan engine to work more efficiently. The article can be found in the Formula structure for performance article.

I’d like to expand on that principle here.

As you may remember, PLANS is the over-arching Anaplan modeling standard, and the applicable areas here are P–Performance and N–Necessary. Actually, a word that I like to use is ‘appropriate.’ Create formulas that only use the necessary or ‘appropriate dimensionality,' and you will achieve better performance.

When creating a formula there is a very important question that I’d like you to ask:

“What elements of this line item expression actually affect the calculation?” If any of the lists or dimensions in the line item ‘Applies to’ do not apply an element of a formula, the expression does not belong in this module. Following the DISCO methodology, this formula should be housed in a separate module.

See the Best practices for module design article.

This is probably best illustrated with an example:

Let’s assume we have a module that is dimensioned by Company, Products, and Months. To allow us to connect to another part of the model, we need to create a combined text string that is the code of the Company and the code of the Product. We could create a text formatted line item with the following formula:

CODE(ITEM(Company))&”_”&CODE(ITEM(Products))

This will give the following result:

All good.

However, let’s look at the components of that formula more closely.

CODE(ITEM(Company))

What affects the result of this expression? By the nature of the ITEM() formula, it can only relate to the Company list and nothing else. But that expression is contained in a line item dimensioned by Company, Product, and Month.

What is happening in the background (in the Anaplan engine) is as follows:

The same result is calculated for ALL Products and ALL months even though the expression does not relate to those dimensions.

The same happens for the second expression, CODE(ITEM(Product)).

The same result is returned for ALL Companies for ALL months.

And finally, let’s see what the “_“ is doing:

The text is “calculating” for ALL Companies, for ALL Products, for ALL months.

This is a classic case of over calculating. We do not need to calculate any of these expressions by month, and each expression only needs to calculate for the applicable list, or dimension. This is what I meant by “appropriate dimensionality"; we should only calculate expressions with dimensions that are appropriate to affect the result.

There is a simple rule to apply.

If the dimensions of the line item are greater than the appropriate dimensions for the expression, then the expression should be done elsewhere.

While it is possible to remove some of the dimensionality and create a subsidiary view, it is best practice to house this in a module of its own; it is easier to audit and maintain, and following DISCO, modules should have a clear and defined purpose that can be explained in a simple sentence.

So, taking the example from above, the first CODE() expression should be done in a module that only contains Company (e.g. Company Details), and the second CODE() expression, in a module only dimensioned by Product (Product Details). Following the DISCO methodology, you should have System modules for the Company and Products lists, so this is the natural place to house the new line items.

We can then bring them together in the target module.

This will give significant savings in calculation time. However, we can make this even more efficient. 

If you remember Duncan Pearson’s “Ask me Anything” presentation, he discussed the memory impact of text strings.

We cannot avoid the use of strings within Anaplan, but we can ensure that the “joins” are done in the most efficient way to minimize the impact. In the example above we have three “joins” (Customer Code, “_” and Product Code).

We noted above that the Code expression only varied by the list dimension. The Code calculations are now fine, and as optimal as can be, but the “_” which joins the two strings is effectively still being created for all dimensions; Company, Product and month.

When you need to join text together in this way, it is likely there will be a common pattern used elsewhere within your model. In our example, the Company code could be the prefix for many other text strings. So, following the rule of “calculate once, reference many times,” we should put the &”_” join in the module by Company. As a rule of thumb, make the join in the smaller of the lists. In our case, we have 1,000 Companies and 3,500 Products, so it is more efficient to add this as a suffix to Company Code.

It is likely that the Code for Company line item might be needed in isolation, so it is better to create a new line in the “Company details” and calculate the string there.

The line item formula for Code to use = Code&”_”.

The final step is to replace the formula in the ultimate target with a reference to this intermediate line item.

The following chart shows the improvement in calculation time for the various options discussed above. All of the line items are within the original target module. 

For clarity:

• Code Inefficient is the first calculation structure.
• Code Optimized is the second example, but still with three joins.
• Code Prefix refers to creating the join in the Product Details module (“_1274”) and joining to the Company code.
• Code Suffix is joining the “840_” with the Product code (as shown above).
  

As you can see, the Code Suffix line item is significantly more efficient than joining the three strings together in the target module. This approach does not materially add to the cell count of a mode but as illustrated, has a hugely beneficial effect on model performance.

One final step to ensure that calculations are fully optimized is to always consider whether the summary options are applicable for the calculations. In our example, by default, Text items default to None because combinations at the parent or total levels do not make sense. The code of the Company item is not applicable to its parent. So, for Text formatted items, there is nothing more to do. However, always check if the Summary options are needed.

See the Reduce calculations for better performance article for a reminder of how to reduce calculations in your models.

In summary, thinking logically about what is needed for each part of a calculation and applying small, simple changes can lead to significant improvements in model performance.

Author David Smith.