Skip to Content
Technical Articles
Author's profile photo Maximilian Paul Gander

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 3rd 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%20Export%20Service%20Overview

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 capability planned for a future release) 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%20Architecture

High-Level Architecture

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

Communication%20between%20the%20involved%20elements

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

 

  1. 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%20of%20SAP%20Analytics%20Cloud%20information%20to%20the%20SQL%20statement

Mapping of SAP Analytics Cloud information to the SQL statement

This creates the Remote Source:

  1. 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
  1. Create application component for SAP Analytics Cloud sources or use an existing one.
  2. Create DataSource
    1. Select model
    2. Select type of data to extract
    3. Activate DataSource
  1. You can create an ADSO with DataSource as template
  2. Create a transformation
  3. Create a DTP
  4. Load data by executing or scheduling the DTP

The OData interface offers full loads only, the respective application will need to deal with the incoming data. SAP BW has a set of possibilities to register only changes.

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:

 

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sven Knöpfler
      Sven Knöpfler

      Dear Maximilian Paul Gander

      thanks a lot for this blog. You mentioned that it is also possible to access the data with "Business User Access" -> does that mean, that any SAC user with valid credentials could use this service too? If so, is there any further information available about that?

      Thanks and kind regards

      Sven

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Sven Knöpfler,

      You are referring to the 3-legged OAuth authentication which allows business users to export smaller subsets of data while leveraging roles and data access privileges to ensure that they can only access data they have privileges for, at a model level.

      Using this is a matter of the OAuth Client set-up: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/e4914540f5c04fd2b5b8be5675248be2.html

      BR
      Max

       

       

      Author's profile photo Sven Knöpfler
      Sven Knöpfler

      Great, that covers exactly my question. Is there a how-to available for the 3-leged OAuth authentication?

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Sven,

      I will get back to you on that.

      BR
      Max