Nested Ifs | Model Optimization Team series

modeloptimizationteam
modeloptimizationteam
edited August 19 in Best Practices
ModelOptimizationTeam.png

Hello! 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 Rosa Eliza Dimaano and Mai Hashad.

July-authors.png

This month, we'll look at how the formula "Nested Ifs" influences your model's performance. To ensure the optimal model performance, it is essential to consider the implications of modeling decisions to prevent the computation engine from being overworked.

As discussed in P.L.A.N.S. — the standard of Anaplan modeling — formulas should be built to be logical and performant. Optimizing Nested IF formulas will help work towards these recommendations.

This article provides some suggestions for reducing the amount of computation required on the calculating engine using Nested IFs formula. Simple examples are provided to show the varied solutions to different types of Nested IF structures.

When is this a problem?

An excellent way to identify a problematic nested IF THEN ELSE statement is by finding a clear objective of the formula.

If you can't explain the purpose of a formula in one sentence, the formula is too long.

Why is this bad?

Referencing Planual 2.02-01 Nested IFs, avoid using multiple IFs. Long formulas, such as complex IF THEN ELSE statements are difficult to understand and maintain. Complex and long calculations consume significant processing time and can cause the engine to work harder than necessary.

Depending on the structure, it is better to split the formula into more line items, or alternative constructs like LISS.

How to find it?

There are three ways to find Nested IFs in your model:

  1. Manual Inspection
    1. Search the blueprint of the module and look for a line item with multiple IF THEN ELSE formulas.
      ManualInspection.png
    2. Download the line items and search in the csv file.
      1. Go to Modules and click “Export”.
        Export.png
      2. Click "Run Export".
        RunExport.png
  2. MAPs: this is a free report your BP can provide up to twice per month. It shows Best Practice checks which includes highlighting Nested IF occurrences.
  3. Request Model Optimization reports through Hypercare Analysts or BPs, which analyze the models for best practices and Planual violations including NESTED IFs, as well as providing specific suggestions on how to improve slow formulas.

How do we fix it?

To fix this issue, we suggest the following steps:

  1. For long formulas, decrease the length of the formula by splitting it into multiple line items.
  2. Use a LOOKUP.
  3. Use line item subsets (LISS) where the ‘checks’ provide different outputs for each item in a list dimension.
  4. Combine ‘checks’ which provide the same outputs.

Examples

Example 1: Multiple Nested IFs that can be split into another line item

example1.png

This is a simple example of a common occurrence we see; a large number of IFs with varied outputs. To optimize this line item we can create an intermediate line item.

Step 1: Create intermediate line item:

Example 1 Step 1.png

Setting the Summary to “NONE” to minimize the number of cell count.

Step 2: Reference the intermediate line item to original line item:

Example 1 Step 2.png

Example 2: Using LOOKUP

This example demonstrates using a hardcoded text that increases the calculation duration of the formula. The line item can be optimized by using LOOKUP and by eliminating the hardcoded text.

Example 2.png

Step 1: Create a mapping module containing the converted Kilogram data:

Example 2 Step 1.png

Step 2: Manually input the data:

Example 2 Step 2.png

Step 3: Create a new line item using FINDITEM.

Syntax
: FINDITEM(List_or_Time, Item_to_find)

The FINDITEM function dimensioned based on the “Item to find”. For this case, we will only use “Size” because the item “Sugar(gram)” applies only to "Size".

Example 2 Step 3.png

Note: Make use of IF ISBLANK(Item to find)THEN BLANK if you know there will be occurrences when the source data is empty; this avoids calculating the FINDITEM unless necessary.

Step 4: Finally, reference the module created in step 1 and the line item created in step 3. The formula becomes:

Example 2 Step 4.png

Note: If necessary, you can set the summary to "LASTNONBLANK" or "FIRSTNONBLANK".

The simpler the better, because it is easier to understand and to main instead of using long/complex formulas of IF THEN ELSE.

You can get more details about this from the community article Decreasing the Length of Your Formulas.

Example 3: Using Line Item Subset

Formula:

Example 3.png
Example 3a.png

Step 1: Create Intermediate line Items for the SELECTs

Example 3 Step 1.png

Step 2: Create a LISS (Line Item Subset)

Use the intermediate line item created in step 1 to create a LISS.

Refer to this Community Article for more information on Line Item Subsets. Please be advised of Planual Rule 1.07-01 which refers to the LISS naming convention.

Example 3 Step 2.png
Example 3 Step 2a.png

Line item subsets can use multiple line items and turns these line items into a list that can be used for calculations, but certain limitations apply:

  • Line item subsets can only contain numeric formatted line items.
  • Only one line item subset can be used as a dimension in a module.
  • Although line items can contain formulas, the items in a line item subset can only aggregate to a simple subtotal.
  • Styles on the line items are not transferred over to the line item subset.

See the article Line item subsets demystified for more information.

Step 3: To extract the information from the LISS, create a Collection module.

To create the formula, type COLLECT() and set of the Summary to None. The dimensions of this line item should match the OPE02 Version Analysis module, with the LISS added as an additional dimension.

Example 3 Step 3.png

Step 4: Create a mapping line item.

Example 3 Step 4.png
Example 3 Step 4a.png

Step 5: Apply the new created line items to the old formula.

To create the Formula, use 'COL01 Versions'.Collect[LOOKUP: Version Label]

Example 3 Step 5.png

Example 4: Combining similar outputs

Example 4.png

Here we have a basic example showing two outputs which are identical. These can be merged:

Example 4a.png

You could also create boolean line items in the Systems module for the ‘country’ list (eg. SYS Country.EMEA Countries) and tick any relevant items for each section. This would help reduce multiple checks on countries for each output.

Summary

Simple is better! Extended Nested IF THEN ELSE statements can negatively impact the performance because it turns the formula into a single-threaded calculation. Keep in mind that it is preferable to split the formulas into several line items or use a LISS rather than compressing all of them into a single large line item.

Questions? Leave a comment!

…..

Check out the other blogs in the series:

Tagged:

Comments

Categories