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

Note: The hyperlinks to the other blogs of this series might not yet be working for you. They will be updated as soon as those links are public / permanent.

This diagram shows the architecture and process from a high level:

Picture1: High Level Architecture and Process

The whole concept and blog series has been put together by Maria TZATSOU,  Andreas Forster, Gonzalo Hernan Sendra and Vlad-Andrei SLADARIU.

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

Picture2: Create 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.

Picture3: Create New Graphical View – Canvas

Once we drag and drop the table, we are asked to provide a name. We will keep the same name ‘’CUSTOM_CALCULATIONS”.

Picture4:Save Local Table

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”, change to Run in Analytical Mode and save and deploy the view.

Picture5: Save New Graphical 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.

Picture6: 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”

Picture: View of Schema Assets

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

https://datasphere-tenant.eu10.hcs.cloud.sap/api/v1/dwc/consumption/relational/SCHEMANAME/CUSTOM_CALCULATIONS_VIEW

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.

Step2:Redirect URI

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

Picture8: OData Services Connection

Select Authentication Type OAuth 2.0 Authorization Code and scroll down to see the Redirect URI

Picture9: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

Picture10:System Administration

With the above Redirect URI https://bocauth.us1.sapbusinessobjects.cloud:443

Picture11: New OAuth Client

And once created copy the OAuth Client ID and the Secret

Picture12:OAuth Client ID and 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.

Picture13: OAuth Clients 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.

Picture14: OData Connection in SAC (1)

and

Picture15: OData Connection in SAC (2)

Now we have successfully created an SAP Analytics Cloud OData Connection and we can now import the calculated results from SAP Datasphere.

Picture16: OData Connection in SAC (3)

Well done! 

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

Picture17: 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.

Picture18: Import Data from OData Services

Next we can select our view CUSTOM_CALCULATIONS_VIEW

Picture19: New Query for OData Services

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.

Picture20: Build OData Services Query

Click Create and the data transformation is created in the data management screen.

Then we need to setup the import job.

Picture21: Set up Import Job

Choose Set Up Import and see the data preview.

Picture22: Data Preview

These are the same data we viewed in SAP Datasphere. Then choose next to select the data transformation logic.

Picture23: Data Transformation

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.

Picture24: Data Mapping

Picture25: Data Mapping (2)

Then we choose next and map the category dimension from the source to the category in the target.

Picture26: Data Mapping (3)

and then choose next to complete the mappings and Run Import and Finish.

Picture27: Run Import

Now the import job has been completed successfully and 9 rows have been imported to our planning model.

Picture28: Import Succesfully completed

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:

1. Save the end user input data in SAP Analytics Cloud Planning Model
2. Trigger the API that includes the calculation of the Machine Learning Model
3. 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

Picture29: New Multi Action

Then we enter an empty screen where we can define the correct sequence of our tasks.

1. 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.
2. Create an API Step and choose from the toolbar on the top as shown below.Then choose a step Name, use the API Connection and API URL created in Blog Extending SAC Planning – Creating custom calculations or ML of these series.

   

   Now our second step has been completed.

3. Create a Data Import Step and choose that from the toolbar on the top as shown belowThen 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.

Picture36: Data Import Step (2)

Now our third step has been also completed and we can now save the Multi action.

Picture37: Save Multi Action

Picture38: Multi Action Steps

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

Picture39: Multi Action Trigger in a Story

And then we can choose a label and our Multi action that we have just created.

Picture40: Multi Action Setup

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.

Picture41: Multi Action in a Story

Summary

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.