Optimize Advanced Formulas 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

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

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

Optimized 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:

With the BREAK statement, the FOREACH calculation stops running before Revenue reaches 200.

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:

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.

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.

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

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

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
  

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:

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:

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

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:

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:

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