Technical Articles
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
HI Olaf, very interesting post, but unfortunately the github url is broken.
Could you check it out?
Many Thanks
Fernando
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
Nice Blog! Thanks for sharing.
Regards,
Girdhari
Dear all,
the git-hub repository is now public - you can start implementing the MS PowerBi - SAP Data Warehouse Cloud connectivity.
Best regards, Olaf
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
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
Thank you very much.
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).
Hi Zili Zhou, The DAC applied model that I loaded via OData shows empty in power BI,so can you explain or provide the steps that you followed would be helpful.
Hi ANANDHU,
have you tried to preview the data with DAC in DWC using the same user you tried in PowerBI? Empty could be a result that your current business user does not have any authorization to view that data.
regards
Zili
Hi Zili Zhou,
Thank you for the reply. we added the same Microsoft account which has the Power BI premium access to the DWC tenant and viewed the DAC there. It was working fine. we were able to see the DAC enabled data in DWC. But when we try to load data to the Power BI (with the same account) it shows empty.
is there anything we need to configure in DWC or in Power BI side?
Hi Olaf, The Odata connection is working fine for me.I need to clarify some doubts and queries about Odata.
1.What is the maximum number of rows that can be loaded from DWC to Power BI?
2.Is the Row limit is decided by the type of license that we use?
3.Is it possible to bring associations and input parameters into Power BI using this connector?
4.The Connector that you are using is the beta version,so where can I find the upgraded version of the connector?
Thanks in Advance
Hi Anandhu, let answer the questions one-by-one:
ad 1. & 2.) I am not aware of fixed limit - to my understanding there are natural limits in terms of resource consumption and your patience to wait for a response. If you plan to export data into non-SAP product, for sure you should consider if fees apply.
ad 3.) Please check the road map explorer
ad 4.) The beta setting has been set by intention - it is a blog post and not a standard delivery. If you like to use the connector in an productive manner I would recommend to remove the flag and follow the signing process described here: https://learn.microsoft.com/en-us/power-query/handling-connector-signing
Best regards, Olaf
Hi Olaf, The Odata connection is working fine for me,I need to clarify some doubts and queries about Odata.
1.What is the maximum number of rows that can be loaded from DWC?
2.Is there any option to make Odata connection live?
3.The connector that you're using is the beta version So where can I found the Upgraded version of the connector?
Thanks in advance
Hi Anandhu,
1.), 3.) Please see my previous reply.
2.) I re-checked with Microsoft - there are now plans to enable live connection for OData.
Best regards, Olaf
Hi Olaf, appreciate your effort to help.
so the token we are using inside the connection.json file has an token lifetime right?
I have come across an issue while trying the the Odata connection.
The connection was successful. However I was trying to upload a table that has almost 10 million rows.When the upload time exceeds 1 hour (which is the token lifetime) the load is failed and is forced to start again.
So is there anyway possible to extend the token lifetime?
Thanks in Advance
Hi Anandhu,
currently the implementation uses an access/bearer token. As per definition of the OAuth User created in DWC, the lifetime of this token is set to 1 hour. Hence you expect a timeout if you try to perform a data refresh once exceeding this timeline.
The PBI custom connector offers the usage of a refresh token to fetch a new token in case of expiry. We are working on enabling this feature and post an update once we have it ready.
Best regards, Olaf
Hi Olaf,
I am seeing that to refresh data from PoweBI Service (the web) we need to use the on-premise data gateways. Is it the only way to refresh data from the Service?
Isn't there any other direct way to refresh DWC data from Power BI service, without the need to install an on-premise data gateways? just by going from cloud (MS) to cloud (SAP).
I have another question. In the news of the github project it says:
but at the bottom of the readme it says:
Current status: So far we managed to deploy the customer connector and see it recognized in the web app. Unfortunately, the authentication using the oauth-flow is failing - the browser dialog appears and it seems not to be able to fetch and store the token.
Could you please, share the status of the PowerBI Service refresh functionality??
Thanks a lot.
Regards,
Luis M Mancha
HI Luis, appreciate your feedback
Ad 1) The on-premise connector is required to register a custom connector. As of now I am not aware of any option to register a custom connector it directly in the web environment of Microsoft Power BI - although it is a cloud and not on-prem based source.
Ad 2) With version 1.40 the data refresh was enabled for a single manual refresh. With version 1.50 the scheduling is now fully supported. The readme comment has been adjusted accordingly.
Best regards, Olaf
Hi Olaf,
Thanks a lot for you response.
Do you know if there is any onebody from Microsoft working on this connector project? I seems very weird to have the need to go through the On-Premise Gateway in order to stablish a connection from PowerBI Service to SAP DWC to get the data.
Thanks
Regards,
Luis M Mancha
Hi Luis,
the requirement to use the on-prem connector holds true for all custom connectors and is not specific to the OData case. I am not aware of any investments of Microsoft to address the general topic.
Best regards, Olaf
Ok Olaf,
Thanks a lot for your responses.
Regards,
Luis M Mancha
Hi Olaf,
While trying to authenticate for Datasphere from Power BI, I'm getting an error msg "uh oh. Something went amiss"
Can you please help
Hi Gururay,
could you please create an issue in the git-hub for odata connection? Don't forget to add (as attachment) additional information or logs helping to understand the context.
Best regards, Olaf
Hi Olaf,
Many thanks for the work conducted to provide us with this respective config. We have managed to use it quite well thus far. We do run into some issues with connecting to certain views in our environment. Below is an example of the error we receive, is there perhaps a resolution for this ?
Has anyone else perhaps run into this issue ?
Any assistance / feedback in this regard will be greatly appreciated.
Regards,
Ruark
Hi Ruark,
please check the issue discussed in GitHub https://github.com/SAP-samples/data-warehouse-cloud-connectors/issues/9 it will provide you some ideas to narrow down the issue.
My first guess: PowerBi doesn't support the paging annotation of OData. This paging is automatically enabled if the result set exceed a certain size (in MB, I think 20MB - The result list is shortened a certain row count to limit the result set.) If the annotation is added it is perceived as an unexpected character.
If this doesn't help, please open an issue in github and we can follow up.
Best regards, Olaf