Leverage the SAP Analytics Cloud Data Export Service to extract your planning data to SAP HANA, SAP Business Warehouse and SAP S/4HANA

Introduction

With QRC2.2022, we are making the new SAP Analytics Cloud Data Export Service (DES) available 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. There is no dedicated UI for this functionality as the API simply facilitates extraction of SAP Analytics Cloud Planning models from an external platform.

Data Export Service Overview

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 extract (since Q4 2022) and basic filtering capabilities
• Extracts more than 30k rows / second
• 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. A common use case will be the combination of the plan data for reporting with actual data in HANA, a BW instance or S/4HANA. In this blog post, we will see how this can be achieved for on-premise systems using a HANA database

How to

Architecture

First, let’s have a look at the overall architecture. In our examples, we export the data to a SAP HANA database. This can be a standalone SAP HANA database or the database below an SAP BW on HANA, an SAP BW/4HANA, or an SAP S/4HANA on premise. The data resides in SAP Analytics Cloud models (Classic Account Model or New Model are possible) and is accessed via DES.

We then need a DP Agent of version 2.5.3 or higher which is installed on premise and connected to our SAP HANA database.

On these SAP HANA instances we need to create a connection and virtual tables to consume the data. From there, we can then load the data into ADSOs in SAP BW or tables in SAP S/4HANA.

High-Level Architecture

The image below describes the communication paths between the involved elements.

Communication between the involved elements

SAP Analytics Cloud configuration

You first need to set up the OAuth client in SAP Analytics Cloud as per the following video. This will give you the Token URL, OAuth Client ID and Secret that you will need later on.

DP Agent

It is a prerequisite to have a Data Provisioning Agent on at least version 2.5.3 connected to your SAP HANA database.

A detailed guide to installing a DPAgent for SDI can be found here:

https://help.sap.com/doc/dd356918159e4ba1a32927b0b917b78c/2.0_SPS05/en-US/SAP_HANA_EIM_Installation_and_Configuration_Guide_en.pdf

If SDA or SDI is not in use at all yet, the required steps to use a DPAgent are:

• Install the agent on a small server
• Import the delivery unit of the agent for SAP HANA
• Start the DPSERVER service on SAP HANA
• Connect the agent to SAP HANA in the config tool of the agent

SAP HANA configuration

1. To register the adapter, currently there is no way to perform this step in the agent itself. This can be worked around with the following SQL statement on SAP HANA:
   ## replace <agent name> with your agent name

   create adapter "CloudDataIntegrationAdapter" at location agent "<agent name>";​

The user needs the system privileges “Agent Admin” and “Adapter Admin” to be able to perform this step.

If you are not aware of the agent name given to the system, you can list the agents with:
select * from agents;

You can retrieve other information on the existing agents with:
select * from M_HOST_AGENT_INFORMATION

2. Create Remote Source in SAP HANA – the input help from HANA studio needs a lot of mandatory input, therefore an SQL Statement is easier:

CREATE REMOTE SOURCE "SACDES" ADAPTER "CloudDataIntegrationAdapter" AT LOCATION AGENT "dpagent_lt5088" CONFIGURATION
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ConnectionProperties displayName="Configurations" name="configurations">
              <PropertyEntry name="host">SAC Host</PropertyEntry>
              <PropertyEntry name="port"></PropertyEntry>
              <PropertyEntry name="protocol">HTTPS</PropertyEntry>
              <PropertyEntry name="servicePath">/api/v1/dataexport/administration</PropertyEntry>
              <PropertyEntry name="auth_mech">OAuth2</PropertyEntry>
              <PropertyEntry name="oauth2_grant_type">client_credentials</PropertyEntry>
              <PropertyEntry name="oauth2_token_request_content_type">url_encoded</PropertyEntry>
<PropertyEntry name="oauth2_token_endpoint">Token Endpoint</PropertyEntry>
<PropertyEntry name="require_csrf_header">true</PropertyEntry>
</ConnectionProperties>'
WITH CREDENTIAL TYPE 'PASSWORD' USING
'<CredentialEntry name="oauth2_client_credential">
    <user>Client ID</user>
    <password>Client Secret</password>
</CredentialEntry>';

Here you see an overview about which inputs to find where in SAP Analytics Cloud:

Mapping of SAP Analytics Cloud information to the SQL statement

This creates the Remote Source:

3. It is now possible to create a virtual table to access the data from the model.

Load into SAP BW ADSOs

First execute the previously described steps on HANA.

1. Create BW source system with following setup
   1. HANA Smart Data Access
   2. Select the HANA remote source created in step 2 for HANA.
   3. Path prefix “sap”
   4. Activate Source system

2. Create application component for SAP Analytics Cloud sources or use an existing one.
3. Create DataSource
   1. Select model
   2. Select type of data to extract
   3. Activate DataSource

4. You can create an ADSO with DataSource as template
5. Create a transformation
6. Create a DTP
7. Load data by executing or scheduling the DTP

The OData interface offers full loads and delta loads.

Load into SAP S/4HANA tables

First execute the previously described steps on the SAP S/4HANA database. By that, you already have the data available in a virtual table in SAP S/4HANA.  From there, you can choose to populate the data in other table such as ACDOCP. You can do so using ABAP logic.

Conclusion

In this blog post, we learned how to leverage the SAP Analytics Cloud Data Export Service with SAP HANA; SAP BW, and SAP S/4HANA. Watch out for further posts, i.e. how to use this API in combination with SAP Data Warehouse Cloud to enable bi-directional data transfer for planning.

The API stands for openness, flexibility, and easy downstream processing of data. It will be further enhanced in the future. So stay tuned, review the additional information provided below, and share your thoughts with the community!

Further information

Further information on the Data Export Service is available:

• Blog post – Using the SAC Data Export API with Data Warehouse Cloud and Data Intelligence
• Blog post – SAP Analytics Cloud Export Data API Sample Scripts
• sap.com – Export Data with the Data Export Service Open API