Performance Issues with SSAS Tabular Model in Excel

[rlussky]

Hello,

I currently have a tabular model set up through Analysis Services. When connected to Power BI, the performance is reallly good. I am currently having an issue with the connection to Excel though Analysis Services though. The performance is really poor and I haven't been able to find any good resources as to why. 

It seems to run fine if I start with a measure and use just one dimension table. As soon as any other dimension tables are brought in, the OLAP query spins for many minutes, sometimes 10-20 minutes(!) which is unacceptable, especially when the OLAP query runs with every move (new row, column, filter, value, design change, etc).

I am curious if anyone out there can help with this.

* Are there best practices I am missing? (i.e. starting with a measure when building a Pivot Table from Analysis Services connection)

* Does Excel always perform poorly with Tabular Models?

* Do I need a Tabular Model for Power BI and a seperate Multidimensional Model for Excel?

Thanks in advance to any help!

[Icey · Accepted solution]

Hi @rlussky ,

Please check:

1. * Are there best practices I am missing? (i.e. starting with a measure when building a Pivot Table from Analysis Services connection)

Here is a blog about speeding up the Excel queries for Tabular SSAS: Faster Excel Queries For Power BI & Azure Analysis Services | XLCubed Blog. Hope it could help you.

2. * Does Excel always perform poorly with Tabular Models?

In most cases, yes. Excel Pivot Tables query Tabular SSAS using MDX, and sometimes the MDX they generate is not ideal. And response times can be particularly slow where the tables have lots of columns and are large in size.

3. * Do I need a Tabular Model for Power BI and a seperate Multidimensional Model for Excel?

Maybe you can use Power BI for both.😉

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rlussky]

@Icey - So, would I have to purchase a XLCubed license for each of my ~100 end users for using the method in your link? https://blog.xlcubed.com/2021/02/faster-excel-queries-for-power-bi-azure-analysis-services/

[Icey]

Hi @rlussky ,

I know little about XLCubed. If you want to use XLCubed, please contact their support in order for you to get the most accurate information:

If you have any questions about Power BI, please feel free to contact me. I will be happy to help you.😀

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rlussky]

I was really hoping not to have to use third party software with licenses to accomplish better performance using Excel. I guess I will have to train more on Power BI with the end users.