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

    Guido on 06 Jul 2020 00:05:48

    RE: Improve performance of MDX queries using Analyze in Excel

    A native DAX interface in Excel pivot-tables could be a solution

    Marco Russo's profile image Profile Picture

    Steven Neumersky on 06 Jul 2020 00:05:44

    RE: Improve performance of MDX queries using Analyze in Excel

    I could not agree more with this.

    Marco Russo's profile image Profile Picture

    Power BI User on 06 Jul 2020 00:05:35

    RE: Improve performance of MDX queries using Analyze in Excel

    This would be very helpful

    Marco Russo's profile image Profile Picture

    Al on 06 Jul 2020 00:05:34

    RE: Improve performance of MDX queries using Analyze in Excel

    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!

    Marco Russo's profile image Profile Picture

    Power BI Ideas Admin on 06 Jul 2020 00:05:34

    RE: Improve performance of MDX queries using Analyze in Excel

    Same problem, different solution...

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39327469-analyze-in-excel-using-dax

    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.

    Marco Russo's profile image Profile Picture

    Martin Kratky on 06 Jul 2020 00:05:32

    RE: Improve performance of MDX queries using Analyze in Excel

    Absolutely essential - nearly on par with data refresh in Excel Online.

    Marco Russo's profile image Profile Picture

    Alexander Barbeau on 06 Jul 2020 00:05:31

    RE: Improve performance of MDX queries using Analyze in Excel

    This is a great idea, I'll be sharing the link with my team and to clients where this would be applicable.