Welcome to VIZIO’s blog series on SAP Analytics Cloud (SAC). This series will include explanations of the various functionalities, tools, features, and methodologies which are at the disposal of SAC application developers.
In this episode, we will be talking about one of the vital SAC tools for planning: Advanced Data Actions
Intro
Data action is one of the planning tools of SAP Analytics Cloud (SAC) that allows data changes to the model data. These are flexible tools that automate the execution of planning functions in a sequential, step-by-step manner. In this way, the results of the prior steps in data actions are observable in the subsequent steps before the latter are executed. You can also define the execution scope at each step of the data action to filter out regions of data not required for the current planning function.
The data actions are designed by modelers while their execution is performed by the planners. To create a data action, go to Home -> Navigation Bar -> Data Actions -> Create New Data Action (Fig 1.01)
Fig 1.01 – Creating a Data Action
There are multiple types of data action steps at the disposal of SAC users; one of them is called the Advanced Formula Action, which is the core topic of today’s blog.
Advanced Formula Action
Advanced Formula Action is a type of data action step that enables one to design formulas for transformations and calculations. These are then applicable to source data and the results are then written to specified scopes in the planning model.
The advanced formula data action stores the calculation results in the SAC database. This contrasts with the dimension formulas which perform real-time ‘on-the-fly’ calculations. However, just like with dimension formulas, planners can use parameters with advanced formula data actions as well.
Fig 1.02 – Add Advanced Formula Action Step
Recent Enhancements in Advanced Formula Data Action
There have been some additional enhancements in the advanced formula actions:
1) AND/OR support for IF conditions
This has improved readability and execution performance time since complex and complicated expressions.
2) Stop loop with keyword “BREAK”
This supports an immediate halt of the FOREACH loop with the IF condition or others if a specific condition is fulfilled. This results in improved execution performance.
3) Load pre-aggregated value as calculation scope:
For non-calculation-related dimensions, this enhancement enables the planners to minimize the data set the size of the scope of calculation.
4) Popup dialog box that shows calculation scope:
This enhancement allows the planners to estimate the scope of calculation at design time. This consequently leads to script optimization for improved execution performance.
Visual Editor vs. Script Editor
You can design advanced formulas in two ways: visual tools and writing scripts. Below is a brief comparison between the two:
| Visual Tool | Scripting |
| For business users | For professional modeling users (e.g., IT specialists) |
| Coding skills not required | Basic coding skills required |
| Advanced Formulas designed by drag-and-drop of graphical elements | Advanced Formulas designed for writing scripts |
| Possible to switch to scripts | Possible to switch to visual tools |
Case Study:
The requirement is to delete data from a measure called ‘Inflation Rate Benefits’ for the current planning year (dynamically controlled by parameters) for the defined dimensions. (controlled by variables). The figure below (Fig 1.03) illustrates how this is achieved by Visual Tool as well as Scripting Editor.
Fig 1.03 – Visual Tool vs Scripting
The figure below shows the plan-enabled table before the execution of the advanced formula and after its execution (Fig 1.4).
Fig 1.04 – Advanced Formula: Delete (Before & After)
Configuration Options:
In the advanced formula, there are additional configuration options as shown in the figure below (Fig 1.05).
Fig 1.05 – Advanced Formula: Configuration Options
The table below briefly describes each of these options:
| Configuration Option | Use |
| Fiscal Year | This controls the time hierarchy used in the advanced formula. If turned OFF -> Calendar Year hierarchy used If turned ON -> Fiscal Year used |
| Sign Flip | For Account Dimensions, if this option is turned ON, all calculations take into consideration, the sign value of each account, as per account type (LEQ, AST, INC & EXP) |
| Unbooked Data | Unbooked data means cells with no values. |
Reach out to our SAC experts for Consulting & End User Training Programs.