Using the SAC Data Export API with Datasphere and ...

Ian_Henry · ‎2022 Apr 04

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 Datasphere, formerly known as Data Warehouse Cloud (DWC) and SAP Data Intelligence (DI).

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

The SAC Data Export API has been made generally available during the QRC in 2022 Q2. Initially, it was part of a controlled release, see the SAC Roadmap Explorer for the latest updates.

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

For bi-directional integration between SAC and Datasphere then you will also need to follow another blogpost - Using the Datasphere OData API with SAC.

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

SAP Analytics Cloud Configuration

Postman

Datasphere

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.

Token URL

OAuth Client ID

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 1.1: Access - Data Export Service

Create a New OAuth Client, similar to that below.

Figure 1.2: New OAuth Client

With these 3 parameters we can access the Data Export API with either Postman, Datasphere, 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 2.1: Postman Authentication

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

Figure 2.2: Postman Authentication Success

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 2.4: Postman API Call - NameSpaces

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

3. SAP Datasphere

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

Figure 3.1: Cloud Data Integration

In Datasphere the URL format would be the following.

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

Figure 3.2: CDI Connection Details

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

Figure 3.3: CDI Connection Details Continued

Be careful when copying and pasting the Client ID and Client Secret not to include any leading or trailing spaces.

Figure 3.3: Validate Connection

Figure 3.4: Data Builder - Import Tables

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. We can see the the GUID on your SAC planning model as the URL suffix when the model is open.

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 Datasphere, or for FactData we support real-time replication.

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 4.1: SAP Data Intelligence - Cloud Data Integration Connection

Switching to the Data Intelligence Modeler perspective

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 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 SAP Datasphere and SAP Data Intelligence.

tim_huse · ‎2022 Apr 04

Hi Ian, great blog!

I will definitely try that out. 🙂

paulvatter · ‎2022 Apr 04

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

Nils_Hocke · ‎2022 Apr 13

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

hannes2k · ‎2022 Apr 15

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

Matthew_Shaw · ‎2022 Apr 15

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

AWummel · ‎2022 Apr 19

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

Nils_Hocke · ‎2022 Apr 19

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

former_member571305 · ‎2022 Apr 21

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

Matthew_Shaw · ‎2022 Apr 21

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

AWummel · ‎2022 Apr 25

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

former_member571305 · ‎2022 Apr 26

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

harald_zarakowitis · ‎2022 May 10

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

Matthew_Shaw · ‎2022 May 10

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

harald_zarakowitis · ‎2022 May 10

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

Matthew_Shaw · ‎2022 May 10

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

paulvatter · ‎2022 May 18

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

Ian_Henry · ‎2022 May 20

Hi Paul,

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

Thanks, Ian.

AWummel · ‎2022 May 23

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

Matthew_Shaw · ‎2022 May 23

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

Matthew_Shaw · ‎2022 May 23

I believe this is expected behaviour.

AWummel · ‎2022 May 23

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

paulvatter · ‎2022 May 25

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

Thanks and best regards Paul

Matthew_Shaw · ‎2022 May 25

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

former_member783770 · ‎2022 Jun 28

Hello Ian,

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

Best regards,

Manuel

Matthew_Shaw · ‎2022 Jun 30

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

harald_zarakowitis · ‎2022 Aug 05

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

Sebastian_Gesiarz · ‎2022 Aug 10

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

Matthew_Shaw · ‎2022 Aug 10

Hi Sebastian

Here's the user guide https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/14cac91febef464dbb1efce20e3f1613/ca45363ac1de4d669ad4a... 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.

[update 18 January 2023: I've just published an article and sample solution to download the activities log. Please visit https://blogs.sap.com/2023/01/18/sap-analytics-cloud-activities-log-command-line-interface-cli-to-au... ]

All the best, Matthew

Sebastian_Gesiarz · ‎2022 Aug 10

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

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

Matthew_Shaw · ‎2022 Aug 11

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.

[update 18 January 2023: I've just published an article and sample solution to download the activities log. Please visit https://blogs.sap.com/2023/01/18/sap-analytics-cloud-activities-log-command-line-interface-cli-to-au... ]

All the best, Matthew

Matthew_Shaw · ‎2022 Aug 11

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

Sebastian_Gesiarz · ‎2022 Aug 12

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.

Matthew_Shaw · ‎2022 Aug 12

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.

[update 18 January 2023: I've just published an article and sample solution to download the activities log. Please visit https://blogs.sap.com/2023/01/18/sap-analytics-cloud-activities-log-command-line-interface-cli-to-au... ]

All the best, Matthew

Sebastian_Gesiarz · ‎2022 Aug 16

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

Matthew_Shaw · ‎2022 Aug 16

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

Sebastian_Gesiarz · ‎2022 Aug 18

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/0c1fb5e6ef1f46acb8377...

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.

Matthew_Shaw · ‎2022 Aug 18

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

[update 18 January 2023: I've just published an article and sample solution to download the activities log. Please visit https://blogs.sap.com/2023/01/18/sap-analytics-cloud-activities-log-command-line-interface-cli-to-au... ]

Regards Matthew

former_member857322 · ‎2023 May 31

Hi Ian,

Great blog!!

I have one question, do we have any similar way to export SAC Data to BTP HANA Cloud?

Thanks,

Chethan

christoph_baierl_audi · ‎2023 Sep 20

Hi Ian,

i setup the connection well, i wanted to import the SAP Resource Model with the User/Projekt information in to Datasphere, because we have to do several User/Project analysis regarding User-Licenses...

But i cannot find the SAP Models with the SAC API Access in Datasphere Data-Flow-Connection tab...

Are those not useable for us?

BR

Christoph

Matthew_Shaw · ‎2023 Sep 20

Hi Chirstoph

If I've understood correctly, you would like to consume the SAP Analytics Cloud Resource REST API (doc and API) and consume this directly into SAP Datasphere?

If so, then the export data API for SAP Analytics Cloud can't be used, as that is for acquired data models. It's not applicable to an API.

Not sure, if this helps? Let us know. Kind regards Matthew

christoph_baierl_audi · ‎2023 Sep 21

Hi Matthew,

thanks for your quick reply.

I want to load "my" User/Project Information from my SAC tenant into Datasphere.

Therefore i thougth to do so with the Export API.

Is there a chance to load this Live-Data directly into DS?

SAC Content Resource

Matthew_Shaw · ‎2023 Sep 29

Hello Christoph, This is a model based off a live (remote) connection. It means it is not an acquired model. Only acquired models can be exported via the Export Data API. For more details on the Export Data (on the SAP Analytics Cloud side), please see my blog https://blogs.sap.com/2022/05/04/sap-analytics-cloud-export-data-api-sample-scripts/ for an overview and other references, including official documentation etc. The blog has a number of working sample scripts.

Hope this helps clarify things, Matthew

osudfuegd · ‎2024 Nov 06

Hello ,

I am looking for the same thing but for the User Activity.

Any idea?

Thanks!!

Matthew_Shaw · ‎2024 Nov 06

Dear @osudfuegd for the user activity logs, my article for SAC should also work for Datasphere. You'll just need to use the Datasphere interface to create the OAuth client (of course) and make other fairly obvious changes. https://community.sap.com/t5/technology-blogs-by-sap/sap-analytics-cloud-activities-log-command-line... All the best, Matthew