Multiple requests for a single Get Data and Refresh Data with Web Data Source

[kinkheong]

It seem like for a single Web Data Source query Power BI Desktop is issuing a number of requests for the URL and the number of requests for the URL for a single query increases as the records to be returned increases.

We have an inhouse REST API server that return a pipe seperated value dataset upon receiving a URL request.
For testing purposes we turned on logging in the REST API application so that it will capture the get requests being received.

Following are results of the test scenerios with different records size and setting timeout to 30 mins:

1. Web request that return 9 records.
   a. From invoking the Get Data till showing the Load dialog, 3 requests are received by the REST API server.
   b. Upon clicking on the LOAD in the dialog 2 additional requests are received by the REST API server.
   c. With each click of the Refresh 2 additional requests are received by the REST API server.
   
   
2. Web request that return 500K records
   a. From invoking the Get Data till showing the Load dialog, 11 requests are received by the REST API server.
   b. Upon clicking on the LOAD in the dialog 4 additional requests are received by the REST API server.
   c. With each click of the Refresh 4 additional equests are received by the REST API server.
   
   
3. Web request that return 1M records.
   a. The Get Data resulted in what seem like never ending loop of get requests to the URL until timeout of 30 mins.

If the above observation is correct then for 500K records which take about a mins for the server to response resulted in 20 mins for it to be loaded into Power BI Desktop.  It will be quite impossible to load even larger recordset.

Have any one experience something similar?

Thanks in advance

 Kin Kheong

[Mohanasudharsan]

We are also facing the same issue. Is anyone find out the solution for it?

[Mornagli]

Hi @kinkheong  

I have the same problem, Did you find a solution?

[MABottiglieri]

Hi I'm having the same problem, Power BI Desktop makes multiple queries to the same API.
as long the API service takes time to reply, PowerBI starts a new query to the same API.

this is the code I'm using to make the query:
Json.Document(Web.Contents("http://localhost:5001/api/crud/v2/read/bookingDesk"))

are there any configurations that I can set to fix this problem? some limit to the maximum request number?

thanks and regards,
Manuel Andr'e Bottiglieri

[deep_thinker]

Hi all

After I got in contact with Microsoft, they provided a solution for me which works!

Solution: 

Excel.Workbook(Binary.Buffer(Web.Contents("http://....

Microsoft: 

"Binary.Buffer streams out in memory the binary content passed into the function which avoids subsequent M code (depending on the functions used) to generate additional calls for the same binary content (PromoteHeaders in your Mquery)."

I hope this helps you as well. 

[Anonymous]

Same problem here. Pinging our internal REST API and Excel/PowerBI sends multiple requests. When the request is for an expensive query it effectively kills our server. We have the results set to stream so the server isn't overburdened, but when Excel/PowerBI sends a dozen requests that are abandoned, the queries are still executed and the pipes fill-up without offloading the data. The timeout option does nothing. This chews through our server's memory pretty quickly. This is a very frustarting problem. Is there any solution?

[deep_thinker]

2020 and this issue still exists! No solution? 

[dpiret]

Same here. Power BI with Excel, calling a RESTful API method that returns a JSON object that takes long (three minutes) to respond.

It looks like Power Query gets nervous and sends several times the same request, making things worse.

The M query is like this

= Json.Document(Web.Contents("http://domain.com/endpoint",[Timeout=#duration(0,2,0,0)]))

[aduval]

dpiret, did you get a resolution to this issue? I am also seeing multiple requests (three) every time I try to refresh a certain query and then it is timing out but continuing to send requests even after that. This is over taxing the server resources with the multiple calls. 

[Robinsedlaczek]

Same here. Are there any solutions to this now???

[Mogura27]

We're having this same problem, I have a single query table that is a Web Query to a 3rd party REST server with stringent traffic throttling. If PBI were only doing a single request for that one table, it would be fine. Instead, what I'm seeing is that in the dependent tables that consist only of List.Distinct(PrimaryTable[InterestingColumn]) also generates a full duplicate query of the master table. As a result, my provider gets slammed with 10 concurrent calls instead of 1 (breaking overhead throttles), and not only that, PBI keeps going back for more and more iterations of the same thing while it's building the connection models.

I ended up having to deploy a cache system that would query my provider, store the data locally, then respond to the excessive PBI calls locally through the on-premise gateway. I needed the gateway though anyways to wrap the requests since PBI does not support Oauth and my provider requires it. Sample sets returned quickly enough from the provider that they didn't stall out, full production sets is where it stalled out and I started looking into the query traffic.

[Daxston]

Hi did you ever figure this out?

I get the the same thing with an MVC API. Power BI Desktop refresh hits the Web datasource multiple times.

Thanks,

Dax

[kinkheong]

Hi Dax,

I suspect it is a bug with Power BI Web Data Source as the repeated requests is much shorter than the Command Timeout value that is specified in the Advance option.

For the time being I am testing a workaround by implementing Varnish Cache Server for the REST API application.
It does help by reducing loading to the REST API applcation and responding to subsequent Power BI requests within seconds.
Problem is Varnish Cache Server does not support SSL and I hope Power BI will have a fix for this.

Best regards
Kin Kheong

[v-haibl-msft]

@kinkheong

If you are using Import mode, you can try to use Direct Query mode to get data from larger dataset.

Best Regards,

Herbert

[rpai27]

Can you please explain what you mean by import mode? 

PowerbI hitting the API multiple times is a huge issue when you are trying to get a large payload. 

[kinkheong]

I am a beginner in Power BI and is testing a solution that will be based on pulling data from a URL.

There doesn't seem to be Data Connectivity mode for Web data source.

Thanks and regards

Kin Kheong