Extending SAC Planning – Importing calculation results from SAP Datasphere and E2E Workflow
Learn how a SAC Planning model can be populated with data coming from custom calculations or Machine Learning.
This blog focusses on importing the calculated results from SAP Datasphere into the SAP Analytics Cloud Planning Model and orchestrating the E2E data flow process and is part of a blog series, which explains the concept of extending SAP Analytics Cloud Planning with custom calculations and Machine Learning end-to-end.
We describe this concept in a series of three blogs:
- Accessing planning data with SAP Datasphere
- Create a simple planning model in SAC
- Make the planning data available in SAP Datasphere, so that it can be used by a Machine Learning algorithm
- Creating custom calculations or ML
- Define the Machine Learning Logic
- Create a REST-API that makes the Machine Learning logic accessible for SAC Planning
- Orchestrating the end-to-end business process (This Blog)
- Import the predictions into the planning model
- Operationalize the process
The purpose of the blog is not to compare with the out-of-the-box SAC predictive planning scenarios but to highlight the extended capabilities from SAC to use custom logic or Machine Learning outside the planning process without the need of data replication.
This diagram shows the architecture and process from a high level:
Introduction for this blog
In the previous blogs it was explained, how to create a planning model in SAP Analytics Cloud, how to capture input from the planning user, how to expose this information in the backend as Remote Table in SAP Datasphere and finally how to create the custom calculation logic or Machine Learning that will be used in the SAP Analytics Cloud Story by the planning user.
In this blog we will show how we can import the calculated results from SAP Datasphere into the SAP Analytics Cloud Planning Model and how we can use all the above steps in a single multi action in SAP Analytics Cloud to orchestrate all the planning and predictive operations.
Multi actions are used to automate a Planning and Predictive Workflow and they link together multiple sequence of steps.
The same workflow and concept can also be used for more complex requirements. For example, the same logic applies when we want to assess multiple risks in SAP Analytics Cloud Planning, and we want to use Monte Carlo simulations.
Importing calculation results from SAP Datasphere
As described in the previous blog Extending SAC Planning – Creating custom calculations or ML, the forecasts of the Machine Learning model are saved in SAP Datasphere in a staging table.
In our case this is the ”CUSTOM_CALCULATIONS”.
In order to be able to see the forecasts in the table via the SAP Datasphere we need to create a view on top of the table.
In SAP Datasphere go to Data Builder -> Extend SAC Planning -> New Graphical View
Then we select Sources from the left side of the screen or panel and drag and drop the ”CUSTOM_CALCULATIONS” table in the canvas area.
Once we drag and drop the table, we are asked to provide a name. We will keep the same name ‘’CUSTOM_CALCULATIONS”.
Then we choose Import and Deploy.
Now we will create the view on top of the table. Click on top of the View 1 and add a name. In our case it will be ”CUSTOM_CALCULATIONS_VIEW”, set the Expose for Consumption option ON ,change to Run in Analytical Mode and save and deploy the view.
Once the view is deployed, we can now see the forecasts in the view we have created, for example via the SAP Datasphere data preview.
For us to import the forecasted results from SAP Datasphere to SAP Analytics Cloud, we need to follow below steps.
Step1:Data Service URL
First, we need to identify the OData Source (Data Service URL) in SAP Datasphere.We can browse the available assets using a URL such as the below
#Public API URL https://datasphere-tenant.eu10.hcs.cloud.sap/api/v1/dwc/catalog/spaces
Then we check the name of our space. In our case this is EXTEND_SAC_PLANNING.
Then we can browse the available assets in that space using a URL such as the below
#Public API URL https://datasphere-tenant.eu10.hcs.cloud.sap/api/v1/dwc/catalog/spaces('SCHEMANAME')/assets
and then we search for our view ”CUSTOM_CALCULATIONS_VIEW”
We copy and paste the URL that mentions assetRelationalDataUrl and this is our Data Service URL that we will need in the SAC OData Connection.
In our case the URL for “assetRelationalDataUrl” is the below
This is the URL we will use for the SAC OData Connection so better to save it in Microsoft OneNote.
We need a few more URLs and IDs to be able to create the SAC OData Connection.
Then, we need the redirect URI.
You can find this when you create a new SAC OData Connection.
In SAP Analytics Cloud go to Connections -> Create New -> Acquire Data -> OData Services
Select Authentication Type OAuth 2.0 Authorization Code and scroll down to see the Redirect URI
In our case is https://bocauth.us1.sapbusinessobjects.cloud:443
Step3:OAuth Client ID and Secret
Then, we need the OAuth Client ID. A new OAuth Client needs to be created in SAP Datasphere.
Go to System -> Administration -> App Integration and create a new OAuth Client
With the above Redirect URI https://bocauth.us1.sapbusinessobjects.cloud:443
And once created copy the OAuth Client ID and the Secret
Step4: Authorisation URL and Token URL
Now we need to copy the following Authorisation URL and Token URL from the OAuth Clients in SAP Datasphere.
Step5: SAC OData Connection
Finally we can create the SAC OData Connection with our SAP Datasphere view.
Please note that the SAC OData Connection connects every time to one single view to SAP Datasphere.
In SAP Analytics Cloud go to Connections -> Create New -> Acquire Data -> OData Services and select OAuth 2.0 Authorization Code and paste all the above ids and URLs that we have saved.
Now we have successfully created an SAP Analytics Cloud OData Connection and we can now import the calculated results from SAP Datasphere.
Step6: Import Job in SAC Planning Model
To import the results into SAP Analytics Cloud Planning model we need to create an import job.
Go to the planning model from SAP Analytics Cloud -> Modeler -> Recent Files -> ExtSACP01-P&L Model -> Workspace: Data Management
and we can create a new import job by selecting data source -> OData Services
and we can connect to our SAP Datasphere view with the OData connection we just created.
Next we can select our view CUSTOM_CALCULATIONS_VIEW
And next we can select the data we want to import with the filters we need.
In our case we drag and drop all fields of the view to the selected data section.
Click Create and the data transformation is created in the data management screen.
Then we need to setup the import job.
Choose Set Up Import and see the data preview.
These are the same data we viewed in SAP Datasphere. Then choose next to select the data transformation logic.
As you can see from the above screen the Target Version field is updated by default with Actual Version from the Source. We want to change that as our source data contain the Plan Version. So lets choose the X indicator in the transformation rule and replace with Plan Version from the source.
Then we choose next and map the category dimension from the source to the category in the target.
and then choose next to complete the mappings and Run Import and Finish.
Now the import job has been completed successfully and 9 rows have been imported to our planning model.
Now we have successfully loaded the calculated Machine Learning results into our planning model.
Creating the multi action
We are using multi action capability in SAP Analytics Cloud to run multiple tasks in the correct desired sequence with a click of a button by the end user. The multi action object will help us orchestrate a set of different planning, API, data management and predictive operations in SAP Analytics Cloud and can be embedded in our story to be triggered by the end user.
To work with multi actions, you’ll need a role with permissions for the multi actions object. Therefore you have to make sure your user has the permissions mentioned in SAP Help Documentation.
In our use case, we need a set of different tasks to be executed when the end user triggers the story button to forecast the planned quantity for April to December based on price input or changes.
Let’s look in more detail what these tasks are:
- Save the end user input data in SAP Analytics Cloud Planning Model
- Trigger the API that includes the calculation of the Machine Learning Model
- Import the results from the Machine Learning Model back to SAP Analytics Cloud Planning Model
Now, that we have completed all the detailed steps for the 3 tasks (all of them have been described either in this or the previous blogs of the series) we can create the Multi action object and link these tasks with the correct sequence.
Go to the multi action screen from SAP Analytics Cloud -> Multi Actions -> Create New
Then we enter an empty screen where we can define the correct sequence of our tasks.
- Create a Version Management Step and choose that from the toolbar on the top as shown below. Then choose a step Name and select our planning model and the version we want to save while the user makes changes in the story. Now our first step has been completed.
- Create an API Step and choose from the toolbar on the top as shown below.
Now our second step has been completed.
- Create a Data Import Step and choose that from the toolbar on the top as shown below Then choose a step Name and select import Model data, our planning model, and the import job we have created in earlier step in this blog.
Embed the multi action into our Planning Story
Now that we have created the required sequence of steps for our multi action, we can now embed this to our planning user input or SAC Story.
Navigate to our story and choose edit mode. From the toolbar on top, we go to
INSERT-> Planning Actions -> Multi Action Trigger
And then we can choose a label and our Multi action that we have just created.
Now we can move the multi action on top of the story and save the story and our multi action is available to be triggered from the planning layout by our business users.
In summary, this simple solution highlights the power of SAP Business Technology Platform to enhance standard capabilities of SAP Analytics Cloud Planning and provide business users the flexibility to trigger any custom calculation logic or Machine Learning that require additional information that does not necessarily need to be included in the planning business process.
In addition to the above, with the bi-directional integration between SAP Analytics Cloud and SAP Datasphere the real-time, intelligent, and continuous automated planning process can be achieved without the need of additional data movement.
Many thanks as well to Nektarios Vasileiou for his guidance and best practices for SAP Analytics Cloud Planning Scenarios.