Improve performance of MDX queries using Analyze in Excel
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.
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.
Rajib Mishra commented
This is an imp update needed
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.
German Henriquez commented
Good idea. It´s very necessary
Lars Heinemann commented
Yes - I can completely agree. A native DAX support in Excel would significantly improve the user experience and subsequently also the load on the analysis service/powerBI.
A must have feature to have the best excel user experience
Siddharth Kumar commented
This is a very good idea! We use a lot of Excel as it is one of the best tool and gives the flexibility and low barrier of entry for "normal users" to analyze the numbers in Excel. A lot of people appreciates Power BI visuals but the bulk of users will not have time to spend on working in Power BI because there is a learning curve to it. This will for sure drive efficiency!
Yes!! This feature is highly needed to improve the performance of Analyze in Excel
Christine Beck commented
Very much needed; this functionality is useful but extremely slow.
Great idea, I think this could be very helpful at a couple of projects.
This is a great idea, the performance of Excel connected to a Power BI dataset or an Azure Analysis Services tabular model can be pretty bad compared to Power BI.
Microsoft is in such a unique position having the leading BI platform Power BI and the world’s favorite data tool Excel but the latter really needs some more love as a Power BI front end. The potential impact is HUGE!
Please also consider voting for these related ideas by other experts on the Excel User Voice:
A native DAX interface in Excel pivot-tables could be a solution
Steven Neumersky commented
I could not agree more with this.
This would be very helpful
Johnny Winter commented
Same problem, different solution...
No idea how practical this is to implement, or whether the suggested resolution above is a better suggestion, but ultimately, the end goal for me it to make sure the user experience using Excel has comparable perfromance to using PBI.
While Power BI is useful for high-level dashboarding for senior management, the real in-depth analysis normally happens in Excel. Thus any speed enhancement in Analyze in Excel will be greatly appreciated!
Absolutely essential - nearly on par with data refresh in Excel Online.
Alex Barbeau commented
This is a great idea, I'll be sharing the link with my team and to clients where this would be applicable.