The evolution of data initialization in SAC Planning
- Data initialization for planning periods
- First Step of Evolution: Copy & Paste within SAC Story
- Second Step of evolution: Data Initialization by Import
- Third step of evolution: Data initialization per data action
- Forth step of evolution: With predictive planning
Data initialization for planning periods
When it comes to Planning the task of data initialization for the next periods of planning is inevitable to have a base which a Planner can then adjust based on his analysis and information considering future periods. In order to have this base available in the scope of the planning periods somehow data needs to be generated based on the past periods. Let´s call things as they are: In the most cases we start in planning with a pure and simple copy&paste of values, by iteratively copying a source cell and paste it in the target cell. I think it´s obvious – this begs for automation! In realistic scenarios the number of cells to copy quickly rocket in magnitudes which will fill serious part of a work day or even week. Beyond that your time, dear reader, is to valuable to copy&paste cell by cell, value by value. And fortunately when it comes to Planning within SAP Analytics Cloud there a several ways to achieve the task of data initialization with less manual work and more automation. The possibilities are ranging from more sophisticated copy&paste over an automation through data actions and until the usage of artificial intelligence within Predictive Planning. In this blog we call it the “evolution of data initialization in SAC Planning”
In the following I will demonstrate the approaches in the 4 evolution stages.
Let´s assume to have the following table as starting situation to plan year 2023. The simple objective is to transfer the data from year 2022 of the Version Actual to the year 2023 of the Version Budget on a week level.
In the following the 4 alternatives will be described how this could be done within SAC.
First Step of Evolution: Copy & Paste within SAC Story
We are living in a world of incredible technologies but let´s face it: when it comes to transferring data from one area in a table to another one copy&paste is still a popular choice. Of course this is not our recommended way to go, but self-evidently this is also possible within tables of a SAC story. Consider our model is designed on weekly basis, meaning a pure copy&paste approach for the planning of a new year would mean 52 cells per row for each line. In our example (which is far smaller than a realistic scenario) we have 12 lines thus more than 600 copy&paste steps. Not really an option!
For copy&paste fans there is a silver lining in SAC. If you copy the aggregated (summed up) entry on the source year level (here, Actual 2022) to the target year level (here, Budget 2023) it will distribute all values on the week level and thereby takes the individual entries into account (e.g. W01 differs from W02).
This little gift reduces the copy&paste steps dramatically (from 624 to 12). But please enthusiastic copy&paste advocates, do not stop here – this is not the end of evolution!
Second Step of evolution: Data Initialization by Import
Of course, the good old excel import is also possible in SAC. This is especially helpful when the planning of future periods are done outside of SAC. In order to import data, go to your model and switch to Workspace “Data Management”. After uploading and clicking on “Set Up Import” you will be guided through the necessary steps.
Hint: Currently there are limitation if the model has a week level design since the week gets not recognized as such during the import.
Beyond you’ll need to use the transformation capabilities in SAC
In our example, you’ll have to replace “Actual” with “Budget” and “2022” with “2023”, please note that there isn’t a mapping tool on the fly during the import.
Third step of evolution: Data initialization per data action
What are Data Actions and where to find information
With the third step we are coming to data actions in SAC. Through data actions it is possible to define a sequence of operations which will be performed on the data of a model. The possibilities with data actions are wide. For an overview you can take a look at the Help Portal and to get a deep insight I highly recommend the openSAP course “Planning with SAP Analytics Cloud – Advanced Topics” in which data actions are discussed profoundly in Week 2.
As you will see with data actions it is possible to make structured changes in your model by copying data, running allocation steps, and executing scripted calculations. Within our scenario to get data from 2022 to 2023 we will create an advanced formula data action step but without any coding necessary!
Please note: We realize that your Data Action will need to be more complex than in the following scenario. But our strong advice is to avoid nested conditional statements and/or loop, because performance will certainly suffer.
Prepare data action step by step
Next to the No-Coding requirement we also raise the requirement that the data actions is dynamic in the way that it still works for planning periods to come (e.g. 2024, 2025 etc.) without any adjustment.
How could this be done? As shown in the following detailed 10 steps recipe; It´s easier than expected!
First, create a new data action within SAC and choose “Advanced Formula” for your first (and only) step in the data action. Consider that it is possible to create a great number of steps which can be executed in sequence to create complex transformations on your model. But as promised, we keep things simple here.
Creating a data action
Follow the steps shown in the screenshots to create a data action. In the same area you can also access existing data actions.
Creating new parameters for context
Within the just created data action, we now create a new parameter. We´ll reference this parameter as context to the step in order to dynamically control the target period which should be initialized.
Adding context and calculation to data action step
With help of the context area, it is possible to control the overall scope of the data action step. Since we target the specific measure “Sales”, we´ll first filter on that measure (see step 7). Additionally, we include the parameter (see step 8) we created in step 5 to connect it to the step (without that the prompt will not be triggered when executing the data action).
Hint: Considering performance it is better to filter in the context than within the calculation steps.
Now, it is all prepared to begin with the calculation. In our example we want to achieve that for the measure sales data across all existing dimensions will be copied from the source version “Actual” to target version “Budget”. With function PREVIOUS we can filter the source on the previous period (with offset = 1) and on the scope of a year (granularity = “YEAR”).
see also the documentation for the function PREVIOUS in SAP Help.
Last but not least, we filter for the version Actual for the source data.
Done! The data action should now look like this. Of course it is also possible to do this in the script editor (see on the right side).
Adding data action to story
Back in the story the planning trigger needs to be added in order to execute the data action.
After executing the data action, the desired result gets generated.
All weeks of year 2023 are now initialized for version “Budget”.
As a next step a planner could adapt the values based on his analysis.
Forth step of evolution: With predictive planning
With predictive planning we enter the most advanced way of data initialization. The main difference with that approach is that it is not a pure copy&paste (in the end the 3 evolution steps before are nothing else) but it already blends some artificial intelligence in the transferred data. Though that manual work can be reduced, and the data becomes more reliable right on from the start. I highly recommend reading the Blog Series from Antoine CHABERT where he outlines the business value of predictive planning in a comprehensible way.
So why not always going for predictive planning and forgetting about the other stages? Our experience is that at least 4-5 years of reliable high-quality data is necessary to get good results generated. Many customers do have this and are very satisfied with the results of predictive planning. If you are one of those customers, predictive planning is the way to go. If not, with the help of data action it is also possible to automate a big part and therewith helping to create exactly this precious historical data as a base for the approach with predictive planning.