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