Leverage the SAP Analytics Cloud Data Export Service to extract your planning data to SAP HANA, SAP Business Warehouse and SAP S/4HANA
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.
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
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.
The image below describes the communication paths 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.
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:
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
- 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
- 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:
This creates the Remote Source:
- 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.
- Create BW source system with following setup
- HANA Smart Data Access
- Select the HANA remote source created in step 2 for HANA.
- Path prefix “sap”
- Activate Source system
- Create application component for SAP Analytics Cloud sources or use an existing one.
- Create DataSource
- Select model
- Select type of data to extract
- Activate DataSource
- You can create an ADSO with DataSource as template
- Create a transformation
- Create a DTP
- 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.
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 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
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
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
Great, that covers exactly my question. Is there a how-to available for the 3-leged OAuth authentication?
I will get back to you on that.
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.
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.
All the best, Matthew
Thanks Matthew that you took the time to answer my question here 🙂
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.
Hi Josef Haid,
Yes, you can export master data including member IDs and descriptions.
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.
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).
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 ?
Did you ever figure this out? We are having the exact same issue. Connection seems to be established fine but no content underneath.
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.
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.
I wrote how to use SAC DES delta enabled API in BW4 as below. BW pulls automatically the delta with SDI technology.
Excellent blog post! Thanks for sharing, Zili Zhou!
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.
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.
Thanks for the reply. Sure will try to use Odata Connection.
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?
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.
This API is still only available to Cloud Foundry customers (not SAP NEO data centres) - correct ?
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?
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.
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 ?
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,
Yes, you can export master data tables (no limitation for private dimensions). You always export via the provider (the model).
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?
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 to see SAC Models
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.
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+).