Skip to main content

Power BI

Completed

Improve performance of MDX queries using Analyze in Excel

Vote (399) Share
Marco Russo's profile image

Marco Russo on 04 Jan 2020 17:08:41

When you use Analyze in Excel, MDX queries are sent to the Power BI dataset. If you have a PivotTable with 10 measures, the performance is usually slower compared to a similar Matrix in Power BI with the same content. Power BI generates DAX queries.
One of the reasons why this difference exists is that the DAX query is optimized using a process called "fusion", which includes in a single storage engine request the aggregations required for multiple measures, whereas this feature has not been implemented for MDX queries.
Implementing fusion for MDX queries would dramatically improve the performance of many pivot tables in Excel where there are many measures in the same pivot table.

Administrator on 14 Jan 2021 22:42:55

Shipped in October 2020.

Comments (27)
Marco Russo's profile image Profile Picture

Marco Russo on 10 Nov 2020 17:31:08

RE: Improve performance of MDX queries using Analyze in Excel

This optimization has been implemented in Power BI Desktop October 2020. It is also available in Azure Analysis Services. You should close the item as "Implemented" !!

Marco Russo's profile image Profile Picture

Jaime Derks on 27 Oct 2020 15:22:05

RE: Improve performance of MDX queries using Analyze in Excel

I agree.

Microsoft please implement a native DAX support as soon as possible!

Marco Russo's profile image Profile Picture

Awen Kerzreho on 15 Jul 2020 10:07:32

RE: Improve performance of MDX queries using Analyze in Excel

We just encountered the same perf problems after deciding that we should try transitioning to tabular from multidim. Excel pivot tables are our main client to access our cubes, and the poor performances when querying tabular models might just completely stop us from taking this step forward. Native DAX support from Excel is really needed!

Marco Russo's profile image Profile Picture

Scott Lu on 10 Jul 2020 17:20:59

RE: Improve performance of MDX queries using Analyze in Excel

Power BI is good, but still a lot of users love excel pivot services.
So why not support DAX for pivot services to improve the query performance. The MDX is really performance killer for tabular cube if the dimension is large.

Marco Russo's profile image Profile Picture

Lars Heinemann on 06 Jul 2020 00:17:51

RE: Improve performance of MDX queries using Analyze in Excel

I noticed a new flag in the advanced options on my azure analysis services instance called "OLAP \ Query \ MdxFusionOptimizations" . Seems like Microsoft is working on something behind the scenes. Very curious to understand when this entire feature is going to see the light.

Marco Russo's profile image Profile Picture

Lars Heinemann on 06 Jul 2020 00:16:26

RE: Improve performance of MDX queries using Analyze in Excel

I agree. It is almost impossible to use Excel with a bigger tabular model. A query running in PowerBi less than 10 seconds takes almost 5 minutes to complete via Excel. Microsoft please implement a native DAX support as soon as possible!

Marco Russo's profile image Profile Picture

Daniel on 06 Jul 2020 00:15:26

RE: Improve performance of MDX queries using Analyze in Excel

Also take into account that every time you use Grand Totals in Excel, MDX queries are being generated with crossjoins which causes the performance of the queries to be quite poor specially when using attributes from 2 large dimensions, such as Customers and Products for example. It would help a lot having a DAX generator on the Excel side and depending on the Analysis Services (OLAP vs Tabular) use one or the other to keep compatibility.

Marco Russo's profile image Profile Picture

Bo on 06 Jul 2020 00:15:08

RE: Improve performance of MDX queries using Analyze in Excel

Please improve, we are struggling at The Coca-Cola Company with the query engines, response, and error codes. Many tickets with the MS team. Many business folks use Excel and native BI is simpliy not the answer.

Marco Russo's profile image Profile Picture

Rajib Mishra on 06 Jul 2020 00:14:58

RE: Improve performance of MDX queries using Analyze in Excel

This is an imp update needed

Marco Russo's profile image Profile Picture

Power BI User on 06 Jul 2020 00:12:55

RE: Improve performance of MDX queries using Analyze in Excel

I had to retrieve table from SSAS model using EVALUATE statement into local instance (Excel, Power BI Desktop). On 500K table, it took 1 minute in Power BI and 30 min in Excel to retrieve it. Native support of DAX in Excel is a must.