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