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)
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" !!
RE: Improve performance of MDX queries using Analyze in Excel
I agree.
Microsoft please implement a native DAX support as soon as possible!
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!
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.
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.
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!
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.
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.
RE: Improve performance of MDX queries using Analyze in Excel
This is an imp update needed
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.