Optimizing Performance in Stories That Use Member Formulas

In a perfect world, every calculation in a story would run seamlessly every time new data is entered, with no impact on system performance.

In reality, when designing calculations, it's important to consider an approach that leverages both real-time calculations such as formulas, and asynchronous calculations like advanced formulas. Using only member formulas for all calculations generates a burden on your overall system performance. A healthy balance is required to be able to offer users high availability and responsiveness rates, together with achieving the desired business objectives.

It can be temping to use member formulas exclusively, because it seems the simpler option when building a model. However, consider their use and add member formulas with care, for example, for ratio calculations. calculations that must be performed at every level or a hierarchy, and for calculations that a planner needs to see reflected during data entry. To optimize performance, consider advanced formulas, leaving calculations to be triggered by the user using a data action starter.

The SAP Help Portal article, Formulas and Calculations is a reference for explanations and examples for the general rules relating to formulas and calculations in SAP Analytics Cloud.

When adding member formulas to a story, it's important to consider not only performance considerations, but how the member formulas will be used. For example, when using ResultsLookup and Lookup formulas.

With ResultsLookup formulas, the dimensions that are defined in the formula need to be in the current drill state and you can only filter on one value. Since value driver trees only include information about the Account and Time dimensions, ResultLookup formulas often don't show any data in value driver tree nodes.

With Lookup formulas, the dimensions defined in the formula don't need to be in the current drill state, and you can maintain more than one member in the filter expression.

Let's take a closer look at a model and the dimension tables for Product, Country, and Account. The following formulas have been applied in the Account dimension:

• Lookup:LOOKUP([Expenses],[d/Product]=("Paper","Glue"))
• ResultLookup:RESULTLOOKUP([Expenses],[d/Product]="Paper") + RESULTLOOKUP([Expenses],[d/Product]="Glue")

Now, let's look at the story were data has been entered in the top table, Table 1.

1. Table 1: In this table, values were entered for products Glue, Plastic, and Paper for France and Germany. The total returned for Glue and Paper is 53 EUR.
2. Table 2: This table has drill state on Account and Product with aggregation on Country.
   • If you check the Product dimension in the table, Lookup shows the same total value for each product, including Plastic, which had not been selected in the formula.
   • The values in the ResultLookup account are grayed out. The dimensions that are defined in the formula need to be in the current drill state, including the filter field in the ResultLookup formula.
3. Table 3: This table has drill state on Account with aggregation on Product and Country.

   There are no values in the ResultLookup account. The dimensions that are defined in the formula need to be in the current drill state, including the filter field in the ResultLookup formula.

Remember, member formulas are pre-calculated and stored within the data model. This can result in better performance for large datasets or complex models, as the calculations are done once rather than every time a query is run. Advanced formulas, however, support complex conditional logic, allowing you to create formulas that adapt based on certain conditions. This is essential for scenarios requiring dynamic calculations.

Generally speaking, consider advanced formulas over member formulas when you need to tackle large volumes, need to be able to decouple calculations and data entry, when you want users to run the formula on demand.

Scenario: You need to perform a series of calculations to determine different levels of contribution margins for the Toys product dimension for 2025. In your model, you add the following nested member formulas to the Account dimension:

In this video, we will take a closer look at how the model is set up with nested member formulas and then at an optimized story where data entry and calculations are decoupled, allowing users to select when the contribution margin calculations happen.

You have a story with performance concerns. In your model, you have the following nested member formulas to the Account dimension:

• Contribution Margin I = Revenue - Cost of Goods Sold: [Revenue]- [Cogs]
• Contribution Margin II = Contribution Margin I – Costs Fix: [ContribMargI]- [CostsFix]
• Contribution Margin III = Contribution Margin II – Material Overhead Overall: [ContribMargII]- [MatOverheadOver]

To optimize the story and model, you decouple the calculations and data entry. You remove the member formulas from the model's Account dimension and create an advanced formulas step in a data action.

In the data action (shown above), you use the following script in the advance formulas data action step:

1234567
MEMBERSET [d/P00D_CostCenter]="Sales"
MEMBERSET [d/U00D_Product]="TOYS"
MEMBERSET[d/Date]="202501" To "202512"
MEMBERSET[d/Measures]="SignedData"
DATA([d/Account]="ContribMargI")=RESULTLOOKUP([d/Account]="Revenue")-RESULTLOOKUP([d/Account]="Cogs")
DATA([d/Account]="ContribMargII")=RESULTLOOKUP([d/Account]="ContribMargI")-RESULTLOOKUP([d/Account]="CostsFix")
DATA([d/Account]="ContribMargIII")=RESULTLOOKUP([d/Account]="ContribMargII")-RESULTLOOKUP([d/Account]="MatOverheadOver"

These calculations are performed using the DATA function, which sets the values for specific Accounts (ContribMargI, ContribMargII, and ContribMargIII) based on the results of the RESULTLOOKUP functions.

Decoupling the data entry from the calculations allows users to complete the data entry before calculating the contribution margin values, allowing for better performance as the two processes are not happening simultaneously.