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

      37 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 Adrian Wummel
      Adrian Wummel

      Hello Matthew,

       

      I have tried your script samples. The results I am having are not quite the same as the one Harald was facing.

      The samples 761 - 764 worked without issue for me. I was able to read the namespaces and the providers.

      When trying the scripts 765+ I received the same error message for each of them:

      READ Provider Metadata test: Unexpected error. Response: {"error":{"code":"71b7d5a9-2845-4382-6688-a0eb19a74c13","message":"EntitySetNames cannot be generated [1211]"}}

      Since in these scripts the endpoints of the Providers Service are called I believe the error lies within the service somewhere. I have opened a ticket and reported those findings to support. If you have any idea as for how that error is generated, I would love to hear your opinion.

       

      In any case, I will make sure to keep you updated with progress made by the support.

       

      Best Regards Adrian

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hello Adrian

      You'll get that error if you didn't provide a valid data file when you come to run the Collection, or if the data file contains an invalid entry.

      The sample data file provided for sample 766 is

      file_ProviderID
      sap.epm:BestRunJuice_SampleModel

      My user guide has a section for 'When things go wrong'.

      Hope this helps, Matthew

       

      Author's profile photo Adrian Wummel
      Adrian Wummel

      Hello Matthew,

       

      I did not realise, that there was no value provided by the variable, that saves the Provider ID. I  thought, that at least the SAC Sample Model might be provided, but that was not the case. I now got all the samples working in one way or another. I only had a few difficulties with 769, this one seemed to reject a few of my Planning Models and accept others, that was rather strange.

      Other than that I feel like I have gotten a good look into what is possible with the new Data Export API.

       

      Thanks for your patience and continued help.

      I hope I can count on your advise when more questions around this topic come up in the future.

      Best Regards Adrian

      Author's profile photo Harald Zarakowitis
      Harald Zarakowitis

      Hi Matthew,

      == Update: I have tried it on our QA tenant and there everything works fine, so I hope the support can find the issue==

      I'm currently in contact with the support, but I just want to give an update here.

      Script 761: Authentication POST is OK, but GET the NamespaceID='sac' throws "API Provider cannot be identified [1201]"

      Script 762: Works fine ==> Environment is setup correctly?

      Script 763: Here I receive 500 Internal Server error occurred.

      Script 764: Here I receive again the "API Provider cannot be identified"

      In difference to my previous message though, I can now access the API URLs in the browser (where my user is used) and I can retrieve information (eg. get a list of all Providers)

      So the tenant in general supports the API. The environment is setup correctly (because 762 works fine) so I really don't know where the problem could be.

      Either the OAuth Client is setup wrong (But there is not much to do wrong, the user has access to the "Data Export Service") or we have some issue because we use an IdP on our tenant for SSO?

      Best regards,
      Harald

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Harald

      If sample script 761 fails, which it appears it does because its complaining about the NamespaceID then there's a problem, most likely server-side.

      Same applies for the other samples too. Seems the API endpoint is accessible, but its just not returning the right result.

      Hopefully you've logged a support incident with SAP Product Support and they've looked into it by now?

      Hope SAP can resolve this for you very soon. It's a very odd error, I've not seen this before.

      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
      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Paul,

      I'm not aware of such limitations. Please open an incident and we can investigate with you.

      Thanks, Ian.

      Author's profile photo Matthew Shaw
      Matthew Shaw

      I believe this is expected behaviour.

      Author's profile photo Paul Vatter
      Paul Vatter

      hi both, thanks for your feedback. 376818 / 2022 is created, hopefully it can be fixed 🙂

      Thanks and best regards Paul

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hello Paul

      I've just added an FAQ section to my blog post and included this question in there. So head over to https://blogs.sap.com/2022/05/04/sap-analytics-cloud-export-data-api-sample-scripts/ for the answer and other FAQs I've captured.

      All the best, Matthew

      Author's profile photo Manuel Kriese
      Manuel Kriese

      Hello Ian,

      is it possible to export the Model Data to an excel file via an API?

       

      Best regards,

      Manuel

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hello Manuel

      If you wanted to use my sample scripts, then Postman has a command line interface that allows for exporting to files. You'd need to modify the Collection a little so it can write to a file, but this is certainly possible. If you wanted to do this, your next stop is the Postman website and its community that have lots of content on how to do this.

      If others think they would like the same, I might add this to my backlog!;-)

      All the best, Matthew

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Hello Matthew Shaw, Ian Henry

      thanks for the blog. The connection worked like a charm.

      Could you please tell me if it is possible to get the data not only from the client-defined models but also the data about audit/user activities as delivered by SAP Analytics Cloud REST API | api/v1/audit/activities?

      I was not able to find this information at https://api.sap.com/api/DataExport_API/resource

      If this API is focused solely on Models and Planning, do you foresee any option to enable the ~old REST API for Cloud Data Integration?

      Cheers,
      Sebastian

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Sebastian

      Here's the user guide https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/14cac91febef464dbb1efce20e3f1613/ca45363ac1de4d669ad4a18115401d5a.html?locale=en-US  for the activities log API.

      Sadly we're waiting for this to be accessible via an OAuth Client at the moment. Its in the roadmap so I hope it will come soon.

      All the best, Matthew

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Thank you, much appreciated. Could you please confirm the only option to connect to the activities log API (from DWC) is through BTP, parsing it in JS as described in https://blogs.sap.com/2022/04/12/step-by-step-sap-sap-data-warehouse-cloud-connection-to-third-part-api-with-micro-service-on-sap-btp/ ?

      Is this item in the public roadmap? I was not able to find it in https://roadmaps.sap.com/board?PRODUCT=67838200100800006884&q=api&range=2022Q2-2023Q1#Q3%202022

      Cheers,
      Sebastian

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Sebastian

      I can't find it in the roadmap either. Sorry, this is probably my fault. I think I got confused with this item 

      It means, there's no current commitment from SAP on the OAuth client for the /activities log API. The /activities API currently requires you to copy a token value from the browser, this is something that you can't do programmatically due to the logon being SAML, its just a different protocol. So you have to wait for the OAuth client, at least that's my understanding. I can't think of any other way around it. I guess a 'screen robot' could do this task, but I don't think that's an option for most.

      Please ask your SAP representative for an update on the OAuth Client feature support for the /activities API.

      All the best, Matthew

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Thank you, Matthew.
      I thought all the APIs already support OAuth 2.0. I did not notice that this one does not.
      I was able e.g. to consume api/v1/stories from ABAP.

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Sebastian

      Yes, we all need to wait for that OAuth client feature for the /activities endpoint.

      If you speak to your SAP representative then the SAP internal reference for this feature is FPA00-44490, worth mentioning that so they can track it easily.

      All the best, Matthew

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      We can request and save the token value programmatically by sending a get request to <sac_tenant>/oauth/token?grant_type=client_credentials.

      The problem is with copying the cookie as it expires. https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/14cac91febef464dbb1efce20e3f1613/0c1fb5e6ef1f46acb83771070084f124.html?locale=en-US#basic-authentication-with-browser-cookie

      So I can only get the API output for as long as the manually copy-pasted cookie (into ABAP in this case) is not expired. Matthew Shaw could you please tell me if by any chance you know how to overcome this issue?

      Meanwhile, we also asked our SAP representative for an update.

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Sebastian, your experience is as expected sadly. You need to wait for the OAuth client. Regards Matthew

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Hello Matthew Shaw ,

      I have one more question 🙂
      Could you please tell me if it would be possible to consume model t.3.sap.epm.usage:SAP__SAC_USAGE_ACTIVITIES directly through this API?

      I was not able to find it on the list of available models after making the connection. Are SAP content models not available at all?
      I think the documentation answers my question - it has to be a planning model. I wanted to double-check.

      A provider corresponds to a planning model within a particular SAP Analytics Cloud system.

                      "description": "SAC Usage Activities: Information about users' activities within SAP Analytics Cloud (from the System > Activities table).",
                      "id": "t.3.sap.epm.usage:SAP__SAC_USAGE_ACTIVITIES",
                      "isPlanning": false

      Thanks,
      Sebastian

      Author's profile photo Matthew Shaw
      Matthew Shaw

      Hi Sebastian

      The activities model is stored in a different part of the backend HANA database and so its not available through this API (hence it has its own API)

      Sample 'Export Data 765-Namespaces ID Provider' will list all the models you have in your SAP Analytics Cloud Service (that you can retrieve via the API, as they are acquired models which includes the planning ones as well as non-planning models).

      Hope this helps, Matthew