Introducing the Bi-Directional Integration of SAP Data Warehouse Cloud and SAP Analytics Cloud for Planning

[Max_Gander]

Introduction

SAP Data Warehouse Cloud and SAP Analytics Cloud are better together, and we are proud to announce another milestone in their integration journey.

First, SAP Data Warehouse Cloud and SAP Analytics Cloud are already today the best suited couple for Analytical use-cases. This is mostly due to the well-known live-connection and the re-use of model semantics (such as currency conversion, multi-language support, etc.).

Second, we are about to establish a strong Planning integration between the two solutions. Therefore, we recently delivered a first important milestone in this direction: the bi-directional integration between SAP Data Warehouse Cloud and SAP Analytics Cloud.

Scenarios

Our new release enables you to load fact data and master data from Data Warehouse Cloud to SAP Analytics Cloud. Similarly, you can seamlessly retract fact data, master data, and audit data from SAP Analytics Cloud models and use it in SAP Data Warehouse Cloud.

Most importantly, this enables you to use actual data from SAP Data Warehouse Cloud in your planning tables in SAP Analytics Cloud. Additionally, you can join plan and actual data from multiple sources in common views in SAP Data Warehouse Cloud that you can then use for live reporting or any other kind of downstream processing of your plan data. You can also meet corporate requirements to store all steering-relevant data in one data warehouse as a single source of truth.

How to

Architecture

It is important to understand that the use case of bidirectional integration is enabled by two public APIs:

• SAP Data Warehouse Cloud provides a public OData API to pull data from DWC and integrate it into SAP Analytics Cloud using the OData Services connection.


• SAP Analytics Cloud provides the Data Export Service (DES) API (generally available with QRC2.2022) to pull data from SAC and make it available in Data Warehouse Cloud using remote tables or data flows.

In the following paragraphs, we will dive into the details of both APIs and how they can be used to transfer data between both solutions.

The following image already gives a good high-level overview of our scenario:

Data transfer from SAP Data Warehouse Cloud to SAP Analytics Cloud

SAP Data Warehouse Cloud Public API for Data Consumption

The SAP Data Warehouse Cloud Public API can be used to replicate fact and master data from SAP Data Warehouse Cloud into SAP Analytics Cloud for planning purposes. By using this API, you can authenticate against SAP Data Warehouse Cloud and get access to its data.

In particular, the API has the following key characteristics:

• It supports deployed Data Layer entities which are marked as “expose for consumption”. This includes views of semantic type Analytical Dataset and Dimension.


• It supports standard OData v4 query parameters ($select, $filter, $top, $skip, $orderBy, $count, etc.).


• It supports business user access and authentication via OAuth 2.0 authorization c

Moreover, we strongly recommend that the data in question is either replicated (via remote table replication or data flow) or the respective view’s data is snapshotted in SAP Data Warehouse Cloud.

More details on the API can be found here.

Technical set-up

To transfer data from SAP Data Warehouse Cloud to SAP Analytics Cloud, the following steps are required:

1. It all starts with a Data Layer artefact of your choice, and which contains data J. Make sure it is deployed and marked as "Exposed for Consumption".


2. In SAP Data Warehouse Cloud, create an OAuth Client with the following specifications:

• 
  
  
  
  • Purpose: Interactive Usage
  
  
  • Redirect URI: Copy-paste the re-direct URI which is highlighted in the OData Service connection creation dialog in SAP Analytics Cloud (hence our recommendation to start simultaneously with step 3).
  
  
  
  

3. In SAP Analytics Cloud, create an OData Service connection with the following specifications:

• 
  
  
  
  • Data Service URL: https://<your-dwc-tenant-url>.cloud.sap/api/v1/dwc/consumption/relational/<space-id>/<asset-id>/
  
  
  • Authentication Type: OAuth 2.0 Authorization Code
  
  
  • OAuth Client ID, Secret, Token URL and Authentication URL: Need to be filled with the OAuth Client information from step 2.
  
  
  
  

4. Now that the connection is successfully established, there are multiple options for acquiring the data into SAP Analytics Cloud:
   
   
   
   • Option1: Load data into an existing model
   
   
   • Option 2: Create a model from scratch via the OData Service connection
   
   
   
   

Note that the data acquisition limits of SAC apply, which are documented here. In particular, Classic Account Models support up 800k rows per OData model import, while the New Model supports up to 10m rows.

Currently, on SAP Analytics Cloud side, one OData Service connection needs to be set up for each SAP Data Warehouse Cloud view that you want to connect to.

Data transfer from SAC to DWC

SAP Analytics Cloud Data Export Service (DES)

With QRC2.2022, we generally released the new SAP Analytics Cloud Data Export Service (DES) to all SAP Analytics Cloud customers. In a nutshell, this is a generic OData-based pull API that can be triggered from other applications and platforms, including 3^rd party tools. There is no dedicated UI for this functionality as the API simply facilitates extraction of SAP Analytics Cloud planning models from an external platform.

The API is comprised of two services: the Administration service and the Provider service. The first extracts a list of models on your SAP Analytics Cloud system, and the second one retrieves information about a specific planning model. It has the following key characteristics:

• Extracts fact data, master data and audit data.


• Support of basic data extraction qualities with FULL data extract (delta capability planned for a future release) and basic filtering capabilities


• Highly performant


• Business & Technical User Access


• Available for Cloud Foundry Tenants

The new Data Export Service enables a wide range of possible scenarios. It will mostly be used to simplify the downstream processing of plan data that was generated in SAP Analytics Cloud.

Technical set-up

In SAP Analytics Cloud, you need to navigate to the App Integration Administration to set up the OAuth client as per the following video. This will give you the Token URL, OAuth Client ID and Secret that you will need later on.

In Data Warehouse Cloud, you then access the connection management for your space to create a CDI connection using the details that you obtained from the SAP Analytics Cloud App Integration Administration. Generic OData connections are an option as well, but not recommended for this use case.

 

Important to note is the URL for the Data Export Service: https://your-tenant.url/api/v1/dataexport/administration

As you can see in the video, you have the possibility to use a Data Provisioning Agent to enable the use of Remote Tables. If this is not done/available, you can use data flows to replicate the data into DWC.

You see there are different ways of consuming data. We ask you to consider these recommended options:

1) Remote Table-based snapshot replication

• 1:1 replication of SAP Analytics Cloud data with option to filter data and project columns


• Requiring a DP Agent for CDI based connectivity


• Supports delta export using real-time replication option (available since Q4 2022)

2) Data Flow

• Full Replication of source data with ability to use ETL functionalites of Data Flow


• Not requiring a DP Agent forCDI operator-based connectivity

What will be next?

An exciting journey lies ahead of us with the destination to offer live planning in SAP Analytics Cloud on top of SAP Data Warehouse Cloud. So, the future holds real-time steering, continuous automated planning on semantically enriched internal and external data, and drastically simplified modeling efforts for planning.

The bi-directional data transfer is the first step into this direction.

Furthermore, we are working on even better integration of SAP Data Warehouse Cloud and SAP Analytics Cloud for live analytics use cases.

Further information

If this blog post aroused your interest, check out this further information:

• Blog post - Using the SAC Data Export API with Data Warehouse Cloud and Data Intelligence


• Blog post – Using the Data Warehouse Cloud OData API with SAP Analytics Cloud


• Blog post - SAP Analytics Cloud Export Data API Sample Scripts


• sap.com - Export Data with the Data Export Service Open API


• sap.com - Browse Data via the SAP Data Warehouse Cloud Public API