SAP planning using SAP Datawarehouse Cloud and SAP Analytics Cloud
A couple of months ago, SAP introduced a scenario in which it is not only possible to use data from Data Warehouse Cloud (DWC) in SAP Analytics Cloud (SAC) but also to retract data from SAC back into DWC. This functionality can, amongst others, be used to create planning data in SAC. Load this planning data into DWC and use it in combination with Actuals in all kinds of datamodels for reporting purposes.
In this blog I will share my first findings in using this functionality.
Dataflow
So let’s explore a case in which a SAC Planning Model is built, where Budget Data for next year should be derived from Actuals coming from SAP ECC/S4HANA. The high level dataflow for this scenarios is as follows:
- Load data into DWC
- Integrate Transactional Data and Master Data into one reporting enabled Analytical Dataset
- Load data into a SAC Planning Model
- Create Planning Function to copy data from Actuals to Budget
- Create a SAC story which displays the Actual Data and contains the Planning Function to copy the data to Budget Version
- Create a remote table in DWC to load the data from SAC to DWC
- Re-use the budget data in an Actual/Budget Analytical Dataset.
For this blog I made use of a simplified data structure using three flatfiles. One file contains Actual totals per Profit Center, Account Number and Calendermonth (actually the first day of every month, to be able to join with a time dimension). The other two files contain Master Data on Profit Centers and Account Number.
Modelling data in DWC
First of all, three inbound tables are created which contain Profit Center Master Data, Account Number Master Data and Profit Center Actuals:
Subsequently two views of the type dimension are created and one Analytical Dataset. The Analytical Dataset contains the table with the Actuals Data and has associations to the two dimension views and to a generic date dimension view.
Analytical Dataset with Profit Center actual totals and associations to Dimension views:
When exploring the Analytical Dataset in SAC, we see that the dataset includes bookings for Version ‘Actuals’, a number of years, a Profit Center hierarchy and Account Number.
Creating a Planning Model in SAC
Now that the DWC datamodel is set up, let’s create a Planning Model in SAC, based on the DWC Analytical Dataset. In order to fill the Planning Model from the DWC dataset, the model needs to be connected to a Data Source based on a OData connection to the DWC dataset. This connection is created in the ‘Connections’ menu item in SAC. The field ‘Data Service URL’ contains the Analytical Dataset of DWC which has to be used.
After creating the Connection, the Planning Model can be created. The Planning Model will contain the Standard Dimensions for Version and Date and the Generic Dimensions for Profit Centers and Accounts. The Date dimension is changed to Granularity on Day.
After saving the Planning Model, in the pull down menu the option ‘Data Management’ can be selected. In the next screen the Model can be populated from the OData connection by selecting ‘Import Data from Datasource.
When the OData connection to the DWC model is selected, mappings of the Source fields to the Target fields can be made. Unfortunately the Description fields of Account Number and Profit Center are not provided by the OData connection. So in the mappings, these fields stay empty. As there are only a limited number of values in these Dimensions, the Descriptions will be added by hand. In the key-findings a work around for this issue will be addressed.
After uploading the data, a SAC story can be used to show the data:
Using a planning function to create budget data
In a prior blog, colleague Xavier Hacking showed a number of possibilities to generate Planning Data in SAC for example by directly writing data in a table or by using predictive modelling. As for this blog the focus is on using the generated plan data in DWC, I will use just a simple planning scenario. In this scenario the Actual Data from 2022 will be copied to a Budget Version of 2023.
First a Data Action is created to copy the data.
Then this Data Action is added to the SAC story. After running the Data Action, a new column with a Budget for the next year is created:
After publishing the data, the data is stored in the SAC Planning Model.
Loading the budget data into DWC
The next step is to load the Budget Data into DWC. Therefore we need to create a connection to SAC in DWC:
Via a Data Flow the data from the SAC Planning Model can be loaded into a local table in DWC. In the Data Flow builder the connection to SAC can be used to find the Planning Model of which the data has to be extracted. When looking at the Planning Model from a DWC perspective, you will notice that the Planning Model is composed of a table with FactData and a number of Dimension tables. As only the facts of the Budget Data are needed (remember, the Actual Data and all the Dimension data is already available in DWC), in de Data Flow the table with FactData will be selected to replicate into DWC. Also, a filter value is set in the Data Flow on Budget Version only.
As output of the Data Flow a new table is created in which the Budget Data is stored in DWC.
After running the Data Flow, the new table is visible in the Data Builder and contains the current dataset from SAC.
Using the Budget Data in reporting
The new table with Budget Data can be used like any other table in DWC. Just for the purpose of checking the content of the table, a new Analytical Dataset can be created combining the Actual and Budget Data.
This Analytical Dataset can then be consumed in a SAC Story, making it possible to report on Budget and Actuals data.
Some key findings
This integration between DWC and SAC in both directions has some interesting features for both SAC planning functions as well as DWC reporting functions.
First of all it is quite valuable to have transactional (Actuals) Data available in your planning and predictive applications as a lot of planning functions will look back at what happened to plan or what will happen. Filling a SAC planning model with data from DWC is therefore an interesting solution. Currently, the only way to import data from DWC is by using an OData connections to DWC. For every dataset to be used in a Planning model, a new OData connection needs to be defined in SAC. When creating an extensive planning application in SAC, this can result in quite a number of connections to DWC. Also the use of the OData connection came with some drawbacks as the columns (e.g. the Descriptions) of the Characteristics of the associated Dimensions of the Analytical Dataset were not visible and available in the Planning Model. A work-around for this issue, is to join the Dimension views with a left outer join in the Analytical Dataset in stead of using an association:
A second big benefit of the integration between SAC and DWC is that Plan/Budget/Forecast/Predictive data can be retracted from SAC into DWC. In DWC it can follow all datawarehousing principles like combining it with Actuals Data, restricting access to subsets of the data by applying Data Access Controls, and so forth.
It would interesting if the Planning Model could be based on a live-connection model accessing the Actuals Data in DWC and thus remove the need to replicate the data into SAC. If then the generated Planning data could be written directly into DWC instead of SAC, the need for replicating data between the two systems would completely disappear. We’ll see what SAP brings us in the future…
