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

      33 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

      Author's profile photo AKASHDEEP BANERJEE
      AKASHDEEP BANERJEE

      Hello Max,

      Any update to this ? We are considering this option for receiving data to BW4HANA system and currently ALL the models are exposed to which there is a business concern.

      Also, I'd need your comments to a related issue : When the remote source is created in HANA, it shows all the models but if I try to create a Source system in BWMT based on this remote source and subsequently a BW Datasource based on this source system, then only first 200 table entries are showing up. In the filter string, I can only give table name (and not Model Name with it) to search but then underneath all the models we have a common table name called "FactData". So, if you have more than 200 models altogether then you may not get the Fact table for your required model.

      Is there a way to get rid of this error. Only workaround I found was if I create the Virtual Table in HANA itself then I can rename the FactData as per my convenience and it can then be consumed in BW through a Local Hana DB source system.

      BR

      Akashdeep

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hello Sven

      There's a whole bunch of sample scripts that support 3-legged OAuth now available via my blog.

      You'll need to wait for me to update my user guide, but this comment I've just written to my blog provides the key elements you need.

      https://blogs.sap.com/2022/05/04/sap-analytics-cloud-export-data-api-sample-scripts/comment-page-1/#comment-637041

      All the best, Matthew

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

      Thanks Matthew that you took the time to answer my question here 🙂

      Author's profile photo Josef Haid
      Josef Haid

      Hi Maximilian Paul Gander ,

      is it possible to export both ID and description of a public dimension to BW with this setup?

      In my scenario users should create members in an SAC analytic application. Afterwards I would like to export these into BW to load them into a BW characteristic.

      Regards

      Josef

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

      Hi Josef Haid,

      Yes, you can export master data including member IDs and descriptions.

      BR
      Max

      Author's profile photo Jef Baeyens
      Jef Baeyens

      When will exporting SAC currency (FX rates) table be supported?
      It doesn't seem part of any data flow of this export service.

      Typically when setting up a new Budget, a very specific / fixed currency rate is used, and we would need to interface this information.

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

      Hi Jef,

      Thanks for your input. We are actually considering this for one of the next few releases but no timeline as it is not the highest priority for the team (working on delta).

      BR
      Max

      Author's profile photo Jon Anders Lundh
      Jon Anders Lundh

      Hi Maximilian Paul Gander

      Thanks for this blog.
      We are now in the process of testing DES. We have installed the DPAgent and created the remote source on HANA (BW) - but we do not see any of the folders/models below. Are we missing a step here? Can you point us in the right direction.

      Another question is if/when DES will have a delta-export functionality ?

       

      Best regards

      Jon Anders

      Author's profile photo Miles Stevenson
      Miles Stevenson

      Did you ever figure this out? We are having the exact same issue. Connection seems to be established fine but no content underneath.

      Author's profile photo Jon Anders Lundh
      Jon Anders Lundh

      Hi

      Yes, we were, and it's a really great functionality.
      We had some network / firewall problems (TCP_MISS messages), but after reinstalling and configuring the DPagent again, we got it to work.

      I don't think we ever identified exactly why it didn't work the first time, though - so I can't really help you here I'm afraid. Ask your network team to check, if possible. Otherwise raise a ticket to SAP.

      Author's profile photo Miles Stevenson
      Miles Stevenson

      hmmm. Well I'm able to call the api's manually just fine from postman, so I don't think it is firewall, but maybe. We've tried deleting the agent and adapter and reconfiguring, but not a full re-install. I guess that may be next and then raise a ticket if necessary. Thanks so much for the quick reply.

      Author's profile photo Zili Zhou
      Zili Zhou

      Hi Max,

      I wrote how to use SAC DES delta enabled API in BW4 as below. BW pulls automatically the delta with SDI technology.

      https://blogs.sap.com/2022/11/07/realtime-delta-data-replication-from-sap-analytics-cloud-to-bw-4-hana/

       

      best regards

      Zili

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

      Excellent blog post! Thanks for sharing, Zili Zhou!

      Author's profile photo M K
      M K

      Hi Max,

      I see that Cloud Data Integration adapter is supported only for SAP Data Warehouse Cloud (DWC), SAP BW/4HANA, and SAP HANA on-premise scenarios. In my case i am trying to extract SAC planning model data to SHC(SAP HANA Cloud). I tried using Cloud Data Integration Adapter but it is not supporting realtime in my case.

      I am getting the below error when i am trying to set the ALTER_REMOTE_SUBSCCRIPTION status to Queue or Distribute.

      CloudDataIntegrationLogger.error  - 1983340133 - while trying to invoke the method org.apache.olingo.client.api.domain.ClientProperty.getValue() of a null object loaded from local variable 'previousDeltaLinkProperty'

      java.lang.NullPointerException: while trying to invoke the method

       

      Could you please suggest any specific adapter that needs to be used to extract SAC Planning model to SHC.

       

      Best Regards,

      MK

      Author's profile photo Zili Zhou
      Zili Zhou

      Hi MK,

      CDI adapter currently not support for HANA Cloud yet. For the moment, the target systems can be a BW/4HANA, DWC, or HANA on premise system (at time of publication of this article). Please check the latest PAM HANA SDI to check the latest status if more target systems are supported.

      For HANA Cloud, you can use Odata connection, doing some development to get the delta.  There are max attention team to implement such project.

      regards

      Zili

      Author's profile photo M K
      M K

      Hi Zili,

       

      Thanks for the reply. Sure will try to use Odata Connection.

       

      Best Regards,

      MK

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Zili Zhou Maximilian Paul Gander
      For a SAC remote source in Hana Cloud, would hanaodbc adapter be possible instead of the (currently still non-supported) CDI adapter?

      Author's profile photo Zili Zhou
      Zili Zhou

      Hi Jef,

      The HANA under SAC is not exposed like DWC or on premise HANA. Thus you can not use ODBC connection in HANA Cloud for this.

       

      regards

      Zili

      Author's profile photo Peter Warren
      Peter Warren

      This API is still only available to Cloud Foundry customers (not SAP NEO data centres) - correct ?

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

      Yes, correct

      Author's profile photo Biswa Panda
      Biswa Panda

      Hello Maximilian,

      Thanks for the nice blog.

      Currently our SAC is connected to S4/HANA system.

      One quick question on the export data to BW ADSO. will it only work if the S4 system have the BW4HANA component?

      If yes, do we need to set up the BW initial set up in the S4/HANA system in order to export the data to BW ADSO?

       

      Thanks,

      Biswa

       

       

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

      Hi,

       

      You do not need BW to export to S/4. You follow the steps on S/4's HANA database and ignore the paragraph about BW.

      BR
      Max

      Author's profile photo Biswa Panda
      Biswa Panda

      Hi ,

       

      Thanks for the reply.

      Could you please provide any sample abap code to write the data back to ACDOCP or any custom table in S4/HANA if any ?

       

      Thanks,

      Biswa

      Author's profile photo Lilu Zhang
      Lilu Zhang

      Hi Maximilian,

       

      Can we export both ID and attributes for a dimension from SAC Planning Model to BW4HANA ADSO via this way? And is there any limitation for private dimensions?

       

      Best Regards & Thanks,

      Lilu.

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

      Hi,

      Yes, you can export master data tables (no limitation for private dimensions). You always export via the provider (the model).

      BR
      Max

      Author's profile photo Ernaldo Cezar
      Ernaldo Cezar

      Dear Max,

      Since October we are trying to set up this connection without sucess.
      We don't see the SAC objects when opening the remote source SACDES (point 2 of the section "SAP HANA configuration"). We get some errors. Before I show you the errors, I have a question.

      We set up SAML on our SAC. Any authentication muss be made under SAML.
      Would it be a problem with OAuth2.0 and your solution described above?

      Regards,
      Ernaldo

      Author's profile photo sureshkumar sundaramurthy
      sureshkumar sundaramurthy

      Dear Maximilian Paul Gander

      Thanks a lot for this blog. I have followed the same steps to implement but I stuck at one point where we cannot see any SAC model to select while creating Data source under SDA Source (Remote Resource) in BW system. Can you please help if we missed any access privileges or steps.

      But we could see those model from remote source in HANA Verification.

      unable%20to%20see%20SAC%20Models

      Regards,

      Suresh

      unable to see SAC Models

      Author's profile photo Zili Zhou
      Zili Zhou

      Hi Suresh,

      it looks like the BW ABAP user does not have select privilege for the tables, Please check with your basis team which user owns this data source. And try to grant privileges to the BW ABAP user.

       

      regards

      Zili

      Author's profile photo sureshkumar sundaramurthy
      sureshkumar sundaramurthy

      Hi Zili,

       

      Thanks for your response.

      it is resolved now, but we are facing other problems as Delta records are not updating in Replication table after we do change in SAC so SAP suggested to upgrade the DP agent version (2.6+).

      Regards,

      Suresh