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:

1. SAP Data Warehouse Cloud OData API Overview
2. OAuth authentication tokens process
3. Create “Blank Query” data source
4. Connecting to OData source (Catalog or Consumption Model)
5. Browsing and Consuming entities
6. 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

1. 1. 1. Go to your SAP Data Warehouse Cloud Administration page
         
      2. Open “App Integration” tab
      3. 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
2. Create a New OAuth Client to be used with PowerBI
   1. 1. Set the name of your OAuth Client
      2. In Purpose field set “Interactive Usage”
      3. In Redirect URI field we can set the PowerBI standard: https://oauth.powerbi.com/views/oauthredirect.html

   

3. Once the OAuth Client is created, click on the edit icon to get your client ID and secret
   
   

2. Generate Code Grant token

1. On a browser, open the URL with pattern (replace parts in red):
   1. <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

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

1. Open an API Client (eg: Postman) and create the following request (replace parts in red)
   1. Method: POST
   2. URL: <token_url> – Token URL displayed in App Integration page
   3. Headers:
      1. Authorization: Basic <base64 encoded clientId:secret>
      2. Content-Type: application/x-www-form-urlencoded
      3. x-sap-sac-custom-auth: true
   4. Body:
      1. code: <code from previous step>
      2. grant_type: authorization_code
      3. response_type: token
2. Make the request and get the “refresh_token“ value from the response body, this will be used later on.
   1. 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:

1. Create a new Power BI Query
2. As Data Source chose “Blank Query”
   
3. For setting up the data connection the PowerScript language will be used, for that, click on “Advanced Editor”
4. 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

5. Replace the required infortmation in th PorwerScript code above. As summary:
   1. <token_url>: SAP Data Warehouse Cloud token URL, found in “Create an OAuth Client in SAP Data Warehouse Cloud” step;
   2. <refresh_token>: OAuth Refresh Token, generated in “Generate Access and Refresh Tokens” step;
   3. <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
6. Click “Done” on the “Advanced Editor”
7. As next step create a PowerBI Query Parameter, go to “Manage Parameters” and create a “New Parameter” and set the following values:
   1. Name: url
   2. Required: checked
   3. Type: Text
   4. Suggested Values: Any Value
   5. 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:

1. Right Click on the created query and select “Create Function”
   
2. Enter the name of your function
3. 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:

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

   

2. Go to “Add Column” tab
3. Click on “Invoke Custom Function” to add a custom column
   1. New Column Name: Add the custom column name (eg: Relational Data Navigation)
   2. Function Name: Select the function created previously
   3. url (parameter): Select one of the 2 data URLs (relational or analytical) – in this case we will use the assetRelatinalDataUrl
      
   4. 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:
      
4. 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
   
5. 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.

1. Click “Close & Apply”
2. 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”.
   
3. To add a chart, click on “New Visual”
4. 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.