Slow then fails to refresh query

[rmitchell]

I have a PowerBI Desktop file that connects to a SSAS cube, importing data (cannot connect live as the server needs to be updated first). The data refresh is usually slow but completes however this month the refresh hangs at about 65k rows or at about 131k rows and never actually completes. I'm connecting over a VPN and I can see that a huge amount of data is being downloaded over the VPN - is it possible I'm running into any file size limits? Is there anything I can do to my query to get it to complete? Have tried turning off the auto data-time but that doesn't seem to make any difference.

Any help much appreciated

[v-jiascu-msft]

Hi @rmitchell,

There could be many causes. Maybe troubleshooting is needed.

1. Limitations of SSAS Multidimensional Models in Power BI Desktop.  Check if your scenario satisfy these limitations.
There are certain limitations to using SSAS MD:

1) Servers must be running SQL Server 2012 SP1 CU4 or later versions of Analysis Services for the Power BI Desktop SSAS MD connector to work properly
2) Cell level Formatting and translation features are not supported in this release of SSAS MD.
3) Actions and Named Sets are not exposed to Power BI, but you can still connect to cubes that also contain Actions or Named sets and create visuals and reports.

2. Maybe you did a large number of complicated query operations. 

Try to create a new PBI file, import data only, no other operations.

3. Maybe the network is bad. 

4. Try to upgrade Power BI Desktop to the latest version. https://www.microsoft.com/en-us/download/details.aspx?id=45331

Maybe a workaround. Do the refresh work in the SSAS server, then copy the whole PBIX file to your computer. 

Best Regards!

Dale

[rmitchell]

Hi Dale,

I am having the same issue again this month, but even worse this time.

The trouble I have is that I don't have easy access to the underlying SSAS server (is managed by another team in another country), so am limited to your (2) cause.

By complicated query operations, what do you mean? I am doing a lot of filters on the information in order to reduce the amount of data that I'm trying to load. Is this the right approach or is it better to actually pull in all the data and then filter in PowerBI?

Thanks,

Rob

[v-jiascu-msft]

@rmitchell,

Hi Rob,

The network performance could be the root cause in your scenario. But the only thing we can do is optimizing the data model. Please refer to these articles for details of tuning.

how-to-tank-your-power-bi-performance

https://www.sqlsaturday.com/SessionDownload.aspx?suid=12289 (a pdf file will be downloaded.)

Best Regards!

Dale