Optimize Advanced Formulas for Better Performance

When writing an advanced formulas step in a data action, you’ll want to consider your script’s performance. There are often a few different ways to get to the same calculation results, and picking the fastest one will save time for your planning users.

Follow the tips below to get the best performance out of your advanced formulas script.

Check the Estimated Function Scope

After you validate your script without errors, you can check the calculation scope for specific functions. This helps you get good performance and correct calculation results in an advanced formulas step.

Check the scope by hovering over one of the supported functions:
  • Functions that show the scope of all dimensions: DATA, RESULTLOOKUP, LINK, CARRYFORWARD, and DELETE.

  • Functions that show the scope of a specified dimension: ATTRIBUTE and date functions (DAY, DAYSINMONTH, DAYSINYEAR, MONTH, DATERATIO, DATEDIFF, YEAR).

The Estimated Function Scope window shows how many members are included for each dimension, and where the scope was restricted:
The Estimated Function Scope shows the number of dimension members included in a function's scope.
Checking estimated function scope

If all members are included for any dimensions, they are shown in the Dimensions Not Restricted list. Check this list to make sure it doesn't include many dimensions with a lot of members.

For better performance, restrict the scope as much as possible and avoid running calculations on a large number of members for multiple dimensions.

For general information about calculation scope, see Calculation Scope.

Note
  • The model's fact data isn't checked when determining the calculation scope, which means that some types of filters can't be included in the Estimated Function Scope window. These include IF conditions that apply value filters or that make comparisons between variables and numbers.

  • The number of members may change for some filters if dimension members are added, removed, or changed. These include filters that use dimension attributes, BASEMEMBER selections, and TO keywords, for example: MEMBERSET[d/Stores].[p/City] = ("New York")

  • For scopes based on external parameters that use prompts, the estimated function scope is evaluated based on the default values. With no default value, you may see a minimum and maximum potential scope for the dimension.

Reduce the Number of FOREACH Functions and Limit Their Scope as Much as Possible

FOREACH functions are helpful when you need to run a calculation for multiple dimension members, and the result from each member becomes the input for the next member. For example, you might use a simple FOREACH function to plan revenue with a month-over-month growth of 10%:

Sample Code
MEMBERSET [d/DATE] ="201801" TO "201812"
MEMBERSET [d/ACCOUNT] ="REVENUE"

FOREACH [d/DATE]
    Data() = ResultLookup([d/DATE]=PREVIOUS(1)) * 1.1
ENDFOR 

However, FOREACH functions repeat the calculation for each member included in their scope, so they can slow down performance.

In many cases, you can either replace a FOREACH function with a different function, or reduce the scope of the FOREACH function for better performance.

Replace FOREACH Functions with DATA Functions:

Say you want to calculate revenue with a 10% increase from the same month of the previous year instead. You could get the correct results with a FOREACH, but it’s not needed because the calculation doesn’t depend on its own results. In this case, it’s faster to use a DATA() function.

Code With Unnecessary FOREACH Function

Sample Code
MEMBERSET [d/DATE] = "201801" TO "201812"
MEMBERSET [d/ACCOUNT] = "REVENUE"

FOREACH [d/DATE]
	DATA() = RESULTLOOKUP([d/DATE] = PREVIOUS(12)) * 1.1
ENDFOR

Optimized Code

Sample Code
MEMBERSET [d/DATE] = "201801" TO "201812"
MEMBERSET [d/ACCOUNT] = "REVENUE"
DATA() = RESULTLOOKUP([d/DATE] = PREVIOUS(12)) * 1.1

Use the BREAK Keyword to Avoid Unnecessary Iterations in FOREACH Functions

You may be able to add the BREAK keyword to a FOREACH function to stop running the loop when a certain condition is met. For example, you can stop running depreciation calculations when the residual value reaches zero.

In this example, a BREAK keyword stops a FOREACH loop before Revenue reaches 200:

Results of the FOREACH calculation with a BREAK statement
With the BREAK statement, the FOREACH calculation stops running before Revenue reaches 200.
Sample Code
MEMBERSET [d/DATE] ="201801" TO "201812"
MEMBERSET [d/ACCOUNT] ="REVENUE"

FOREACH [d/DATE]
    IF ResultLookup([d/DATE]=PREVIOUS(1)) * 1.1 > 200
		BREAK
    ENDIF    
    Data() = ResultLookup([d/DATE]=PREVIOUS(1)) * 1.1
ENDFOR 

Replace FOREACH Functions with CARRYFORWARD Functions

If you just need to add and subtract values in a calculation that repeats for multiple time periods, you can use the CARRYFORWARD function for better performance. In this case, you’ll use a separate dimension to record the flow of values. For example, to increase revenue by $10 000 each month, use this formula:

Sample Code
MEMBERSET [d/DATE] = "202001" TO "202012"
MEMBERSET [d/ACCOUNT] = "REVENUE"

DATA() = CARRYFORWARD([d/FLOW], "Opening_Balance", "Closing_Balance", "Opening_Balance" + 10000)

Reduce the Scope of FOREACH Functions

When FOREACH functions are needed, you’ll want to reduce the number of times that the calculation repeats. You can do this by reducing the number of dimensions in the FOREACH scope, filtering the members of these dimensions, and limiting the calculation to booked values only.

First, try to avoid using multiple dimensions in the FOREACH scope. For example, if you write a function such as FOREACH [d/Date], [d/Region], the calculation repeats itself for every combination of Date and Region leaf members that haven’t been filtered out yet.

Next, filter the members that determine how many times the FOREACH function repeats. You can do this using MEMBERSET statements, or by nesting the FOREACH function within an IF statement.

Sample Code
MEMBERSET [d/DATE] = "202001" TO "202012"
MEMBERSET [d/ACCOUNT] = "REVENUE"

IF [d/ ENTITY] = "US" THEN
    FOREACH [d/DATE]
        DATA() = RESULTLOOKUP([d/DATE]=PREVIOUS(1)) * 1.1
    ENDFOR 
ENDIF

Finally, you can use the FOREACH.BOOKED function if your calculation only needs to run on booked values.

Sample Code
MEMBERSET [d/DATE] = "202101" TO "202112"
MEMBERSET [d/ACCOUNT] = "REVENUE"

FOREACH.BOOKED [d/DATE]
    DATA() = RESULTLOOKUP([d/DATE]=PREVIOUS(1)) * 1.1
ENDFOR 

Remove Unnecessary IF Statements and Organize IF Statements to Reduce the Calculation Scope

Complex scripts usually involve several IF statements, often nested within one another. Checking all these conditions can slow down performance, so it’s best to remove unneeded IF statements. Also, try to arrange your IF statements to reduce the overall number of calculations.

Replace IF Functions with DATA and RESULTLOOKUP Filters

Remove IF statements that only return a single member. For example:

IF [d/Account] = "A" THEN
    DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
ENDIF

You can apply the filter within the DATA and RESULTLOOKUP functions for quicker results:

DATA([d/Account] = "A") = RESULTLOOKUP([d/Product] = "X" , [d/Account] = "A") + RESULTLOOKUP([d/Account] = "B")

Consider the Order of IF Statements

If your script involves several IF statements, try to arrange them so that the overall number of calculations is reduced:

  • When some calculations are repeated in different IF statements but are based on a similar condition, try to group them under a single IF function instead. For example:

    IF [d/Date] = "202001" THEN
        IF [d/ENTITY] = "US" THEN
            DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
            DATA([d/FLOW] = "#") = RESULTLOOKUP([d/Date]=  PREVIOUS(12)) * 0.5
        ENDIF
    ELSEIF [d/Date] = "202002" THEN 
        IF [d/ENTITY] = "US" THEN
            DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
            DATA([d/FLOW] = "#") = RESULTLOOKUP([d/Date]=  PREVIOUS(12)) * 0.5
        ENDIF
    ELSEIF [d/Date] = "202003" THEN
        IF [d/ENTITY] = "US" THEN
            DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
            DATA([d/FLOW] = "#") = RESULTLOOKUP([d/Date]=  PREVIOUS(12)) * 0.5
        ENDIF
    ENDIF
    
    

    For better performance, you can filter for the data from the US first, and then combine the time periods into a single IF statement:

    IF [d/ENTITY] = "US" THEN
        DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
    
        IF [d/Date] = ("202001", "202002", "202003") THEN		 
            FOREACH[d/Date]
                DATA([d/FLOW] = "#") = RESULTLOOKUP([d/Date] =  PREVIOUS (12)) * 0.5
            ENDFOR
        ENDIF
    ENDIF
    
  • Use conditions to reduce the scope of FOREACH loops. For example, when using FOREACH functions and IF statements based on the same dimension, nest the FOREACH function inside the IF statement where possible to reduce the number of looped calculations.

    This script runs the FOREACH function first, which creates unnecessary calculations:

    FOREACH[d/Date]
        IF [d/ENTITY] = "US" THEN
            DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
        ENDIF
    ENDFOR
    

    By nesting the FOREACH function within the IF statement, you speed up the script by only running the FOREACH calculation on data from the US:

    IF [d/ENTITY] = "US" THEN
        FOREACH[d/Date]
            DATA() = RESULTLOOKUP([d/Product] = "X") + RESULTLOOKUP([d/Account] = "B")
        ENDFOR
    ENDIF
    

Reduce the Number of RESULTLOOKUP Functions Used in a Single Calculation

RESULTLOOKUP functions are the main performance drivers for data actions, because each one retrieves model data from the database. In particular, you can get better performance by avoiding multiple RESULTLOOKUP functions in a single calculation.

Consider this calculation:

Sample Code
RESULTLOOKUP("A")*RESULTLOOKUP("B")+RESULTLOOKUP("A")*RESULTLOOKUP("C")+RESULTLOOKUP("A")*RESULTLOOKUP("D")

By applying arithmetic, you can rewrite it with fewer RESULTLOOKUP functions:

Sample Code
RESULTLOOKUP("A")*(RESULTLOOKUP("B")+RESULTLOOKUP("C")+RESULTLOOKUP("D"))

Avoid Using the "= NULL" Comparison if Possible

Using the "= NULL" comparison can slow down your data action, because the script needs to check each member to see if it’s unbooked.

For example, consider a script that copies the sales price of each product in the US from 2019 to 2020. There is a condition that if a sales price didn’t exist in the previous year, the product should use the default price from HQ.

Case 1: This script checks whether there’s a price to copy for the United States. If so, it copies that price and if not, it copies the price from HQ.

Sample Code
IF RESULTLOOKUP([d/Time] = "201901", [d/Entity]= "US") != Null THEN
    DATA([d/Time] = "202001", [d/Entity]= "US") = RESULTLOOKUP([d/Time] = "201901", [d/Entity]= "US")
ELSEIF RESULTLOOKUP([d/Time] = "201901", [d/Entity]= "US") = Null THEN
    DATA([d/Time] = "202001", [d/Entity]= "US") = RESULTLOOKUP([d/Time] = "201901", [d/Entity]= "HQ")
ENDIF

Case 2: This script gives the same result with better performance. It copies the data from HQ first, and then copies the data from the United States. With CONFIG.GENERATE_UNBOOKED_DATA set to its default OFF setting, unbooked members won’t be copied. (Note that these are the same DATA() functions from the previous case, just in different order.)

Sample Code
DATA([d/Time] = "202001", [d/Entity] = "US") = RESULTLOOKUP([d/Time] = "201901", [d/Entity] = "HQ")
DATA([d/Time] = "202001", [d/Entity] = "US") = RESULTLOOKUP([d/Time] = "201901", [d/Entity] = "US")

Reduce the Number of DATA Functions

Using a lot of DATA functions can also cause slow performance. You might be able to use fewer DATA functions when copying data within a dimension, or when assigning initial values.

Use Fewer DATA Functions by Maintaining Target Dimension Members as Attributes of the Source if Possible

If you need to copy values from one set of members to another set within a single dimension, it might be simplest to use several DATA functions:

Sample Code
MEMBERSET [d/ACCOUNT] = ("A","B","C","D")

DATA([d/ACCOUNT]= "W") = RESULTLOOKUP([d/ACCOUNT]= "A")
DATA([d/ACCOUNT]= "X") = RESULTLOOKUP([d/ACCOUNT]= "B")
DATA([d/ACCOUNT]= "Y") = RESULTLOOKUP([d/ACCOUNT]= "C")
DATA([d/ACCOUNT]= "Z") = RESULTLOOKUP([d/ACCOUNT]= "D")

If you want better performance though, you can set the target members in a dimension attribute in your model. For example, in this case the Sister attribute stores the target account:

ID

Description

Type

Sister

A

Account Receivable

AST

W

B

Inventory

AST

X

C

Automobile

AST

Y

D

Computer

AST

Z

W

Cashflow01

EXP

 

X

Cashflow02

EXP

 

Y

Cashflow03

EXP

 

Z

Cashflow04

EXP

 

Now you can use a single DATA function to copy values from each source account to its sister account.

Sample Code
MEMBERSET [d/ACCOUNT] = ("A", "B", "C", "D")

DATA([d/ACCOUNT] = [d/ACCOUNT].[p/SISTER]) = RESULTLOOKUP()

Use Fewer DATA Functions When Assigning Initial Values:

Your script might use some DATA functions just to set values to zero before performing more calculations with them. Try to reduce the number of DATA functions in this case.

First, when you’re using a virtual variable member, you can declare it based on a dimension that isn’t used elsewhere in your calculations. This way, it won’t be affected by other calculations and you won’t need to reset its value.

For example, if you’re using a variable based on the Account dimension for several calculations with different account members, you might need to reset it before each calculation:

Sample Code
VARIABLEMEMBER #ACCOUNT_1 OF [d/Account] 
INTEGER @ZERO = 0
IF [d/Account] = "A" THEN
	DATA([d/Account] = #ACCOUNT_1) = @ZERO // Reset the variable to 0
	DATA([d/Account] = #ACCOUNT_1) = RESULTLOOKUP([d/Date] = "202001") * RESULTLOOKUP([d/Account] = "D")
ENDIF 
IF [d/Account] = "B" THEN
	DATA([d/Account] = #ACCOUNT_1) = @ZERO // Reset the variable to 0
	DATA([d/Account] = #ACCOUNT_1) = RESULTLOOKUP([d/Date] = "202001") * RESULTLOOKUP([d/Account] = "D")
ENDIF 

Instead, declare the variable based on another dimension such as Audit_Trail, so that there are separate values for each account:

Sample Code
VARIABLEMEMBER #AUDIT_1 OF [d/Audit_Trail] 
INTEGER @ZERO = 0

DATA([d/Audit_Trail] = #AUDIT_1) = @ZERO // Reset the variable to 0
IF [d/Account] = ("A", "B") THEN
DATA([d/Audit_Trail] = #AUDIT_1) = RESULTLOOKUP([d/Date] = "202001") * RESULTLOOKUP([d/Account] = "D")
ENDIF

Use Dimension Aggregation Functions Where Possible

Together, the AGGREGATE_DIMENSIONS, AGGREGATE_WRITETO, and MODEL functions let you aggregate dimension members together before running the step's calculations. You can achieve the same results by applying member filters, but the performance is slower.

For example, the following script copies the average amount of the last two years' data to 2021 using aggregated dimensions:

Sample Code
MEMBERSET [d/Date] = "202101" TO "202112" 
MEMBERSET [d/ACCOUNT] = (BASEMEMBER([d/ACCOUNT].[h/parentId], "Total_Income"), BASEMEMBER([d/ACCOUNT].[h/parentId], "Total_Expense"))
MEMBERSET [d/SCENARIO]="#"

AGGREGATE_DIMENSIONS = [d/BIZAREA], [d/CHANNEL], [d/COSTCENTER], [d/DATATYPE], [d/ITEM], [d/SEASON], [d/STORE], [d/INDEX]

AGGREGATE_WRITETO [d/BIZAREA] = "#" 
AGGREGATE_WRITETO [d/CHANNEL] = "#" 
AGGREGATE_WRITETO [d/COSTCENTER] = "#" 
AGGREGATE_WRITETO [d/DATATYPE] = "#" 
AGGREGATE_WRITETO [d/ITEM] = "#" 
AGGREGATE_WRITETO [d/SEASON] = "#" 
AGGREGATE_WRITETO [d/STORE] = "#" 
AGGREGATE_WRITETO [d/INDEX] = "#" 

DATA([d/SCENARIO] = "SCEN_1") = (RESULTLOOKUP([d/Date] = PREVIOUS(12)) + RESULTLOOKUP([d/Date] = PREVIOUS(24))) / 2  
This script uses member filters in the DATA function instead of aggregated dimensions, which gives the same result but has slower performance:
Sample Code
MEMBERSET [d/Date]="202101" to "202112"
MEMBERSET [d/ACCOUNT] = (BASEMEMBER([d/ACCOUNT].[h/parentId], "Total_Income"), BASEMEMBER([d/ACCOUNT].[h/parentId], "Total_Expense")) 
MEMBERSET [d/SCENARIO]="#" 

DATA([d/SCENARIO]="SCEN_1", [d/BIZAREA]="#", [d/CHANNEL]="#", [d/COSTCENTER]="#", [d/DATATYPE]="#", [d/ITEM]="#", [d/SEASON]="#", [d/STORE]="#", [d/INDEX]="#") = (RESULTLOOKUP([d/Date]=PREVIOUS(12)) + RESULTLOOKUP([d/Date]=PREVIOUS(24)))/2