Technical Articles
Connecting SAP Data Warehouse Cloud OData API with PowerBI via a Blank Query
In a Data Warehousing scenario, integrating complex models to analytics solutions is vital to empower business planning and decision making. The SAP Data Warehouse Cloud Public OData APIs empower customers by enabling programmatical data consumption as plain relational data or even complex analytical models. Such scenario extends the existing integration capabilities with SAP Analytics Cloud and gives customers freedom to build their own solutions or use third-party BI tools such as PowerBI.
The goal of this blog post is serve as a general reference on how to make the integration between SAP Data Warehouse Cloud and PowerBI via OData APIs using a Blank Query data source, enabling the users and customers to further evolve their PowerBI Queries and Data Sources as needed. It will cover the following steps:
- SAP Data Warehouse Cloud OData API Overview
- OAuth authentication tokens process
- Create “Blank Query” data source
- Connecting to OData source (Catalog or Consumption Model)
- Browsing and Consuming entities
- Creating a simple chart dashboard from the created query
OData API Overview
The SAP Data Warehouse Cloud OData APIs can be used to replicate data via APIs following the OData version 4 standard. It is divided into two services:
- Catalog Service: Allows consumer to list and query all accessible spaces and entities, which are available to a specific user and exposed for consumption. Each entity have reference to their consumption service.
- Consumption Service: Allows the consumption of the data from specified entities in a space. The data can be consumed in 2 fashions: as plain relational tables or as analytical models, also rich metadata definitions about the entity are exposed allowing consumers to derive detailed information about each column, dimension or measure.
The main entry point for the SAP Data Warehouse Cloud OData API is:
https://your-dwc-tenant....sap/api/v1/dwc/catalog/
although this secondary entry point could be used as well:
https://your-dwc-tenant....sap/dwaas-core/odata/v4/catalog/
For more details about the SAP Data Warehouse Cloud Public OData APIs, please refer to Official SAP Help Portal.
OAuth authentication process
The first step to setup the connection to SAP Data Warehouse Cloud OData APIs is to prepare and generate all OAuth information required to create the “Blank Query” data source in Microsoft Power BI.
As Power BI does not natively support 3-lagged OAuth flow without a custom connector, the authentication steps will be performed manually outside of PowerBI.
1. Create an OAuth Client in SAP Data Warehouse Cloud
-
-
- Go to your SAP Data Warehouse Cloud Administration page
- Open “App Integration” tab
- On the OAuth Client section, you will find important URLs for generating the OAuth credentials, save it for later.Later on this blog when there is a reference to the bellow tags replace it by its correspondent:
- <dwc_authorization_url>: Authorization URL
- <dwc_token_url>: Token URL
- Go to your SAP Data Warehouse Cloud Administration page
-
- Create a New OAuth Client to be used with PowerBI
-
- Set the name of your OAuth Client
- In Purpose field set “Interactive Usage”
- In Redirect URI field we can set the PowerBI standard: https://oauth.powerbi.com/views/oauthredirect.html
-
- Once the OAuth Client is created, click on the edit icon to get your client ID and secret
2. Generate Code Grant token
- On a browser, open the URL with pattern (replace parts in red):
- <dwc_authorization_url>?response_type=code&client_id=<clientID>
https:/your-dwc-tenant.authentication....sap/oauth/authorize?response_type=code&client_id=sb-5a0f6ce9-3ac2-4425-9d27-1d78ce6be785!b13811%7Cclient!b2319
In case you have a bad request error, replace the | (pipe) in the clientID by %7C
- <dwc_authorization_url>?response_type=code&client_id=<clientID>
- As a result of your access it will redirect you to a page where you should get the code value presented in the query parameters, copy that value (eg: lxuC1347ZLcNg3gjqFHKjAc6gT4iB0hp). Replace it later on the place of <code> tag
3. Generate Access and Refresh Tokens
- Open an API Client (eg: Postman) and create the following request (replace parts in red)
- Method: POST
- URL: <token_url> – Token URL displayed in App Integration page
- Headers:
- Authorization: Basic <base64 encoded clientId:secret>
- Content-Type: application/x-www-form-urlencoded
- x-sap-sac-custom-auth: true
- Body:
- code: <code from previous step>
- grant_type: authorization_code
- response_type: token
- Make the request and get the “refresh_token“ value from the response body, this will be used later on.
- This refresh token will be valid for 30 days, allowing you to use PowerBI without re-authenticating by 30 days from the last access.
Creating PowerBI Blank Query Data Source
At this point all pre-required authentication information and values have already been generated and it is possible to start devoloping and use a “Blank Query” as form of integration. The “Blank Query” allows PowerBI users to develop a custom data source integration using PowerScript coding language, it gives flexibility to users to define their integration scenario with less limitations than other data sources.
In the context of this blog post, we will take advantage of the “Blank Query” capabilities to expose an OData feed component from PowerBI and also generate the authentication token required by each request. To increase the query flexibility we will create and use a paremeter (“url”) that would contain the URL of the SAP Data Warehouse Cloud OData API service desired to be consumed.
Follow the next steps to start creating the query:
- Create a new Power BI Query
- As Data Source chose “Blank Query”
- For setting up the data connection the PowerScript language will be used, for that, click on “Advanced Editor”
- Use the following code to create the connection with SAP Data Warehouse Cloud Catalog API (Replace values between marked with <—–>):
let DwcPubliAPIQuery = let auth_host_token_url = <token_url>, auth_refresh_token = <refresh_token>, client_id = <client_id>, secret = <secret>, headers_content = [#"Content-Type" = "application/json", #"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & secret), 0)], query_params = [ #"grant_type" = "refresh_token", #"refresh_token" = auth_refresh_token ], payload = Json.FromValue(""), options = [ Query = query_params, Headers = headers_content, Content = payload ], response = Web.Contents(auth_host_token_url, options ), jsonResponse = Json.Document(response), // we have the authentication token access_token = jsonResponse[access_token], Headers_Connectivity = [#"Authorization" = "Bearer " & access_token] in // https://docs.microsoft.com/en-us/powerquery-m/odata-feed // OData.Feed(serviceUri as text, optional headers as nullable record, optional options as any) as any OData.Feed(url, Headers_Connectivity) in DwcPubliAPIQuery
- Replace the required infortmation in th PorwerScript code above. As summary:
- <token_url>: SAP Data Warehouse Cloud token URL, found in “Create an OAuth Client in SAP Data Warehouse Cloud” step;
- <refresh_token>: OAuth Refresh Token, generated in “Generate Access and Refresh Tokens” step;
- <client_id> and <secret>: ClientId and Secret, generated as part of the OAuth client in “Create an OAuth Client in SAP Data Warehouse Cloud” step
- Click “Done” on the “Advanced Editor”
- As next step create a PowerBI Query Parameter, go to “Manage Parameters” and create a “New Parameter” and set the following values:
- Name: url
- Required: checked
- Type: Text
- Suggested Values: Any Value
- Current Value: Use the main entry point as described in “OData API Overview” section of this blog post.
To check the result and confirm that everything is working as expected, click on the PowerBI query name on left side. It should consume the SAP Data Warehouse Cloud Public OData API catalog endpoint and display the available services. The result should look like the image bellow:
At this point browsing spaces and assets from SAP Data Warehouse Cloud repository is already possible. Clicking into “Table” link of the “Data” column will make PowerBI follow the navigation the next OData request, allowing to see all avialble spaces and assets information.
Browsing and consuming entities
Although when accesing a list of assets it is possible to see relational and analytical consumption URLs for those entities, it is not quite easy to follow those URLs and check the data of each of them.
To overcome this situation its possible to take advantage of the previous developed query and convert it into a function which receives the URL of the SAP Data Warehouse Cloud OData API service that will be consumed. That way, would be possible to execute the query multiple times for different OData services.
Follow the steps bellow:
- Right Click on the created query and select “Create Function”
- Enter the name of your function
- To try it out: click on the function name on the left side and provide the OData catalog service URL, like in the parameter setup, and invoke the function
To enable the navigation between a table of assets and the assets data, we will include a custom column into the assets table, which will execute our function with assetRelationalDataUrl or assetAnalyticalDataUrl. This will enable a navigation to the URL data service by just clicking in the added custom column for each line.
Follow the steps bellow:
- Navigate to the list of assets into a space
For performance reasons it’s recommended to first filter the assets you want work with and only later apply manipulations to the OData Feed table, like adding columns, as this operation will be applied for each asset and each operation might take a certain amout of time.
- Go to “Add Column” tab
- Click on “Invoke Custom Function” to add a custom column
- New Column Name: Add the custom column name (eg: Relational Data Navigation)
- Function Name: Select the function created previously
- url (parameter): Select one of the 2 data URLs (relational or analytical) – in this case we will use the assetRelatinalDataUrl
- Wait a moment until the services of all assets are check and the metadata preloaded. If everything went successfull, you should see the new column at the end of the table, like the image bellow:
- Click on the “Table” link for the prefered asset: You will see the list of available entities inside this asset. In the example is the CollegeEnrollment entity
- Click “Table” link of the “Data” column to consume the entity data.
Creating a simple chart dashboard
After navigating to the data preview of the chosen entity it is possible to apply and save the current visualization to be consumed by the PowerBI Dashboard builder.
- Click “Close & Apply”
- After applying the available data sources will be displayed at the right left panel. In our case the CollegeEnrollment entity data is being exposed with the dafault name “Invoked Function”.
- To add a chart, click on “New Visual”
- Select the measures and dimensions, in the right panel, desired to be displayed in the chart
Summary
This blog post covered the required concepts and steps to integrate SAP Data Warehouse Cloud with PowerBI using a Blank Query data source. Including how to manually authenticate yourself using the OAuth 3-lagged process, which is the same that should be done for any other 3rd-party application. Also how to start from the OData API catalog service, listing your spaces and assets and navigating all the way to consume entities data from SAP Data Warehouse Cloud.
I’d be happy to hear your thoughts, ideas and comments and discuss further on this topic. Let me know in the comments!
To keep updated about the latest information and blog post about SAP Data Warehouse Cloud follow the topic page, checkout the Q&A section and other related blog posts.
Hello Gustavo Kath
Are there any plans to publish the SAP Data Warehouse Cloud Public OData APIs on SAP API Business Hub?; regards;Piotr
Hi Piotr,
Yes, SAP API Business Hub documentation will be available in the following days.
Best Regards
Gustavo Kath
Hi Piotr Tesny,
The SAP Data Warehouse Cloud OData APIs are available in the SAP API Business Hub. You can access via this link: https://api.sap.com/package/sapdatawarehousecloud/overview
Best Regards
Gustavo Kath
Thanks Gustavo, Indeed the API package is there. I already provisioned a 90-days free DWC instance on my SAP BTP3Tier landscape so will soon give it a go; kind regards; Piotr
Hi Gustavo Kath ,
In this blog you are showcasing the 3-legged authentication between DS and a 3rd party tool (Power BI).
A 3-legged authentication [with the authorization code] unfortunately implies an interactive login to DS via its SAMSL SSO provider at least every 30 days (=the max validity of the refresh token) for every named user. This is OK with the third party tools or apps or whenever named users isolation cannot be guaranteed. But in case of SAP landscapes and SAP LOB application the 2-legged authentication would make more sense.
Thus, I have tried to use the OAuth2SAMLBearerAssertion flow with the public DS ODATA APIs (both catalog and consumption).
I get valid access bearer tokens returned by my OAuth2SAMLBearerAssertion destinations.
Still when trying to call any DS API with the bearer token I always get 401. I have full DWC admin rights assigned to my DS user.
on a side note, same 2-legged approach works very well with SAC App integration.
Any ideas ?
PS. Eventually, the official DS documentation says the SAML Assertion flow is supported with the ODATA APIs.
Consume Data via the OData API: https://help.sap.com/docs/SAP_DATASPHERE/43509d67b8b84e66a30851e832f66911/7a453609c8694b029493e7d87e0de60a.html
Gregor
Hi Gustavo,
Does this connection support the "Direct Query" access feature in PowerBI? "Direct Query" is a PBI functionality which is comparable with our Live Connection @ SAC and which allows virtual access of data. I.e. no data replication is necessary in PBI.
Thx, Ingo
Hi Ingo,
I expect it to be a replication only scenario.
The reason is that the solution described above returns an OData-Feed object. (
)
The OData-Feed Object comes with the same functionality as the standard OData connection of PowerBI. And this standard connection is for replication only.
Best regards, Olaf
Hi Gustavo Kath,
In the Generate Code Grant token step 1, the code is not generated. Could you please suggest me if anything got changed?
I used the same link in the Redirect URI ( https://oauth.powerbi.com/views/oauthredirect.html ), Is it correct or i need to change, please suggest.
Hi Kartik,
The behaviour should still the same from the time the blog post published. I've also retested now and it looked correct, the redirect response should contain the code query parameter.
Could you elaborate a little bit more what is happening?
In case you want to discuss with more specific details feel free to open a ticket at support.sap.com and assign it to DWC-BB component.
Best Regards, Gustavo
Hi Gustavo Kath,
In the Generate Code Grant token step, The URL which i created it is not working
(https://dwctenant.sap/oauth/authorize?response_type=code&client_id=sb-71e1f442-634d-4c17-a2b2-60e8772645a8!b34806%7Cclient!) It is not working, it should redirect to the PBI code.
In the OAuth Client, i am using the (https://oauth.powerbi.com/views/oauthredirect.html) same link to redirect URI, it is correct?
Yes, those links are correct. Could you check if you are using the DWC Authorization URL that is informed in the DWC Administration page? This URL looks like you DWC standard URL but it is not exactly the same.
I will check in incident you created and we can follow up there with more detailed information.
Best Regards
Gustavo Kath
Hi Gustavo, I have created the Incident to SAP, this is the incident number 881426.
Hi Gustavo Kath,
In the Generate Access and Refresh Token ( 3rd Step), I was not able to generate the refresh token using Postman. It says 401 unauthorized error and 400 Bad syntax error . I didn't find any working solution in Google. Is there any workaround for this or any alternative solution that you would suggest?
Hi Anish,
The Postman request should look like the image bellow, and the fields can be found at:
Body:
- code: In the query section of the redirected URL on your browser
- client_id: Inside your OAuth Client created in the DWC Administration page - Note: On postman no need to replace | by %7C
Header:
- Authorization: "Basic clientId:secret" base64 encoded (available at DWC Admin page) - or use the Basic authentication type from postman
Please let me know if this helps and feel free to send more details about the errors you are facing.
Best Regards
Gustavo Kath
Hi Gustavo,
Thank you for the reply, I've tried what you asked me to but now I am getting this 401 unauthorized error. Is there an issue with the credentials I have used in postman or is the authorization blocked from my DWC side.
Can you share the screen shot of the header part too if you don't mind.
Hi Anish Arjun,
Sorry as it took so long for a reply, I did not saw your comment was received until now.
In case you still need and for keeping it explained, this is the header section
Hi Gustavo
regarding: "This refresh token will be valid for 30 days, allowing you to use PowerBI without re-authenticating by 30 days from the last access."
does this mean that if I (or any other user that added the same blank query) do not refresh data within PowerBI in 30 days, that I do need to retrieve a new refresh token via API client?
best regards
David
Hi David,
Yes, this means that you will not need to re-authenticate in a period of 30 days, after that you will need to manually run steps 2 and 3 from "OAuth authentication process" section and update the details on the PowerBI query.
Let me know if it got clarified
Best Regards
Gustavo Kath
Hi Gustavo Kath,
In the 'generate code grant token' section, I'm not receiving the code. It redirects me to a page, but it doesn't display any code.
This is the setup I used:
<tenant-link>/oauth/authorize?response_type=code&client_id=<client-id>
And this is the resulting output:
{"authorizationUrl":"https://<tenant-link>/mobileapp/oauth2/api/v1/authorize","tokenUrl": "https://<tenant-link/mobileapp/oauth2/api/v1/token","cloudFoundry":true,"tenantUrl":"https://<tenant-link>"}
Is it possible that something has changed in the latest version of Datasphere? Or maybe I'm misunderstanding something.
Thank you for your support.
Best regards,
Andoni L.
Dear Andoni Lagos,
This is happening as your using your own domain / tenant URL to access authorization features. For requesting tokens you should use the Authorization URL which is available at the App Integration page.
Those URLs should look similar to this:
Let me know if you still have any questions
Best Regards
Gustavo Kath
Le
Dear Gustavo,
Thank you for your response.
I followed your instructions and attempted to access with the authorization URL "https:/your-dwc-tenant.authentication....sap/". However, I encountered a 400 Bad Request error when trying to access this address.
Is there any additional step or consideration that I should take into account to successfully access the authorization? I would appreciate any further suggestions or guidance you can provide to resolve this issue.
Thank you in advance for your assistance.
Best regards,
Andoni Lagos
Dear Andoni,
Could you please send some more details on the URL path you are using and the query parameters provided?
I would recommend to take another look at the step 2 "Generate Code Grant token" of the post as it has an example of the how the URL is formatted and called into a browser. It should have the bellow format:
Also as described you might need to replace the "|" (pipe) symbol per its HTML encoded code "%7C" to avoid a bad request error, as some browsers don't handle that character so well.
Hope this helps, let me know if you still have any questions
Best Regards
Gustavo Kath
Dear Gustavo Kath,
I apologize for the confusion.
I followed your recommendation and successfully obtained the code. It turns out the issue was resolved by replacing the pipe symbol as you suggested. Thank you for your valuable assistance.
Best regards,
Andoni Lagos
Dear Gustavo,
I appreciate your support so far, and I would like to share my current situation with you regarding the final step of creating the navigation function. In the navigation column, I am receiving "error" values.
Currently, I am using the following parameter in the URL: https://<tenant_url>/api/v1/dwc/catalog
However, when validating the function individually using the value "https://<tenant-link>/api/v1/dwc/consumption/relational/<space>/<asset>/" taken from the assetRelationalDataUrl column, I encounter the following error:
"Unexpected error in 'Datasphere OData API' query. DataSource.Error: OData: The metadata document of the source does not seem valid. Error: The metadata document could not be read from the message content. InvalidEnumMemberPath: 'Collection(Capabilities.FilterExpressionRestrictionType)/MultiValue' is not a valid enum member path. : (76, 14)
Details: DataSourceKind=OData DataSourcePath=https://<tenant-link>/api/v1/dwc/consumption/relational/<space>/<asset>/$metadata"
Your assistance in this blog has been extremely valuable to me, and I only have this final step left to complete. I sincerely appreciate your efforts and support.
Best regards,
Andoni Lagos