2

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

Giovanni Sellitto
  • 21
  • 2
  • What version of SSAS are you running on prem? Can you connect Object Explorer and share the version number that’s displayed? – GregGalloway Sep 08 '22 at 23:45
  • 1
    MDX Fusion will ship in SQL Server 2022 Analysis Services https://learn.microsoft.com/en-us/analysis-services/what-s-new-in-sql-server-analysis-services?view=asallproducts-allversions – David Browne - Microsoft Sep 10 '22 at 23:44
  • We are running Version 15.0.32.55 – Giovanni Sellitto Sep 12 '22 at 21:10
  • Note, even with Azure Analysis Services (and MDX Fusion), we have found lesser performance in Excel (MDX)compared to Power BI (DAX). Just the downside of using Excel. – Gabe Oct 03 '22 at 21:30

1 Answers1

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.

Output to Excel from DAX Studio

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....

Editing DAX behind a table

And then change the Command Text

Here's the DAX

TheRizza
  • 1,577
  • 1
  • 10
  • 23