Large Table and Matrix resultset crashes/slow SSAS multidimensional performance

Question

Hi!&nbsp;I stumbled upon a problem with SSAS live multidimensional&nbsp;connectivity when there are many columns in Table or Matrix with large resultset, then it crashes or is very slow. It eats up all the ram in server trying to render the result...&nbsp;The reason is how multidimensional SSAS works with large flat resultsets. Is is described in here... Apparently it is fixed in Excel 2016, but not in Power BI.&nbsp;https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performance-gains/&nbsp;The solution is to disable totals and use some other element to show total values.&nbsp;&nbsp;Hope it helps someone...&nbsp;BW,Kaarel.

v-sihou-msft · Answer

kaarel
&nbsp;
I have seen the generated extra query in SQL Profiler trace.
&nbsp;
Without Total enabled:
&nbsp;
With Total enabled:
&nbsp;

&nbsp;
We have reported this issue internally.
&nbsp;
Regards,

v-haibl-msft · Answer

kaarel
&nbsp;
Can you repro this issue with the preview Matrix visual?
&nbsp;
Best Regards,Herbert

anonymous · Answer

Hi&nbsp;kaarel,&nbsp;Thanks for your post. When I finally found it, after a lot of frustrations, it was the answer to my question.&nbsp;&nbsp;My reports which included large tables continuously loaded and led to full RAM on our server, and eventually wouldn't even load the data.&nbsp;After deselecting the totals for the big tables this issue was resolved.&nbsp;&nbsp;Kind Regards,&nbsp;Paul

anonymous · Answer

kaarel&nbsp;This help me a lot!&nbsp;I don't why, this is still happening.I had to disable the "Totals", and then it was really faster!It didn't fixed yet?&nbsp;v-sihou-msft&nbsp; can you help us?&nbsp;Thank!