Using Advanced Formulas to Optimize Performance

In this lesson, we'll explore performance topics related to advanced formulas, however, we will not cover how to create advanced formula data action steps or how the scripts are processed.

There are two main ways to create an advanced formula step in a data action. The visual editor allows business users with no expertise in programming to design formulas by dragging and dropping rather than directly writing scripts, however there are limitations and the script editor is used to define and design complicated business scenarios.

Topics related to creating, configuring, and running advanced formula data actions are covered in the following lessons in the Leveraging Advanced Features in SAP Analytics Cloud for Planning course:

1. Using the Visual Editor to Create an Advanced Formula Data Action
2. Using the Script Editor to Create an Advanced Formula Data Action

Advanced formula steps in data actions give you a lot more flexibility to run calculations on planning model data. The calculation results are stored in the base cells of the model, which means that you can run calculations and copy data across different sets of filters.

The syntax and semantics of advanced formula steps in SAP Analytics Cloud data actions can be found in the Advanced Formulas Reference Guide.

It's important that you are familiar with the general behaviors of advanced formulas and how the calculations are processed.

Four key things to understand:

1. Advanced formulas are created as a data action step. Data action steps are executed sequentially. The next data action step is calculated based on the result of the previous one.
2. In advanced formulas, each line of instruction inherits the calculation results of previous lines of instructions.
3. Advanced formulas performs all calculations only with leaf-level members that don’t have any account formulas defined. Any other members that have child members are not supported.
4. In a RESULTLOOKUP function, values of different data slices returned by RESULTLOOKUP are not aggregated (unless you explicitly define them in an AGGREGATE_DIMENSION function).

The SAP Help Portal article, Understand General Rules for Advanced Formula Calculations for Planning is a reference for explanations and examples for the general rules listed above as well as information on topics such as calculation scope and calculation rules.

When writing an advanced formulas step in a data action, you’ll want to consider your script’s performance. As with writing scripting for widgets, in an advanced formula in a data action, there are also often a few different ways to get to the same calculation results.

In this lesson, we will look at two scenarios:

1. Evaluating three formulas used in an advanced formulas step that can perform the same task.
2. Optimizing a data action with an advanced formula step that uses the Link() function.

Evaluating the script and picking the most efficient one will save time for your planning users. In this scenario, we use three separate advanced formulas that have been designed to perform the same task. You can see the three data action starters in the following story. While all three will perform the calculation correctly, evaluating the formulas and picking the correct one to suit your requirements will ensure optimal performance of our planning story.

Let's take a closer look at a simplified planning story. We will look at labor costs for one Product, in this example, Tennis Shoes.

Your labor costs were manually planned for 2025 for region Pacific and region Midwest. For 2026, you want to plan labor costs but only for the Pacific. To do this, you will use an advanced formula to copy the 2025 data and update the 2026 plan with a fix revaluation percentage of 10%. This means that the labor costs of Jan 2025 are copied into Jan 2026 and then the copied value is increased by 10%. The same for February to December.

You look at three different advanced formulas and evaluate them to see which one should be the most efficient for your calculation, in order to optimize the story performance.

The planning tables are configured in the same way, with the exception of the Date filter.

In the first table, the Date filter is set to Jan (Q1/2025), Feb (Q1/2025), Mar (Q1/2025), Apr (Q2/2025), May (Q2/2025), Jun (Q2/2025), Jul (Q3/2025), Aug (Q3/2025), Sep (Q3/2025), Oct (Q4/2025), Nov (Q4/2025), and Dec (Q4/2025).

In the second table, the Date filter is set to Jan (Q1/2026), Feb (Q1/2026), Mar (Q1/2026), Apr (Q2/2026), May (Q2/2026), Jun (Q2/2026), Jul (Q3/2026), Aug (Q3/2026), Sep (Q3/2026), Oct (Q4/2026), Nov (Q4/2026), and Dec (Q4/2026).

1. FOREACH: You create a data action with an advanced formulas step, using the FOREACH function to calculate the labor costs.

You use the following script in the advance formulas data action step:

12345678
MEMBERSET [d/Date] = "202601" To "202612"
MEMBERSET [d/Account] = "H121100"

FOREACH [d/Date]
  IF [d/Entity] = "REG0004" THEN	
		DATA() = RESULTLOOKUP([d/Date]=PREVIOUS(12)) * 1.1
ENDIF
ENDFOR

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, the script runs the FOREACH function first, which creates unnecessary calculations.

2. FOREACH Function Nested in an IF Statement: You create a second data action and update the script to use conditions to reduce the scope of FOREACH loops. You know that IF statements let you define filters for specific dimensions. Regardless of the MEMBERSET scope, these filters will apply between the IF keyword and the corresponding ENDIF keyword.

When using FOREACH functions and IF statements based on the same dimension, you can nest the FOREACH function inside the IF statement, where possible, to reduce the number of looped calculations.

You use the following script in the advance formulas data action step:

12345678
MEMBERSET [d/Date] = "202601" To "202612"
MEMBERSET [d/Account] = "H121100"

IF [d/Entity] = "REG0004" THEN
	FOREACH [d/Date]
		DATA() = RESULTLOOKUP([d/Date]=PREVIOUS(12)) * 1.1
	ENDFOR
ENDIF

By nesting the FOREACH function within the IF statement, you speed up the script by only running the FOREACH calculation on data from the REG0004 –Pacific entity.

3. DATA() Function: In the third data action, you use remove the FOREACH function and only use the DATA() function. The FOREACH function is not needed because the calculation doesn’t depend on its own results. This is the most efficient script to complete the task, leading to better performance.

You use the following script in the advance formulas data action step:

123456
MEMBERSET [d/Date] = "202601" To "202612"
MEMBERSET [d/Account] = "H121100"

IF [d/Entity] = "REG0004" THEN
	DATA() = RESULTLOOKUP([d/Date]=PREVIOUS(12)) * 1.1
ENDIF

Linking two models together is often required when the source model contains detailed data that you want to view in the target model. This can be done in member formulas as well as advanced formulas, but we'll look at an example using advanced formulas. For example:

• You link your target model, a Finance model, to two other source models, a Payroll model and HR model. You require the payroll account in the P&L from the Payroll model and salaries from the HR model.
• You have a Pricing model that is often called by other models to perform Price*Volume calculations.

Scenario: You are calculating labor costs, which requires data from two models, the Expenses model and the HR model. You need to view labor rates as part of the calculation, so, you have a data action containing an advanced formula step that uses the LINK() function. The Link() function is used to copy or read data from a different model than the default model in the data action.

The advanced formula:

123
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "[2025]"))
MEMBERSET [d/P00D_CostCenter] = (BASEMEMBER([d/P00D_CostCenter].[h/H1], %CostCenter%))
DATA([d/Account] = "Labor") = RESULTLOOKUP([d/Account] = "LHours") * LINK([P00M_HR_Plan], [d/Account] = "LRate", [d/Personnel_Area] = "AMER", [d/Version] = "public.PubHrPlan")

Now, let's break down the calculation:

• DATA([d/Account] = "Labor"): This sets up the calculation for the labor costs.
• RESULTLOOKUP([d/Account] = "LHours"): This reads the number of labor hours from the P00_EXP_PLAN.
• LINK([P00M_HR_Plan], [d/Account] = "LRate", [d/Personnel_Area] = "AMER", [d/Version] = "public.PubHrPlan"): This function links to the P00M_HR_Plan model and retrieves the value from the LRate account where the personnel area is AMER and the version is public.PubHrPlan.
• Multiplication: The labor cost is then calculated by multiplying the number of labor hours by the labor rate.

Or, simply LINK (Labor Costs = Labor Hours * Labor Rate). Labor costs (written to the P00M_HR_PLAN model) equals labor hours, read from the P00_EXP_PLAN, multiplied by the labor rate, read from the P00M_HR_PLAN model at runtime using the Link() functionality.

You have performance issues in the story. Looking at the formula, you can see that when you access data for one account that is linked to another model, both models are queried at the same time in order to perform the calculation. This means that the amount of data that has to be processed is much bigger, impacting on system performance.

Optimization: Use a data action with a cross-model copy step and combined with an advanced formulas step to complete the calculation instead of using dynamic links across models (i.e. LINK() function). This approach is particularly effective when the planning process does not require that both models are used for data collection at the same time, but instead, when the planning process is more sequential with one model being updated before another.

Embedded data actions have to refer to the same model, so a data action with two steps is used in this scenario.

Step 1 - Copy labor rates: In the first step of the data action, a cross-model copy step copies the labor rate from the HR model to the Expenses model.

Step 2 - Calculate labor costs: In the second step of the data action, an advanced formula calculates the labor costs and writes it to the Expense model.

The advanced formula:

123
MEMBERSET[d/Date]="202501" To "202512"
MEMBERSET[d/Measures]="SignedData"
DATA([d/Account]="Labor")=RESULTLOOKUP([d/Account]="LHours")* RESULTLOOKUP([d/Account]="LRate")

Now, let's break down the calculation:

• DATA([d/Account] = "Labor"): This sets up the calculation for the labor costs.
• RESULTLOOKUP([d/Account] = "LHours"): This reads the number of labor hours.
• RESULTLOOKUP([d/Account] = "LRate"): This reads the labor rate.
• Multiplication: The labor cost is then calculated by multiplying the number of labor hours by the labor rate.

By copying the labor rates from the HR model to the Expenses model first, the advanced formula performs a simpler RESULTSLOOKUP of the value, resulting in a smaller amount of data that has to be processed, reducing the impact on the system and improving performance.

Advanced formula scripts can be used to define and design complicated business scenarios in SAP Analytics Cloud planning stories. Like with adding scripting to extend widgets, often a variety of different scripts can be used to achieve the same result. As such, in this lesson we focused on one scenario where three different formulas were used in the same story to calculate plan labor values, highlighting the importance of optimizing the scripts that are used. If you are using advanced formulas and users are experiencing performance issues, then it's worth investigating the advanced formulas scripts to see if they can be optimized.

More strategies for creating optimized advanced formulas can be found in the SAP Help Portal article Optimize Advanced Formulas for Better Performance. It includes instructions on how to:

• Reduce the number of FOREACH functions and limit their scope as much as possible.
• Remove unnecessary IF statements and organize IF statements to reduce the calculation scope.
• Reduce the number of RESULTLOOKUP functions used in a single calculation.
• Avoid using the "= NULL" comparison, if possible.
• Reduce the number of DATA functions.
• Use dimension aggregation functions where possible.