PowerBI Desktop against SSAS 2016 Live query extremely slow and timesout

[ASHOKKPIN]

We Migrated our Analysis Service from 2012 SP2 to 2016 SP1 CU1. We have PowerBI Desktop reports that do direct query to SSAS Instance

The PowerBI Reports have become extremely slow and times out certain visuals when we run against 2016 SSAS.

Same report run fine against 2012.

The 2016 Instance has double the # of CPUs and Double Memory (128GB)Unable to figure out anything. Have gone through all memory settings etc.

Any assistance is greatly appreciated.

MSDN Query Posted

[Phil_Seamark]

Are they Tabular or MDX cubes?

[ASHOKKPIN]

They are all MDX

[Phil_Seamark]

Interesting in that  we've just upgraded to SQL 2016 and live connections are definitely slow for even basic queries.

The same query in an Excel Pivot Table against the same cube is noticably faster.

[v-sihou-msft]

@ASHOKKPIN@Phil_Seamark

We have reported this issue to SQL Team internally.

Regards,

[ASHOKKPIN]

Thanks @v-sihou-msft Please let me know if we can provide additional info. We are more than happy to have a webex as this is affecting our production systems after the upgrade.

[ASHOKKPIN]

HI @v-sihou-msft - Any luck with SQL Team. What we are finding is as soon as we add the measure, it starts clocking. some behavious change with SQL2016 compared to SQL2012 SSAS

[daandamhuis]

I noticed this issue too, we have some simple measures that run great in the Excel connection to SSAS 2014, but in Power BI it maxes out the CPU and it takes about 60 seconds instead of < 1 second.

[tannera]

Have any of you found the source of the performance issue? 

[ASHOKKPIN]

Hi  - Based on the analyis and Inputs from microsoft - there has been a change in the way DAX from PowerBI generates/runs on SSAS 2016 MDX instances. This change was done because of a bug in earlier versions where it produced incorrect results.

This fix while ensures that results are accurate, in certain edge cases results in slow query performance and timeouts.

At this point there is no resolution to the issue other than

1. Try not to create reports that are very wide in Power BI.

2.Use SSRS for such reports

[Anonymous]

Hi Ashokkpin

We had the same issue.

Clear the cache in PBI Desktop.

This resolved our problems.

[ASHOKKPIN]

Thanks Pfister. Unfortunately that did not help. Issue is persisting in PowerBI.com as well