Scheduled refresh Performance using Power BI and the Gateway

[Anonymous]

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

[v-lili6-msft]

hi, @Anonymous 

This is the same thing on my side, Scheduled refresh needs more time than manual refresh.

It may be that Scheduled refresh needs more steps in the process. and I have no related documents about the details of it.

If you want to learn more about this, you'd better create a support ticket in Power BI Support team to get further help.

https://powerbi.microsoft.com/en-us/support/

Best Regards,

Lin

[Anonymous]

Thanks for responding.

Unfortunately, I'm not able to create a support ticket.   I'm currently running the 60 day trial of Power BI Pro.  When I try to create one, it says I can't because I need to actually purchase Power BI Pro.  When I tried, it said the kind of account I had didn't make me eligible to purchase.

So I thought, "OK, I'll just create a brand new account and pay the 9.95 a month".  Well, when I tried to enter my personal email account, I got this message along the lines of, "this doesn't look like a work-related account".  So I couldn't even do that.  

I even tried to updade another free Power BI account I had....I followed the steps in this link, but they didn't work. 

https://docs.microsoft.com/en-us/power-bi/service-admin-purchasing-power-bi-pro#purchase-subscriptio...

Unfortunately, I don't even have the rights to update to a Power BI Pro account  from my own free account.

Rather frustrating!   So bottom line, I can't create a support ticket, and surprised it would be this difficult.

Kevin

[v-lili6-msft]

hi, @Anonymous 

You must be a member of the Global administrator or Billing administrator role in Office 365.

Then you can purchase and assign Power BI Pro licenses.

for create a support ticket, you could refer to this:

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/683031?advanced=false&collapse_discussion=true&q=Scroll%20down%20and%20click%20%22CREATE%20SUPPORT%20TICKET%22.&search_type=thread

And I'm sorry that I couldn't give you further help on this thread.

Best Regards,

Lin

[Anonymous]

Thanks for responding and for your help. 

It took a while longer, but I finally figured out how to create a new Power BI Pro account where I could actually submit a ticket.