GENERAL CONSIDERATIONS
SAP Analytics Cloud for Planning offers several options when it comes to calculations : Formulas within the Account type dimension, Advanced Formulas as part of the Data Actions, Spreading Panel inside grids and last but not least, Allocations. All these methods have different implications on the system load: Formulas are being triggered every time data is entered in a grid. Spreads are performed on demand. Ultimately, Data Actions Advanced Formulas and Allocations are triggered manually within stories.
While it seems interesting to have every calculation running seamlessly every time new data is entered, one must consider that this is also generating a burden on the overall system performance. A healthy model is the one that offers high availability and responsiveness rates, together with achieving the desired business objectives.
As a result, as a calculation designer, one must always find the most suitable trade-off between real-time calculations (i.e. Formulas) and asynchronous calculations (e.g. Advanced formulas).
DO's and DONT's
- Use Formulas with care, for business requirements such as:
- Ratio calculations
- Calculations that must be performed at every level of a hierarchy
- Don't go for exclusive usage of Formulas because it looks simpler
- Instead, consider Advanced Formulas (i.e. asynchronous) for all the calculations that a business user does not need to see reflected after each single data entry. Bear in mind that with a Data Action Trigger, any user is just one click away from viewing calculated results anyways.
TIPS & TRICKS
About Member Formulas:
Some functions involve more consumption of system resources than others.
E.g. 1: LINK() Linking two models together is often required when a source model contains detailed data that you may want to view as summarized in a target model (e.g. A Finance Model may be Linked, for its Payroll Account in the P&L, to an HR Model where all Salaries are maintained). Alternatively, a Pricing model may be called by other models to perform Price*Volume calculations.
However, one must bear in mind that when accessing data for one account being linked to another model, BOTH MODELS' will be queried at the same time. The amount of data to be processed is therefore much bigger. As a workaround, instead of using dynamic links across models (i.e. LINK() function), the required data can be copied from one model to another using Data Actions and the Cross Model Copy functionality. This is even more true when the planning process does not required 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.
Both functions call a subset of a specific region of the data model. Therefore, they require additional processing in order to either include, exclude or aggregate data points.
Remark: Lookup versus ResultLookup
With ResultLookup, the dimensions that are defined in the ResultLookup formula need to be in the current drill state. Also, you can filter on only one value.
Because 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, the dimensions defined in the Lookup formula don't need to be in the current drill state, and you can maintain more than one member in the filter expression.
E.g. 3: Do not use ‘Calculate on details’ toggle, as this option will be deprecated
This toggle is impacting performance significantly and will be deprecated soon. Instead add the exception aggregation 'SUM' with all required dimensions as exception aggregation dimensions.
See the following article on Exception Aggregation in SAP Analytics Cloud: Applying Aggregation in SAP Analytics Cloud.
See also an example where a Price x Volume calculation is required : click here
About Advanced Formulas
This is the starting point when figuring out where to design calculations. They offer a lot of benefits such as:
- Ability to tackle large volumes
- Ability to use conditional logic based on several attributes (member ID, Member property, Value)
- Ability to decouple calculations and data entry
- Ability to perform calculations across multiple models
- Ability be executed on demand (as opposed to self-triggering after data entry)
- Ability to track the processing runtime
- Ability to use a graphical builder, a.k.a. Visual Calculations, allowing business users to design complex calculations without the need for scripting
Warning: Despite all the benefits above, one must consider that, depending on the way you design advanced formulas, the resulting runtime can significantly vary.
OPTIMIZATION TIPS FOR ADVANCED FORMULAS
Scope of calculation
This aspect alone can be considered as one of utmost impact on how to optimize performance.
Generally speaking, the ‘scope’ defines the data region where calculations take place. It is initiated by the MEMBERSET() function. Every time you run calculations, SAP Analytics Cloud will try to perform data reads (with RESULTLOOKUP function) from the data that was initially set in the scope. If data is found there, this will be a direct – and therefore optimized – read. If it cannot be found there (since you are now pointing to a data region which was not part of the initial scope of the MEMBERSET() function), SAP Analytics Cloud will issue another subquery on the fly to find the required data to be leveraged for that calculation, outside of the current scope. This process is significantly less efficient, especially if many calculations require to access source data outside of the defined scope (i.e. MEMBERSET).
Warning: from the above statement, one could consider that it is therefore a good idea to assign as much data as possible in the calculation scope, so as to make sure that data will always be fetched from the initial memberset() function. This shortcut is not a good idea either.
As a general rule of thumb, you should follow an approach where :
- most of the required data to perform calculations is in the current scope
- most of the data in the scope will actually be leveraged by the formulas in your data action (in other words, don’t add 100 rows in the calculation scope, if you will only use 2 of them. This is highly inefficient.)
==> BOTH objectives should be pursued at the same time
Number of DATA() and RESULTLOOKUP() functions
Each of the 2 functions will generate a query. Logically, the more the number of queries, the more the processing time required to execute calculations, even for the same amount of data to be processed.
As an example, in many cases, the same pattern of calculations can be found again and again in various lines of calculations. In this case, instead of pointing to individual members (and therefore creating more DATA() functions on several lines of instructions), you should try to leverage – where possible – member properties which can significantly reduce the number of DATA() instructions, while producing the exact same output (see an example further down in this document).
Using Variables and Variable Members
Leveraging Variables is often a good idea when some data element will be re-used multiple times. Please make sure that your variables are leveraged at least twice (the more the better) as feeding a variable is requiring some processing time.
Conditional statements
Every IF statement generates a subquery in order to check if a condition is true or false.
Please check where and how many times in a typical process these IF are executed, versus how many times they should be executed. As an example, if you insert an IF statement in a FOREACH statement, please make sure that this condition must be verified according to each loop. E.g. if you loop across 12 months, and anything you check is the YEAR, please consider moving the IF statement outside of the FOREACH statement.
Data Action Steps / writing highly modular scripts
Designing calculations in an elegant way, where every calculation would reside in a separate step, could make a data action more readable. This approach looks somewhat appealing. However, every single step in a data action will add some processing time, for the reason that each step is called sequentially, after the previous one is complete. This is usually a subsecond process, but would you end up with tens and tens of data action steps, you would see processing time increase, as opposed to writing all calculations in fewer steps.
Illogical/irrational variation on processing time for one given data action step
Suppose that one of your data action process, all of a sudden, becomes way more time consuming than before, even though the amount of data to be processed has not changed so much. This could happen if your model design drastically changed, with dimensions going from hundreds to thousands or millions of members, or with so many more new dimensions. In this case, go to your Advanced Formula and re-Save your steps. This will regenerate scripts in the backend, taking into consideration the latest dimensionality of your model. If you have done so, but with no success, please contact customer support.
OPTIMIZATION EXAMPLES
Eg #1 - About scoping
Define the scope of data required for subsequent calculations properly by using MEMBERSET() statements to preload data in a dedicated memory space. Alternatively, don’t load into memory a huge amount of data, that you will ultimately filter out so that only a subset is leveraged by either the DATA() or RESULTLOOKUP() functions
Example: Initializing accounts for one period with a % increase vs last year
- Option 1: INEFFICIENT
DATA([d/Date]="201901", [d/Account]="A" ) = RESULTLOOKUP ([d/Date]="201801", [d/ENTITY] = “US”) * 1.05
DATA([d/Date]="201901", [d/Account]="B" ) = RESULTLOOKUP ([d/Date]="201801", [d/ENTITY] = “US”) * 1.05 - Option 2: RECOMMENDED
MEMBERSET [d/Date] = (“201801")
MEMBERSET [d/Account]=("A", “B”)
MEMBERSET [d/ENTITY] = “US”DATA ([d/Date]="201901") = RESULTLOOKUP () * 1.05
Eg #2 - FOREACH
FOREACH (i.e. loops) statements must be used for recursive use cases only. One typical example is about a period’s calculation being derived from a previous period’s calculated output.
Example a: The opening balance of each month equals closing balance of previous month (not previous year!)
MEMBERSET [d/Date] = "201901" to "201906"
FOREACH [d/Date]
// Copy previous period’s closing value to the opening value of current period
DATA([d/FLOW] = "OPEN") =
RESULTLOOKUP([d/FLOW] = "CLOSE", [d/Date] = PREVIOUS(1))
// Calculate current period’s closing value
DATA([d/FLOW] = "F_CLOSE") =
RESULTLOOKUP([d/FLOW] = "OPEN") + RESULTLOOKUP([d/FLOW] = "INCR") +
RESULTLOOKUP([d/FLOW] = "DECR")
ENDFOR
In case where an opening balance equals last year’s closing balance, there is no need to use a FOREACH statement as all periods can be independently calculated.
Eg #3 - Data versus masterdata based calculations
if your calculation only needs to run on booked values, then use FOREACH.BOOKED statement.
Example :
MEMBERSET [d/DATE] = "202101" TO "202112"
MEMBERSET [d/ACCOUNT] = "REVENUE"
FOREACH.BOOKED [d/DATE]
DATA() = RESULTLOOKUP([d/DATE]=PREVIOUS(1)) * 1.1
ENDFOR
Eg #4 - Calling data already cached in calculation scope (i.e. resultlookup)
Using data from current calculation scope with MEMBERSET function instead of calling data in RESULTLOOKUP that resides outside of the MEMBERSET definition.
Example a: Summing up multiple periods in order to calculate a rolling 3 months Revenue
- Option 1: INEFFICIENT
MEMBERSET [d/Time] = “201812"
MEMBERSET [d/ACCOUNT] = “Revenue”
DATA([d/Account]=”Revenue3months") = RESULTLOOKUP ([d/Time]=PREVIOUS(0))+, RESULTLOOKUP ([d/Time]=PREVIOUS(1)) + RESULTLOOKUP ([d/Time]=PREVIOUS(2)) - Option 2: RECOMMENDED
MEMBERSET [d/Time] = “201810” TO “201812”
MEMBERSET [d/ACCOUNT] = “Revenue”
DATA([d/Account]=”Revenue3months”, [d/Time] = “201812”) = RESULTLOOKUP ()
Example b: Copying one account from one period to many
- Option 1: INEFFICIENT
MEMBERSET [d/Time] = “201812"
MEMBERSET [d/ACCOUNT] = “Revenue”
DATA([d/Account]=”Revenue”, [d/Time]=PREVIOUS(1)) = RESULTLOOKUP()
DATA([d/Account]=”Revenue”, [d/Time]=PREVIOUS(2)) = RESULTLOOKUP() DATA([d/Account]=”Revenue”, [d/Time]=PREVIOUS(3)) = RESULTLOOKUP() - Option 2: RECOMMENDED
MEMBERSET [d/Time] = “201809” TO “201811”
MEMBERSET [d/ACCOUNT] = “Revenue”
DATA([d/Account]=”Revenue”) = RESULTLOOKUP ([d/Time] = “201812”)
Eg #5 - Variables
Use variables when a single value or even an array of values will be used multiples times as a source for several calculations. This allows to limit the number of RESULTLOOKUP calls for the same values again and again
Example a : a single value as a variable (FLOAT @[fVariable] – 64 bit or INTEGER @[fVariable] - 32 bit)
FLOAT @SumOfValue
FOREACH PRODUCT
@SumofValue=RESULTLOOKUP([d/ACCOUNT]="Profit")
ENDFOR
DATA([d/Account]="TotalProfit")=@SumofValue
Example b: a ‘member variable’ can store an array of data (i.e. a multi-dimensional variable) - VARIABLEMEMBER #VariableMemberName OF [d/DimensionName]
MEMBERSET [d/Date] = "201801"
MEMBERSET [d/Product] = ("PRD0001","PRD0002","PRD0003")
VARIABLEMEMBER #SumOfSales OF [d/Account]
DATA([d/Account] = #sumOfSales, [d/Product]=”#”) = RESULTLOOKUP([d/Account]="SALES")
Eg #6: Avoiding verbose scripting
Condense your script (and make it run faster) by leveraging member properties or by tweaking your code instead of assigning calculations to individual members.
- Opening balance of an account equals opening balance of same account plus of an adjustment account
Option 1: INEFFICIENT
DATA([d/Account]=”Asset1”, [d/Flow]=”Opening”) =
RESULTLOOKUP([d/Flow]=”Closing”, ([d/Time]= PREYEARLAST()) + RESULTLOOKUP([d/Account]=”Asset1Adjust”, [d/Flow]=”Opening”)
DATA([d/Account]=”Asset2”, [d/Flow]=”Opening”) =
RESULTLOOKUP([d/Flow]=”Closing”, ([d/Time]= PREYEARLAST()) + RESULTLOOKUP([d/Account]=”Asset2Adjust”, [d/Flow]=”Opening”) - Option 2: RECOMMENDED
Setup member properties as follows
| Member ID | Property ‘AdjAccount’ |
| Asset1 | Asset1Adjust |
| Asset2 | Asset2Adjust |
- Then:
DATA([d/Flow]=”Opening”) =
RESULTLOOKUP([d/Flow]=”Closing”, ([d/Time]= PREYEARLAST()) + RESULTLOOKUP([d/Account]. [p/AdjAccount], [d/Flow]=”Opening”)
Eg #7: Use consistent pointers to members in RESULTLOOKUP
With multiple RESULTLOOKUP functions for one calculation (i.e. one DATA() function), try to balance dimensions being referred to in each RESULTLOOKUP function
E.g. 1: Pointing to dimension members in a balanced way
Option 1: INEFFICIENT
NB: We suppose here that ‘ShortTermAsset’ is not broken down by flow members
Data([d/Account]=”Asset_Subtotal”) = RESULTLOOKUP( [d/Account]=”ShortTermAsset”) + RESULTLOOKUP([d/Account] =”LongTermAsset”, [d/Flow]=”Closing”)
Option 2: RECOMMENDED
Data([d/Account]=”Asset_Subtotal”) = RESULTLOOKUP( [d/Account]=”ShortTermAsset”, [d/Flow]=”#”) + RESULTLOOKUP([d/Account] =”LongTermAsset”, [d/Flow]=”Closing”)
E.g. 2: Use brackets to group RESULTLOOKUP() functions with balanced dimensionality
Option 1: INEFFICIENT
Data([d/Account]=”Asset_Subtotal1”) = RESULTLOOKUP( [d/Account]=”ShortTermAsset”) + RESULTLOOKUP([d/Account] =”LongTermAsset”, [d/Flow]=”Closing”)
Option 2: RECOMMENDED
Data([d/Account]=”Asset_Subtotal2”) = RESULTLOOKUP( [d/Account]=”OtherShortTermAsset”, [d/Flow]=”#”) + ( RESULTLOOKUP([d/Account] =”LongTermAsset1”, [d/Flow]=”Closing”) + RESULTLOOKUP([d/Account] =”LongTermAsset2”, [d/Flow]=”Closing”) + RESULTLOOKUP([d/Account] =”LongTermAsset3”, [d/Flow]=”Closing”) )
Miscellaneous quick hints:
Date comparisons (Year and Period combined)
Concatenate Year and Month by using YEAR() and MONTH() functions like this: YEAR([d/Date]) * 100 + MONTH([d/Date])
This will create a result like 202012 for December 2020. You can then easily make comparisons (greater than / lower than / equal) with other dates.
Zeroing out data before further calculations
Warning : if you write the following : DATA() = 0 you are actually filling the database with stored zeros, which is inefficient and wasting valuable storage space for nothing. Instead, clearing data must be done using the DELETE() function. E.g.
DELETE([d/FLOW]="OPEN")
CONCLUSION
As always, setting up calculations is some sort of art where many routes can lead to an equivalent functional outcome, but with varying results when it comes to performance and system burden.
Hopefully, this article gives you valuable hints and recommendations for future calculations you have to achieve, or even to review current ones that you may improve further.
USEFUL LINKS
- Formulas:
https://saphanajourney.com/sap-analytics-cloud/learning-article/working-with-formulas-in-sap-analyti... - Advanced Formulas Best Practice Document: https://help.sap.com/viewer/00f68c2e08b941f081002fd3691d86a7/release/en-US/fa558b0ff273475c8f3cfa005...
- Advanced Formulas Reference Guide : https://help.sap.com/doc/af782eb599024480b8a9dd85cfa4ae61/release/en-US/Advanced%20Formulas%20Refere...
This blog is part of the Best Practice series. If you want to discover more best practices, please check out these blogs as well:
https://community.sap.com/search/?ct=blog&mt=819703369010316911100650199149950&q=%23best_practices
- SAP Managed Tags:
3 Comments
