Anaplan Polaris — The power of SUMs

AnaplanOEG

This article is part of a series on Polaris best practices. Click here for more Community content or visit Anapedia for detailed technical guidance.

The Polaris engine was built specifically to unlock the ability to build at natural dimensionality, which in many cases results in very high cell counts. One of the fastest and most optimized functions in Polaris is the SUM function, and we are learning that using it as a replacement for Classic modeling methods can result in massive performance improvements in Polaris in these high cell count dataspaces.

Below we outline some common formula constructs which, at a very high cell count, can perform faster when converted to a SUM.

Example 1: SUMs as a replacement for high cell count LOOKUPs

1A. Use SUM as a direct replacement for LOOKUP

There are times when the “LOOKUP” can be directly swapped for the “SUM” function.

When considering the LOOKUP syntax above, a LOOKUP can be replaced by a SUM when the mappings represent dimensions which are present in the source, the target, and the LOOKUP is on a single list member. In this scenario, you can directly swap the “[LOOKUP:” inside of the formula with “[SUM:” and the calculated result should be identical. However, the calculation effort is substantially reduced at high cell counts.

1B. Using a Line Item Subset

A more complex example of replacing a LOOKUP with a SUM is using a line item subset (LIS) and mapping to create a more performant formula. This is most effective when the LOOKUP is on a dimension that is NOT present in the target module but is present in the source module.

Generic Example

CALC Entity Rates.Conversion Rate[LOOKUP: ADMIN Global Settings.Avg Rate Type]

Instead do:

1. Create an LIS with the target module selected in the LIS. Check the target line item.
   
2. Create a mapping module where the dimension you are trying to lookup over is in the applies to and add a mapping line item with the format of your newly created LIS.
3. Populate only the cell of the list member that was being looked up with the target line item.
4. In the target line item, remove the LOOKUP and replace it with a SUM over the newly created mapping line item.

New formula:

CALC Entity Rates.Conversion Rate[SUM: Conversion Rate Mapping]

(Click to enlarge)

Example 2: SUMs as a replacement for Global Item and List Comparison

Comparisons of an in-cell value to a list member can be poorly performing at very high cell count. For every cell in the line item, it must cycle through the complete list until it finds the true value. Using a SUM instead will perform substantially faster at high cell counts.

Generic example (Boolean result):

Module.List Formatted Line Item = ITEM(List) (or Sys List Attributes.Item)

Instead do:

Module. Numeric Line Item [SUM: Module. Line Item] <> 0

Real example (complexity factor of 399,902):

DAT Opportunities. Account = ITEM(Account)

Instead do:

DAT Opportunities. Count[SUM: Dat Opportunities. Account] <> 0

(Click to enlarge)

This results in a complexity factor of 2 and model open time reduced by 27 min!

Functions like ITEM(), CODE() and NAME() should never been done in a line item with multi-dimensionality, but anywhere a comparison is being done of a list formatted Line Item to a list should attempt to use a SUM if possible. This is especially important at high cell count.

It is important to remember that in Polaris model optimization should always be viewed through the lens of Cell Count and Dimensionality. Most modeling constructs and formulas are performant at Classic model sizes, but as model builders buildwith Natural Dimensionality in Polaris they will see vastly larger cell counts. In these cases, new ways of modeling are emerging which includes using the SUM function in ways we may not have before.

……………..

Authors: Anaplan’s Theresa Reid (@TheresaR) and Jason Blinn (@jasonblinn), Performance and Architecture Directors. Special thanks to Rob Marshall (@rob_marshall) for his contributions.