Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Scheduled refresh Performance using Power BI and the Gateway

My first time posting a question here, so apologies if I've posted in the wrong place.  I have a Power BI report I've deployed to my Power BI Pro Trial site.  I also have a gateway that points back to my on-prem data source.  I'm getting some very strange performance results with scheduled refresh that I don't get with a manual refresh.

 

Here's what I've done. 

 

  • I built a Power BI report using the Desktop version.  The report points to a view that returns about 50,000 rows.  I've deployed the report to the cloud, using my Power BI Pro Trial version.    

 

  • I have a gateway set up between Power BI and my on-prem SQL Server data source.

 

  • When I refresh the data and refresh the Power BI report “manually” it takes under 30 seconds from begin to end. So far, so good.  (Again, the result set is about 50,000 rows, so it’s certainly not huge)

 

  • However, when I do a scheduled refresh, the behavior is very odd and here’s where things go bad.  In a nutshell, here is what’s happening:

 

    • If I schedule a refresh at 8 AM, the refresh might not start until 8:03 or 8:05 or even a minute or so later.  I realize that you can only set refreshes at the top and the bottom of the hour, and with some of the licensing plans, you only get 8 refreshes a day.  So I’m guessing the delay in starting is due to many people doing the same thing – but still wanted to ask if there’s any way to get a faster start

    • We’ve detected, through SQL Server Profiler, that the queries themselves  (initiated by Power BI in the cloud, through the gateway, back to our on-prem data source) are not starting for a good 5-10 minutes after the refresh actually starts!

    • When our queries actually run, they only take about 10-20 seconds.  So – it would appear that the querying is taking a very small amount of time.  There’s the initial delay of 3-5 minutes – and then there’s a longer period (5-10 minutes) before Power BI is initiating the first query back to our data source

  • So what I'm trying to figure out: only a very small portion of the time during the refresh is being spent on the query.    I've tried it at different points during the day (and evening) and keep getting similar results.  It can take up to 10 minutes for the query (view) itself to fire.

Thanks,

Kevin

 

4 Replies