cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andreas_franz Frequent Visitor
Frequent Visitor

OData refresh called multiple times

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

8 REPLIES 8
Highlighted
andreas_franz Frequent Visitor
Frequent Visitor

OData refresh called multiple times

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

Moderator v-yuezhe-msft
Moderator

Re: OData refresh called multiple times

@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.
1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
andreas_franz Frequent Visitor
Frequent Visitor

Re: OData refresh called multiple times

@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

Moderator v-yuezhe-msft
Moderator

Re: OData refresh called multiple times

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
andreas_franz Frequent Visitor
Frequent Visitor

Re: OData refresh called multiple times

@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 Frequent Visitor
Frequent Visitor

Re: OData refresh called multiple times

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 Frequent Visitor
Frequent Visitor

Re: OData refresh called multiple times

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 Frequent Visitor
Frequent Visitor

Re: OData refresh called multiple times

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.