Using the Data Warehouse Cloud OData API with SAP Analytics Cloud
An OData API has been developed to allow SAP Analytics Cloud (SAC) to acquire Data Warehouse Cloud (DWC) data. Acquisition of data within SAC is often required for SAC Planning scenarios. Consumption of this API by 3rd party tools is also planned, but not available with the initial release.
The DWC OData API was made available in Data Warehouse Cloud from wave 2022.11 onwards.
The SAP Note 3199078 details any restrictions as to the use of the API which need to be respected, SAP Analytics Cloud (SAC) was the first supported client.
For bi-directional integration betweem DWC and SAC, refer to another blog post I shared – Using the SAC Data Export API with Data Warehouse Cloud and Data Intelligence.
This blog post covers the steps required to import DWC data into SAC.
- Identify the OData Source (Data Service URL) in DWC
- Lookup your redirect URI in SAC
- Create the OAuth Client in DWC
- Create OData Connection in SAC
- Import from OData Connection
** Note currently I recommend using the Alternative URL as it is more reliable than the Public API URL **
1. Identify the OData Source (Data Service URL)
We could use the documentation to construct our OData URL, alternatively we can copy and paste that from our space assets.
First we can browse the available spaces using a URL such as
# Public API URL https://dwc-tenant.eu10.hcs.cloud.sap/api/v1/dwc/catalog/spaces # Alternative URL https://dwc-tenant.eu10.hcs.cloud.sap/dwaas-core/odata/v4/catalog/spaces
By specifying the space, we can see the assets in there.
# Public API URL https://dwc-tenant.eu10.hcs.cloud.sap/api/v1/dwc/catalog/spaces('I049374')/assets # Alternative URL https://dwc-tenant.eu10.hcs.cloud.sap/dwaas-core/odata/v4/catalog/spaces('I049374')/assets
Identify the dataset you require and then copy assetRelationalDataUrl, this is your Data Service URL in the SAC OData Connection.
Note My JSON response is being formatted by a browser extension, yours may be JSON returned in plain text.
If we click on the assetRelationalDataUrl, we receive the metadata from the OData Service.
We can see the data from the OData service by appended the dataset name a second time.
2. Lookup your redirect URI in SAC
The redirect URI comes from the SAC connection properties, as SAC establishes this connection to DWC
Create a new OData Connection in SAC and we can identify the URI
Note, my SAC tenant is in eu10 but strangely the Redirect URI is in us1.
3. Create the OAuth Client in DWC
A new OAuth Client needs to created in DWC
Navigate to System -> Administration -> App Integration
Add a New OAuth Client, paste the Redirect URI from step 1, ensuring there are no leading spaces.
Once created copy the OAuth Client ID and Secret
We also need to the copy the following from the OAuth Client URLs
4. Create OData Connection in SAC
We can now create the SAC OData Connection.
The SAC connection points to a single dataset/view from DWC,
You now have the 5 connection parameters and can copy and paste these values.
Data Service URL = assetRelationalDataUrl
OAuth Client ID
All being well we can save the connection.
5. Import from OData Connection
We can now create either a model or dataset from our OData connection.
In this blog post you can see that we can now import DWC data into SAC. This (in conjunction with the SAC OData/CDI API) provides support for bi-directional integration that helps with SAC Planning scenarios.
For further reading checkout Max’s Blog on the bi-directional SAC Planning and DWC scenarios.