Skip to Content
Technical Articles
Author's profile photo Ian Henry

Using the SAC Data Export API with Data Warehouse Cloud and Data Intelligence

In this blog post I will show how we can configure the SAP Analytics Cloud (SAC) Data Export API to access SAC planning data and integrate it with Data Warehouse Cloud (DWC) and SAP Data Intelligence (DI).

The API provides OData and Cloud Data Integration (CDI) access for great integration with DWC and DI. CDI is a superset of OData, therefore when available it is better to use CDI.

At the time of writing the SAC Data Export API is in controlled release, see the SAC Roadmap Explorer for the latest updates. The Export API is planned to be made generally available during Q2 2022.

Details of the SAC Data Export API are covered in the SAC help documentation and the SAP API Business Hub.

The steps to be covered in this blog post are the technical setup of the integration.

  1. SAP Analytics Cloud Configuration
  2. Postman
  3. Data Warehouse Cloud
  4. Data Intelligence

1. SAP Analytics Cloud Configuration

To use the SAC Data Export API you require 3 parameters, I refer to these numbers further in the blog post.

  1. Token URL
  2. OAuth Client ID
  3. Secret

In SAC navigate to System -> Administration -> App Integration

Here you will see your OAuth Clients and Token URL.

Add a New OAuth Client, when the Data Export API is enabled/released you will see the Data Export Service as an Access Type as below.

Figure%201%3A%20Access%20-%20Data%20Export%20Service

Figure 1.1: Access – Data Export Service

Create a New OAuth Client, similar to that below.

Figure%201%3A

Figure 1.2: New OAuth Client

With these 3 parameters we can access the Data Export API with either Postman, Data Warehouse Cloud, SAP Data Intelligence.

2. Postman

We can test the API with postman to verify it’s working as expected.

First we need to Authenticate using the OAuth Client details created above.

Figure%202.1%3A%20Postman%20Authentication

Figure 2.1: Postman Authentication

All being well we can click “Get New Access Token”, which will let us use the API.

Figure%202.2%3A%20Postman%20Sucess

Figure 2.2: Postman Authentication Success

Figure%202.3%3A%20Use%20Token

Figure 2.3: Use Token

Click Use Token and we can use that token to call the actual API

https://my-sac-tenanant.eu10.hcs.cloud.sap/api/v1/dataexport/administration/Namespaces(NamespaceID='sac')

Figure%202.4%3A%20Postman%20API%20Call%20-%20NameSpaces

Figure 2.4: Postman API Call – NameSpaces

Above, we can see the API is working and returning metadata as requested

3. SAP Data Warehouse Cloud

Switching to DWC we can add a new connection of type Cloud Data Integration (CDI).

Figure%203.1%3A%20Cloud%20Data%20Integration

Figure 3.1: Cloud Data Integration

In Data Warehouse Cloud the URL format would be the following.

https://my-sac-tenant.eu10.sapanalytics.cloud/api/v1/dataexport/administration

Figure%203.2%3A%20CDI%20Connection%20Details

Figure 3.2: CDI Connection Details

To use remote tables with the CDI Connection a Data Provisioning Agent is required.

Figure%203.3%3A%20CDI%20Connection%20Details%20Continued

Figure 3.3: CDI Connection Details Continued

 

Figure%203.3%3A%20Validate%20Connection

Figure 3.3: Validate Connection

Figure%203.4%3A%20Data%20Builder%20-%20Import%20Tables

Figure 3.4: Data Builder – Import Tables

Figure%203.5%3A%20Import%20Tables%20-%20Select%20Connection

Figure 3.5: Import Tables – Select Connection

We can now see namespace (sac) and the planning models, some of which have a GUID others have a description.

Figure%203.6%3A%20Select%20Required%20model%20and%20Tables

Figure 3.6: Select Required Planning Model and Tables

We are now able to use the remote tables for modeling with either remote access (federation) or replicate as a snapshot into DWC. Currently we do NOT support real-time replication from this connection, this is under review and may be supported in the future.

4. SAP Data Intelligence

In SAP Data Intelligence we can create a new connection of type CLOUD_DATA_INTEGRATION. Use the three configuration parameters as below and paste in your details from SAC, obtained in step 1.

The Service Path should be

/api/v1/dataexport/administration

 

Figure%204.1%3A%20SAP%20Data%20Intelligence%20-%20Cloud%20Data%20Integration%20Connection

Figure 4.1: SAP Data Intelligence – Cloud Data Integration Connection

Switching to the Data Intelligence Modeler perspective

Figure%204.2%3A%20Modeler%20-%20Generation%202%20-%20SAP%20Application%20Consumer

Figure 4.2: Modeler – Generation 2 – SAP Application Consumer

We can then browse the SAC Planning Models, via the namespace (sac) and then the provider, some of which have a description others have a GUID.

Figure%204.3%3A%20SAP%20Application%20Consumer

Figure 4.3: SAP Application Consumer – Browse Objects

5. Conclusion

In this blog post you can see that we can access SAC Planning data using the SAP Cloud Data Integration within Data Warehouse Cloud and SAP Data Intelligence.

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Tim Huse
      Tim Huse

      Hi Ian, great blog!

      I will definitely try that out. 🙂

      Author's profile photo Paul Vatter
      Paul Vatter

      hi Ian

      we highly appreciate this feature! It also worked fine in the controlled release on our SAC/DWC-tenants, looking forward to the release on the customer tenants!

      Best regards
      Paul

      Author's profile photo Nils Hocke
      Nils Hocke

      Hi Ian,

      thanks for the very nice Blog and description.

      Until the point, you describe the SAC -> DWC Connection it worked very good for me in rebuilding it. But from the moment replicating data or accessing data from SAC within DWC or even directly through the API it always throughs me an HTTP500 error.

      Do you have any idea where this could come from?

      Thanks

      Nils

      Author's profile photo Hannes Kröner
      Hannes Kröner

      Hi Ian,

      i ran into an similar issue, when trying to fetch masterdata from a model. ( all other requests worked fine, like  Metadata for an provider )

      https://{{SACserviceFQDN}}/api/v1/dataexport/providers/sac/CAA5CI0NUENRV05L8FL4VFLCZK/MasterData

       

      BR,

      Hannes

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hello

      If you are getting http 500 status errors then it could be a bad request. I spotted a few documentation bugs. If you could wait until next week when I plan to publish a load of Postman sample scripts that demonstrate the export data API. My samples have implemented all the API endpoints so one of my samples will almost certainly solve your problem. I'll add another comment here once I've published my samples.

      [added 4th May 2022: My sample scripts now available at https://blogs.sap.com/2022/05/04/sap-analytics-cloud-export-data-api-sample-scripts/ ]

      Regards Matthew

      Author's profile photo Nils Hocke
      Nils Hocke

      We just received the Q2.2022 update on our SAC tenant and from now on it worked again without any changes to the configuration. Maybe the update helps

       

      Regards

      Nils

      Author's profile photo Adrian Wummel
      Adrian Wummel

      Hello Ian,

       

      Since today the Functionality of the Data Export API became available on our SAC Tenant, I immediatly took the time to explore its capabilities.

      Unfortunately I ran into an Error when trying to access the API via Postman.

      I am able to successfully generate an Access Token as per your descriptions above.

      But when I try to use the API, I get the Error 404 Not Found. I tried using different API Endpoints as per this resource API Endpoints and Parameters - SAP Help Portal but nothing seems to work.

       

       

      I wonder if this is due to an Error in the Configuration or in the System itself.

       

      Thank you in advance.

      Best Regards Adrian

      Author's profile photo Italo Lombardi
      Italo Lombardi

      Hi Adrian,

      please make sure that the URL is correct and includes the base path specified in the documentation.

      For example: https://your-sac-tenant-url.cloud/api/v1/dataexport/administration/Namespaces(NamespaceID='sac')

       

      To simplify the interaction with our API, we just listed it on SAP API Business Hub.

      https://api.sap.com/api/DataExport_API/overview

      Here you can find all information about endpoints and parameters.

       

      In case you are still getting an error, I recommend you create an incident and we will address it.

       

      Thanks

      Best regards,

      Italo

      Author's profile photo Adrian Wummel
      Adrian Wummel

      Hello Italo,

       

      thank you for your quick reply. As mentioned above, I did check out the reference guide for the viable API Endpoints. I double checked multiple times to make sure the URL was correct.

      In the below screenshot you can see, I also tried it with the example you proposed.

       

       

      Getting the Access Token does work without issue. So I am certain I got that part right.

      But something is still missing and I would like to know if it is part of the configuration that I can influence or if its something in the system, that does not work as intended.

       

      When you are out of ideas aswell I may open up an incident about this.

      Regardless, thanks for your help so far.

       

      Best Regards Adrian

      Author's profile photo Italo Lombardi
      Italo Lombardi

      Hi Adrian,

      please make sure that your SAC tenant is at least on version 2022.08.

      If that is the case, I suggest you create an incident using component LOD-ANA-PL.

      We will work on it and propose a solution in the ticket itself.

       

      Thanks in advance.

      Best regards,

      Italo

      Author's profile photo Harald Zarakowitis
      Harald Zarakowitis

      Hi Adrian,

      were you able to get this resolved by SAP support? I'm facing an error where the API returns:

      https://<my-tenant-name>.eu10.sapanalytics.cloud/api/v1/dataexport/administration/Namespaces

      {
        "status": 500,
        "message": "A server error has occurred"
      }

      https://<my-tenant-name>.eu10.sapanalytics.cloud/api/v1/dataexport/administration/Namespaces(NamespaceID='sac')

      {"error":{"code":"3b353450-e51c-4a18-7431-f4d1515d638f","message":"API Provider cannot be identified [1201]"}}

      I tried in the browser, in Postman and in the SAP Business Hub. Getting the Auth token works for me as well. I will raise a ticket with SAP anyway, but I just wondered if they could help you.

      Kind regards,

      Harald

       

       

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hello Harald

      Could you please try with my sample scripts https://blogs.sap.com/2022/05/04/sap-analytics-cloud-export-data-api-sample-scripts/

      Firstly use my sample script 761 to test the setup. If that's ok, then sample script 763 (which is the API call you're trying to make, will work).

      Your SAP Analytics Cloud service must be on the Q2 2022 QRC release for this service to be available which is scheduled for later this month (unless SAP made it available to you earlier)  https://launchpad.support.sap.com/#/notes/2888562 for the dates

      Regards, Matthew

      Author's profile photo Harald Zarakowitis
      Harald Zarakowitis

      Hi Matthew,
      I saw your sample scripts, but haven't tried them since I already failed at the first step anyway. I now used your 761 script to check everything and it returns the same that I have seen in my tests. Access token is retrieved correctly, but accessing the dataexport API / Namespaces returns the "500 internal server error" reply.

      I don't think we are on any special release cycle, but since we had the option to create OAuth Clients with the Data Export Service option, I thought it should be available. I think I will wait a few more weeks and try again then and will also use more of your scripts.

       

      Best regards,
      Harald

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Harald,

      Yes, seems your SAP Analytics Cloud Service is on the regular Quarterly Release Cycle and so you'll need to wait until that's available. I agree with you though, if you see the 'Data Export Service' option is available, you really would think that it is! My guess is, there's been an update to the user interface option that has been made before the backend changes are actually there.  Won't be too long now until your Service is updated.

      All the best, Matthew

      Author's profile photo Matthew Shaw
      Matthew Shaw

      I've yet to finish writing the user guide but you can access my sample scripts (collections) written in Postman here https://github.com/SAP-samples/analytics-cloud-scim-api-samples/tree/main/Export%20Data

      It demonstrates each endpoint. Stay tuned for the user guide.

       

      [added 4th May 2022]

      User Guide now available for my samples via https://blogs.sap.com/2022/05/04/sap-analytics-cloud-export-data-api-sample-scripts/

      Regards, Matthew

      Author's profile photo Paul Vatter
      Paul Vatter

      hi Ian Henry & Matthew Shaw

      some of our (public) dimensions on SAC have data access controls, which creates the Read and Write column. In addition we have hierarchies activated, which creates one column per hierarchy (where the parent node is stored).
      Our remote table in DWC unfortunately is not displaying these columns.
      Do you know if these are known limitations or should we open an incident?
      Thanks and best regards
      Paul