Are there line items that can be placed in a systems module rather than within a calculation module?
When is this a problem?
Look for functions that result in data that doesn't change (i.e., the parent of a line item). Placing these functions in a systems module means that the calculation is run once and can be referenced by multiple line items in multiple modules. Look for: PARENT(PARENT(PARENT(line item))), text field concatenations, and time functions such as START(), CURRENTPERIODSTART(), CURRENTPERIODEND(), etc.
Why is this a problem?
Calculations that are repeated can slow down performance. When these types of functions are included as part of longer formulas, think about how many times they may run if they are included in multiple line items and for multiple list members. It is better for performance for the calculation to run once and be referenced many times.
How to correct:
Locate the functions that can be moved to a systems module. Create the systems module, add the function as a line item, and then change the original calculation to reference the line item in the systems module.
More information:
Planual 2.02-18
Planual 2.02-03
Planual 2.01-04
Creating Systems Modules
Build a Time Systems Module
Reduce Calculations for Better Performance
Are there any formulas that that combine text strings?
When is this a problem?
The use of text strings in Anaplan cannot be avoided, so you must ensure that the joins are done in the most efficient way to minimize the impact on performance. Be sure to read the Formula Optimization article. You can find the link in the More Information section below.
Why is this a problem?
Combining text strings takes a lot of memory and can impact performance.
How to correct:
Create systems modules that only include the dimension needed. For example, if you want to create a code that consists of an item code and a company code joined together, you would create three systems modules. One that holds the item data, another that holds the company data, and a third that holds the combination of the two.
More information:
Formula Optimization
String Concatenation
Are there any formulas that are daisy chains?
When is this a problem?
Daisy chain formulas are always a problem. Daisy chains happen when you have a line item in module A that is referenced in module B, which is then referenced in module C, and so on.
This creates a situation where there is a long dependency chain that has to be calculated. Instead, each module should directly reference the data. The line item in module A is referenced directly in module B and is also referenced directly in module C.
Why is this a problem?
Daisy chain formulas require the engine to work harder. Whenever one data point that is used in the calculation changes, the entire sequence must be re-calculated.
How to correct:
Break up these formulas. Place parts that need to be referenced multiple times in a systems module, creating a single source of truth.
More information:
Planual 2.02-19
This article explains daisy chains
Are there long formulas, such as complex IF THEN ELSE statements?
When is this a problem?
If you can't explain the purpose of a formula in one sentence, the formula is too long.
Why is this a problem?
Long formulas, such as complex IF THEN ELSE statements, are difficult to understand, audit, and maintain. Complex formulas use valuable processing time and require the engine to work harder than necessary.
How to correct:
Simplify complex IF THEN ELSE statements and put the most common condition first. Break up formulas into multiple line items. If your formula includes more than 10 IF THEN calculations, think about using a LOOKUP. In some situations, you can also consider using a line item subset.
More information:
Planual 2.02-17
Planual 2.02-18
Formula Structure for Performance
Thinking Through Your Formulas
Line Item Subsets Demystified
Manipulating Data with Line Item Subsets
Decreasing the Length of Your Formulas
Is there an excessive use of the FINDITEM function?
When is this a problem?
The FINDITEM function requires a lot from the engine, as it is attempting to find an item in a list and it does this for every cell that is related to that line item. This is especially true when the list includes null values. Excessive use depends on your model and the lists it is being run against.
Why is this a problem?
Using FINDITEM excessively makes the engine work harder than necessary.
How to correct:
Check the data for null values before running FINDITEM. So where prod_text is the name of the list, the formula to check for null values is IF ISNONBLANK(prod_text) THEN FINDITEM(prod_text) ELSE blank. This function is often used when loading data. Think about loading the data into spoke models from the data hub as list formatted line items rather than text.
More information:
Planual 2.02
Are there SUM and LOOKUP combinations?
When is this a problem?
Having SUM and LOOKUP in the same line item is always a problem and can cause performance issues.
Why is this a problem?
Using SUM and LOOKUP in the same formula generally causes large formula calculations, especially if time is a dimension or when the source and target structures are very different.
How to correct:
Split SUM and LOOKUP into different modules and line items.
More information:
Planual 2.02-08
The LOOKUP Function
How to Use the SUM Function
Compound Functions: SUM, LOOKUP, SELECT
Is the TEXTLIST function used?
When is this a problem?
Always a problem.
Why is this a problem?
Using TEXTLIST requires a lot of memory.
How to correct:
Using two-dimensional modules and Boolean flags with ANY is a good alternative. Click here to see an ANY instead of TEXTLIST document. Use FIRSTNONBLANK and LASTNONBLANK with ANY and ALL.
More information:
Planual 2.02-11
Anapedia: x[ANY:y]
Do any formulas use the SELECT function, especially with time?
When is this a problem?
Using SELECT is considered hardcoding and not recommended.
It is OK to use SELECT for versions and general time periods.
Why is this a problem?
Using SELECT is hard coding and not recommended. When you use SELECT with a specific list item or time, you limit the ability to update those list items. This is especially an issue with time because a change will be required every year. You’ll need to locate every line item that references the SELECT function and make updates. These changes can only be performed by a Workspace Administrator (WSA), compared to the solution we recommend, which allows an end user to make changes.
How to correct:
Create a module with no dimensions to hold assumptions for Time, and other "SELECT" values or use LOOKUP instead of SELECT. For example, in this module, create a line item called Current Year, format it as a Time Period and select year. You can then use a lookup formula to reference this line item. When the current year changes, you update the value in that line item and it will automatically update all the formulas that reference it.
More information:
Planual 2.02-14
Using SELECT
Planual 2.02-12
Avoiding Hard Coding
Do any of the line items use conditional formatting with summaries turned on?
When is this a problem?
This is a problem because conditional formatting is done at the detail level, not the parent level.
Why is this a problem?
The summary is unnecessary.
How to correct:
Turn off the Summary settings.
More information:
Planual 2.03-01
