Skip to Content
Technical Articles
Author's profile photo Olaf Fischer

SAP Data Warehouse Cloud: OData Connector for PowerBi

Introduction

This blog post explains how MS PowerBi can it be connected to the OData interface of SAP Data Warehouse Cloud.

The focus is to offer a smooth user experience and to avoid coding requirements for the user. Hence we decided to implement a custom connection for MS PowerBi that comes with a selection dialog and which wraps the whole authentication process.

The following sections present screen recordings for typical workflows like browsing for existing OData services or loading of data. It shows the user perspective where the custom connector is already configured and deployed to their local installation.

The details on how to implement and configure the customer connection can be found in this git-hub repository.

Some words to the OData implementation of SAP Data Warehouse Cloud:

It has been decided to create a single OData service for every published view. Or other way round – there is not a single service that lists all views as entities but we need to use a catalog service for that.

Typical workflow:

  • Use the catalog service to search for an OData Service.
    (Here you could filter e.g. by space or view name.)
  • Use the concrete space and view name to connect and to load data.

If you are interessted in starting from a blank query and add the source code to the scripting section directly – please check out the following tutorial https://blogs.sap.com/2022/09/23/connecting-sap-data-warehouse-cloud-odata-api-with-powerbi-via-a-blank-query-2/

Browse for Published OData Services

This video demonstrates the import of a list of all OData servies exposed by SAP Data Warehouse Cloud for your user.

As a result you get the space and view names. This list could be used as starting point for the second step – the import of data.

Consume data of an OData Service

Once you have the space and the view name available you can continue and import the data. If you know your space and view already, you can start with this step directly.

The video shows the typical steps involved like:

  • specify the space and view name
  • authenticate
  • apply filters on your result set based on a data sample
  • import the data

Security Settings in MS PowerBi

As the custom connector provided is not signed, we have to lower the security to enable it. This video demonstrates how this could be done.

 

Summary

Using this connector provides you access to SAP Data Warehouse Cloud data using PowerBI. Take this opportunity and start building your reports. Thanks Gustavo for your input and feedback.

I’d be happy to hear your thoughts, ideas and comments on this topic. Let me know in the comments!

Best regards, Olaf Fischer

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Fernando Gonzalez
      Fernando Gonzalez

      HI Olaf, very interesting post, but unfortunately the github url is broken.

      Could you check it out?

      Many Thanks

      Fernando

      Author's profile photo Olaf Fischer
      Olaf Fischer
      Blog Post Author

      Hi Gonzalez,

      the URL is correct - I have verified this. The reason is that the git-hub repo is not public yet - I will follow up and let you know once available.

      Best regards, Olaf

      Author's profile photo girdhari mondal
      girdhari mondal

      Nice Blog! Thanks for sharing.

       

      Regards,

      Girdhari

      Author's profile photo Olaf Fischer
      Olaf Fischer
      Blog Post Author

      Dear all,
      the git-hub repository is now public - you can start implementing the MS PowerBi - SAP Data Warehouse Cloud connectivity.
      Best regards, Olaf

      Author's profile photo Miquel Fornieles Moreno
      Miquel Fornieles Moreno

      Hello Olaf,

      Good Job.

      In my current project, we have connected SAP DWC and PowerBI via ODBC. Unfortunately, PowerBI can't reuse SAP DWC hierarchies, associations or parameters. Could you confirm it is possible with ODATA?

      Regards,

      Miquel

      Author's profile photo Olaf Fischer
      Olaf Fischer
      Blog Post Author

      Hi Miquel,

      as of now the OData Service of SAP DWC neither supports parameters nor hierarchies. The support for associations is in evaluation - please check the road map explorer of SAP DWC to understand the latest plans and timelines.

      Best regards, Olaf

      Author's profile photo Miquel Fornieles Moreno
      Miquel Fornieles Moreno

      Thank you very much.

      Author's profile photo Zili Zhou
      Zili Zhou

      Hi Olaf, it is great idea. With your way to connect PowerBI to DWC, business users can be passed and the data access control will work. I think it will be preferred compared to use PowerBI using ODBC connection to DWC (with a technique user).