Performance Best Practices
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.
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%:
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 quite a bit.
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:
MEMBERSET [d/DATE] ="201801" TO "201812" MEMBERSET [d/ACCOUNT] ="REVENUE" FOREACH [d/DATE] Data() = ResultLookup([d/DATE]=PREVIOUS(12)) * 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
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
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:
RESULTLOOKUP("A")*RESULTLOOKUP("B")+RESULTLOOKUP("A")*RESULTLOOKUP("C")+RESULTLOOKUP("A")*RESULTLOOKUP("D")
By applying arithmetic, you can rewrite it with fewer RESULTLOOKUP functions:
RESULTLOOKUP("A")*(RESULTLOOKUP("B")+RESULTLOOKUP("C")+RESULTLOOKUP("D"))
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 the previous year to the current year (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.
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.)
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")
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:
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.
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