OData refresh called multiple times

[andreas_franz]

Hi,

I have a Power BI Destop application that accesses 1 Data Source which is an OData feed (from SAP). The OData feed is quite big (~650 MB) and when calling it from a web browser or SAP it takes roughly 7 minutes to fetch the data. When refreshing the data in Power BI Desktop it takes much longer (roughly 50 minutes). After clicking on Refresh in the popup it shows "Evaluating" and "Loading 4 KB..." for a very long time. I have traced the access also on the backend of the OData application in SAP and I can see that the OData feed is called multiple times with the same returned size of ~650 MB and always using the same amount of time. These multiple calls add up to the 50 minutes.

Why does Power BI desktop call the OData feed multiple times? Is there a way to reduce the number of calls e.g. to only 1 call so that a refresh takes only 7 minutes?

Thanks already for the help!

Andreas

[v-yuezhe-msft]

@andreas_franz,

How many queries does your PBIX file contain and have you applied several transformations(Merge query, append query, etc) in Query Editor?

Power BI load previews of the data returned by a query for display in the Query Editor, when your PBIX file contains multiple queries and multiple transformations, clicking Refresh in Power BI Desktop triggers a refresh of these previews, which would result in multiple call times and a long refresh time. Please disable the following option in your PBIX file, and tune the performance using Table.Buffer function.

Regards,
Lydia

[andreas_franz]

@v-yuezhe-msft

Thanks for the reply!

I just have 1 query with a source step - nothing else. It looks roughly like this (The timeout I had to increase due to the fact that it did not finish in time):

let
    Source = OData.Feed("http://myserver:8001/sap/opu/odata/My_Service/My_Data_Object,null, [Timeout=#duration(0, 0, 59, 0)])
in
    Source

I have de-activated the "Background Data" but it still calls the OData feed several times, still taking the long time to finish. I have not used the Table.Buffer as I am quite new to the topic and could not get it to work.

Your help is highly appreciated!

Thanks and best regards,

Andreas

[v-yuezhe-msft]

@andreas_franz,

Have you make any calculations in Query Editor and create other reference queries? Please review the following blog about Table.Buffer.

https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/

Regards,

Lydia

[andreas_franz]

@v-yuezhe-msft

The query I provided is the only query and it is the complete logic. There are no calculations and no other queries are referenced.

I had seen the blog in a web search also but it seems to me that Table.Buffer is only applicable when having more than 1 query which is not the case for me.

Regards,

Andreas

[acmpbi]

Hi Andreas,

Were you able to find a resolution for multiple reads of OData Feed to SAP. I am facing the exact same problem.

I have a OData Feed which roughly takes 3 minutes to fetch data in Browser but the same is taking 15 minutes when called from Power BI. 

Any help is greatly appreciated.

Thanks,

Chandra.

[andreas_franz]

Hi Chandra,

unfortunately not. In the end I used a different way of getting the data - but this only works with getting data. I directly connect to the HANA database to a SQL view that pulls the data in the desired way (logic that I previously included in the OData code is now in the SQL command). This is also way faster in general as for us we have a gateway before the SAP system that has the data. and by directly accessing the database we avoid the gateway and SAP database access delays.

Not what I was looking for in the beginning but so far it works for us.

Best regards,

Andreas

[acmpbi]

Hi Andreas,

Thanks for your Response.  I am pretty much doing the same what you did. I am using HANA Connector for now.But would like to see OData Connector working with out any performance issues.

Regards,

Chandra.

[thoward120]

Was there ever a resolution for this? I'm running into the exact same issue now.

[thoward120]

For anyone else who may be running into the same issue: the requests are not actually duplicates. If you look at the query string from the URI of each request, you will see that they are requesting different resources. In my case, one request was for Top X rows, one was requesting the model, and another was the actual GET request. 

If you're using the OData provider, it's up to you to handle these requests accordingly. 

[andreas_franz]

Hi,

I have a Power BI Destop application that accesses 1 Data Source which is an OData feed (from SAP). The OData feed is quite big (~650 MB) and when calling it from a web browser or SAP it takes roughly 7 minutes to fetch the data. When refreshing the data in Power BI Desktop it takes much longer (roughly 50 minutes). After clicking on Refresh in the popup it shows "Evaluating" and "Loading 4 KB..." for a very long time. I have traced the access also on the backend of the OData application in SAP and I can see that the OData feed is called multiple times with the same returned size of ~650 MB and always using the same amount of time. These multiple calls add up to the 50 minutes.

Why does Power BI desktop call the OData feed multiple times? Is there a way to reduce the number of calls e.g. to only 1 call so that a refresh takes only 7 minutes?

Thanks already for the help!

Andreas