Service Refresh Speed Inconsistent from Onedrive

[as19]

I have a dataset which has 6 excel files coming in from onedrive live (web).
3 files contain a years worth of data (~50mb), and a 4th appends all of these together (in power bi).
Another xlsx has 50 columns unpivotted. 

In the grand scheme there is not an incredible amount of data, however my service refresh speeds vary from 1min up to 35mins. I have no idea why.

Would the refreshes be quicker if i did the appending in excel before it came into power bi? 
Would the refreshes be quicker if i did the unpivoting in excel before it came into power bi?

Thanks.

[Burningsuit · Accepted solution]

Hi @as19 

When you schedule a refresh on a Dataset you are actually scheduling a time that your Dataset will be placed in the refresh queue. The refresh process runs in the Power BI cloud, which (if you have a Pro licence) is used by all other users with a Pro licence. So depending on how many refreshes have been scheduled at any one time, it may take a longer or shorter time to get around to your refresh. I think the maximum time a refresh can be queued for is an hour after which it will give an error. With a Pro licence you can't do anything about this, you're n with all the other Power BI users and have to wait your turn. You may be able to get it refreshed quicker by scheduling at an earlier or later time, for instance 8:00-10:00 am eastern US time seems to be quite busy as all the American datasets get queued up to be refreshed before the start of the day.

Other alternatives are to go to a PPU licence (which has more dedicated resources) or Premium Capacity (which is dedicated resource) . I doubt that changing the query will make any difference in this case.

Hope this helps

Stuart  

[as19]

Hi @Burningsuit , Thanks for the response.

I didnt think of this. Im assuming the fact that at times i can manually refresh in 2mins and other times its 30mins is therefore down to traffic on the PowerBI cloud then?

With a PPU licence, is it a different cloud meaning i wont have this issue?
I believe however that with a PPU, pro users will not be able to view and use any reports i publish. Is that correct?

Thanks

[Burningsuit]

Hi @as19 

Certainly the times take for refreshing in the Cloud (Not Power BI desktop) are dependant on the traffic in the cloud. If you use PPU there are more resources, and fewer users, so times sem to be faster. But you're right if you have content in a PPU workspace, anyone consuming that content also needs a PPU licence.

The main question really is, why does it matter if it take 2minutes or 20minutes ? so long as it gets refreshed. If you need it done by a certain time, why not just schedule an earier refresh ?

Hope this helps

Stuart

[as19]

Thanks for the info Stuart @Burningsuit . Really helpful.

Annoying that a PPU can not publish to pro users. I am the only developer and producer in my Team, and the rest just consume. Having PPU would be very beneficial for me, but would mean my team lose all ability to see dashboards.

Yes and no.
Once new data is uploaded into the excel sheets, i often want to refresh to get some subscriptions triggered and also produce some PDFs ASAP. So quicker does help.
I wonder, is there any evaluation/analysis tool which would allow me to see what the bottleneck is? (My query/model vs Bi Cloud)