The evolution of data initialization in SAC Planning

[former_member4756]

Contents

• 
  
  
  
  • 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
    
    
    
    • What are Data Actions and where to find information
    
    
    • Prepare data action step by step
      
      
      
      • Creating a data action
      
      
      • Creating new parameters for context
      
      
      • Adding context and calculation to data action step
      
      
      • Adding data action to story
      
      
      
      
    
    
    
    
  
  
  • 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"

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.

weekly sales data in table for starting situation in SAC Planning

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).

copy and paste values from version actual to budget from 2022 to 2023

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.

Import excel file to model in data management

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 a new data action

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.

Create parameter for target date (e.g. 2023)

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 😎 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. 

Adding context to data action step

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").

Adding time scope selection function to source

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.

Adding source version

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).

Target state of data action step for data initialization

Adding data action to story

Back in the story the planning trigger needs to be added in order to execute the data action.

Add Data Action Trigger in SAC Story

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.

Execute data action to initialize planning periods with data

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.