Managing Data Action Performance

When writing an advanced formula step in a data action, 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 planners.

1. Check the calculation 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.
2. 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.
   • 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.
3. 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.
4. 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.
5. 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.
6. Reduce the number of DATA functions: Using numerous 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.
7. 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.
8. Define as many MEMBERSET statements as possible to focus your calculation to specific dimension members.