Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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

Max_Gander
Product and Topic Expert
Product and Topic Expert
‎05-30-2022 7:46 AM

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 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_...

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

 

 
44 Comments
Labels in this area
Popular Blog Posts