Technical Articles
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.
- SAP Analytics Cloud Configuration
- Postman
- Data Warehouse Cloud
- 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, 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 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 Data Warehouse Cloud
Switching to DWC we can add a new connection of type Cloud Data Integration (CDI).
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 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
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.
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 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 Data Warehouse Cloud and SAP Data Intelligence.
Hi Ian, great blog!
I will definitely try that out. 🙂
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
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
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
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
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
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
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
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
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
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
https://<my-tenant-name>.eu10.sapanalytics.cloud/api/v1/dataexport/administration/Namespaces(NamespaceID='sac')
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
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
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
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
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
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
My user guide has a section for 'When things go wrong'.
Hope this helps, Matthew
Â
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
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
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
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
hi Ian Henry & Matthew Shaw
Hi Paul,
I'm not aware of such limitations. Please open an incident and we can investigate with you.
Thanks, Ian.
I believe this is expected behaviour.
hi both, thanks for your feedback. 376818 / 2022 is created, hopefully it can be fixed 🙂
Thanks and best regards Paul
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
Hello Ian,
is it possible to export the Model Data to an excel file via an API?
Â
Best regards,
Manuel
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
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
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
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
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
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.
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
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.
Hi Sebastian, your experience is as expected sadly. You need to wait for the OAuth client. Regards Matthew
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.
Thanks,
Sebastian
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