SAP Datasphere: OData Connector for PowerBi

[olaf_fischer]

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

[fagonzalez]

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

Could you check it out?

Many Thanks

Fernando

[olaf_fischer]

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

[former_member16553]

Nice Blog! Thanks for sharing.

 

Regards,

Girdhari

[olaf_fischer]

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

[miquelfornieles]

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

[olaf_fischer]

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

[miquelfornieles]

Thank you very much.

[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).

[anandhu1234]

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

[anandhu1234]

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

 

[olaf_fischer]

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

[olaf_fischer]

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

 

[anandhu1234]

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

[olaf_fischer]

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

[anandhu12345]

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.

[zili_zhou]

Hi anandhu12345

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

[anandhu12345]

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?

[luismimancha]

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:

• data refresh after publishing to PowerBi Web is now possible

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

[olaf_fischer]

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

[luismimancha]

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

[olaf_fischer]

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

[luismimancha]

Ok Olaf,

Thanks a lot for your responses.

 

Regards,

 

Luis M Mancha

[guru263]

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

 

[ruarkp]

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

[olaf_fischer]

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

[olaf_fischer]

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

 

[Venky999]

Hi Olaf,

Thank you for the detailed blog.

We have an option in Power BI 'Odata feed'. Can't we use this to access datasphere consumption API's ? Do we need to implement only custom connector for the Datasphere to be able to connect and access datasphere API's ?

Thanks & Regards,

Venkatesh

[olaf_fischer]

Hi Venkatesh,

the standard OData Feed of PowerBi doesn't support the datasphere authentication. Hence we need the customer connector to implementation the authenticaton - and then hand back a standard OData Feed to PowerBi - but incl. the require authentication token.

Best regards, Olaf

[sonupandita007]

Hi Olaf,

Thanks for the detailed blog.

[hasba_younes]

Hello @olaf_fischer  Thank you for this update.

can you please let us know on which version of PBI Desktop you are deploying the Connector?

thank you & Best regards

Younes