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

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 4
Community Support Team
Community Support Team

Re: Scheduled refresh Performance using Power BI and the Gateway

hi, @KevinScottGoff 

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

 

 

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

Re: Scheduled refresh Performance using Power BI and the Gateway

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

 

 

 

Community Support Team
Community Support Team

Re: Scheduled refresh Performance using Power BI and the Gateway

hi, @KevinScottGoff 

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/6830...

 

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

 

Best Regards,

Lin

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

Re: Scheduled refresh Performance using Power BI and the Gateway

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.  

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)