Large Table and Matrix resultset crashes/slow SSAS multidimensional performance

[kaarel]

Hi!

I stumbled upon a problem with SSAS live multidimensional 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...

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.

https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performa...

The solution is to disable totals and use some other element to show total values.

Hope it helps someone...

BW,

Kaarel.

[Anonymous]

@kaarel This help me a lot!

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?

@v-sihou-msft  can you help us?

Thank!

[kaarel]

SQL Analysis Services 2019 already has a fix. Once Power BI Desktop update comes out we should be fine. Only Microsoft can answer when.

https://powerbi.microsoft.com/en-us/blog/whats-new-for-sql-server-2019-analysis-services-rc1/

[Anonymous]

---

@kaarel wrote:

SQL Analysis Services 2019 already has a fix. Once Power BI Desktop update comes out we should be fine. Only Microsoft can answer when.

https://powerbi.microsoft.com/en-us/blog/whats-new-for-sql-server-2019-analysis-services-rc1/

---

Oh, ok!

Thanks for let me know, I'm new to Analysis Services. Also thinking, what people and companies are using without OLAP Cube? Is there better option?

I'm using SQL Analysis Services 2017, I already disable the totals for that. If you have any more tips, please also let me know.

Really thank you!!

[v-haibl-msft]

@kaarel

Can you repro this issue with the preview Matrix visual?

Best Regards,
Herbert

[Anonymous]

Hi @kaarel,

Thanks for your post. When I finally found it, after a lot of frustrations, it was the answer to my question. 

My reports which included large tables continuously loaded and led to full RAM on our server, and eventually wouldn't even load the data. After deselecting the totals for the big tables this issue was resolved. 

Kind Regards, 

Paul

[v-sihou-msft]

@kaarel

I have seen the generated extra query in SQL Profiler trace.

Without Total enabled:

With Total enabled:

We have reported this issue internally.

Regards,