Introduction
Update (Dec. 2023):
The OData API has recently been improved:
- Analytic Models are exposed
- Input Parameters for relational views and analytical dataset
Both features are now supported by the connector (from version 0.7).
This blog post explains how MS PowerBi can it be connected to the OData interface of SAP Datasphere.
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 Datasphere:
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-bl...
Browse for Published OData Services
This video demonstrates the import of a list of all OData servies exposed by SAP Datasphere 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 Datasphere 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