Poor performance consuming Analysis service Tabular model via Excel

Question

we run MS Analysis service OnPrem and data are consumed via Power BI and via Excel using Tabular model.

In some cases, we are experiencing some performance issue. If we compare some “query execution” from PowerBI and from Excel we notice very different performance, and PowerBI is much faster (50x faster).

We assume issue is related to MDX compared to DAX . We also executed, directly in Tabular using SSMS, MDX query generated by Excel, to exclude specific client bottleneck.

We sound some articles/blogs related to this scenario, and discovered this behavior is quite common, but we did not found anything helpulf to address some workaround in order to mitigate the issue.

It seems that MDX Fusion capability, available in Azure Analysis service, helps a lot in performance improvement, but we still need to support our scenario where Analysis service is onPrem, so this is not a solution we can deploy in short term

Could you suggest something suitable ?

Is there a sort of checklist of known limitations using Excel with Analysis OnPrem ? • Excel version compatibility (By the way we executed mentioned test with Office 365 Excel) • Some specific Release level for Analysis Service • Some fix / parameterization that could be adjustend on Analysis service • Some specific MDX statement that we should avoid

Thanks in advance

Answer 1

There are many ways to connect to SSAS from Excel. The performance for each varies drastically. I have found the very best performance to be an Excel Table that has a DAX statement behind it. The easiest way to do this is to write the DAX Evaluate statement in DAX Studio and output to Excel as a linked query.

Performance for even large tables is lightning fast. Seconds for a million rows. It's amazing.

If the row limit is a problem, you can use this to generate the connection, and then create a Pivot off of the connection (Insert > PivotTable > From External Data Source > Choose Connection). I don't do this often, but you might see if this improves your performance for a pivot off of large data.

The more you filter your DAX, the better the performance. After the Excel Table is created, you can modify the DAX by right clicking the table and selecting Table > Edit Query....

And then change the Command Text