PowerBI Desktop Refresh - timeout or fails, or SLOW

[kenab]

I LOVE PowerBI...

I use PowerBI Desktop for all my report creation. Many of these reports have daily iterations, which require me to update the data quite regularly in PowerBI Desktop. I am connected to an on-prem SQL Data Warehouse and several different tables for all the different report data. PROBLEM: I consistently get timed out when trying to update the data from within PowerBI Desktop. I end up having to update each individual table separately which is VERY time consuming, even these individual updates can each take quite some time to update (very inconsistent, sometimes a 33K line table will updated in 3 minutes, other times, it will updated in 3 SECONDS...) Any ideas to help optimize these updates?

I am using PowerBI Enterprise Gateway for daily updates to the data, but as you know, when I edit the report in PowerBI Desktop, I access a non-updated file, hence the need to update the file. If only I could access the file that is updated by the Enterprise Gateway....that would save me some time, but that does not solve my problem.  Ideas please?

[Seth_C_Bauer]

@kenab I'm assuming there is a reason for not using Direct Query? - this would resolve the load issues.

Also, what sort of memory do you have on your machine? That will directly impact how fast things load each time... Do you have other processes running at times and other times nothing but Power BI?

[kenab]

I use DAX heavily in places, so direct query is not the best option.  Also, hundreds of peple will access these reports on a daily basis, our Data Warehouse could not handle the traffic.

Desktop machine is i5 1.7 with 8gigs of ram.  Not a bad little machine.

No other processes running (well, occasionally IE11 is running to update refresh stuff for other files in PowerBI), this machine is ONLY for PowerBI Desktop.  I remote desktop connect to it from my other production machine.  This machine is logged in with a services account.  This makes publishing reports much easier and they are associated with this services account, and not my email address..

Other ideas?

[ianbarker]

Hi @kenab

Are you by any chance using any Power Query/M in your queries?

Are you doing any merges, filters or anything like that ahead of doing your DAX?

I've found that if you are doing anything other than selecting a table in the Query Editor it causes timeouts, fails, and just general slowness so I now avoid doing anything to shape the data in the query editor first.

[Seth_C_Bauer]

+1 @ianbarker - Good points!

[Seth_C_Bauer]

@kenab Under File -> Options (Under "Current File" -> Data Load) There are a bunch of options that you can disable which may help in speeding things up. If I recall "Allow data preview to download in the background" is a quick one that should speed up a full load. You could play around with other options as well depending on your need.

Out of curiousity, how close are you to the 1GB limit on the Desktop file?

With such a large number of users have you thought about building your model in Tabular SSAS especially with heavy DAX development?

[kenab]

I am trying these suggestions now, and I will research using Tabular SSAS (I don't know about this at all, so I need to research.)

My file sizes never exceed 45 Megs, so all files are relatively small.  Most of my reports are under 15 megs.

I really appreciate these suggestions, thank you.

I have also starting migrating away from using query editor for query changes.  I hope to point to full tables or at least views (which will do the manuipulation I need).  I hope this will solve my problem (lenghtly process to migrate all my reports, but the right thing to do in the long run anyhow, regardless of refresh performance).

[kenab]

The suggestions helped ease some of my pain, but refreshes are still VERY slow, and still fail the majority of the time.

By removing all check boxes on Data Load under OPTIONS, I do find that I am able to refresh all tables most of the time, although it still takes 3 or 4 attempts before it finaly does not time out.

Still frustrated, any other ideas?

[ianbarker]

Hi @kenab

When you say very slow, what sort of timings are we talking about?

Assuming you've removed all the queries (other than the Source step) and you're only using DAX to create your calculated columns and measure, it could be either network or the machine you are developing on which is the bottleneck.

Assuming the machine has got plenty of RAM (I've found the more RAM the better with PBI Desktop), you could also check your options. Under the Global Data Load tab, try clearing the cache and setting your Maximum allowed (MB) to 4096.

Also, under the Current File Data Load tab, make sure Update relationships when refreshing queries is unchecked and Autodetect new relationships after data is loaded is unchecked.

One final consideration is around your Data Warehouse. Is it being written to/updated at the same time you are refreshing the desktop file? You probably want to avoid this.

I'm currently working on a PBI desktop file that is connected to an on-premise SQL Data Warehouse. The file is about 30MB and to do a full refresh it takes a few minutes. Maybe 5 minutes maximum.