With a recent update of the Analysis Services Tabular engine in Power BI, Multidimensional Expressions (MDX) clients, such as Microsoft Excel, can now enjoy improved query performance. This MDX performance improvement alleviates some of the optimization burden for measures used in a PivotTable. For example, Excel users connecting to a Power BI dataset by using the Analyze in Excel feature, the XMLA endpoint, or the newly announced Power BI Datasets within Excel feature, will now notice significantly better PivotTable performance out of the box.
The MDX query performance improvement is the result of a clever Formula Engine (FE) optimization aimed at reducing the number of Storage Engine (SE) queries per MDX query, similar to DAX optimizations known as DAX Fusion. With MDX Fusion, the most common MDX query patterns can now trigger far fewer SE queries where previously numerous such SE queries were necessary for different granularities. Fewer SE queries mean fewer expensive scans over large datasets, which ultimately results in sizeable performance gains especially when connecting to a Tabular model or Power BI dataset in Direct Query mode. The corresponding MDX Fusion idea received several hundred votes on ideas.powerbi.com, and we are happy to announce availability of MDX Fusion at this point. Note that MDX Fusion is not available with SQL Server Analysis Services 2019 or earlier.
Without going into too much detail, let’s look at a simplified but nonetheless typical MDX query that Excel might generate for a PivotTable against a Power BI dataset.
SELECT
{[Measures].[Avg Unit Price],[Measures].[Total Sales Amount]} ON COLUMNS,
NON EMPTY Hierarchize(DrilldownMember(CrossJoin(
{[DimProductCategory].[EnglishProductCategoryName].[All],[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].AllMembers}, {([DimProductSubcategory].[EnglishProductSubcategoryName].[All])}), [DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].AllMembers, [DimProductSubcategory].[EnglishProductSubcategoryName]
)) ON ROWS
FROM [Model] WHERE ([DimDate].[CalendarYear].&[2014])
PROPERTIES VALUE
The following screenshot shows the results based on AdventureWorks sample data, listing the average unit price and total sales amount for each product subcategory and category. The NON EMPTY CrossJoin causes the result to include only those rows where data is available for the specified measures and the WHERE clause limits the results to the year 2014. The most interesting part is that Excel references all members of a hierarchy and usually also includes the special “ALL” member in order to retrieve all levels of granularity (subtotals and totals) in a single MDX query.
You can see multiple levels of detail in the PivotTable screenshot above for the two measures. At the lowest granularity, each subcategory has its own Avg Unit Price and Total Sales Amount, and each category summarizes its subcategories, and then at an even higher level, the PivotTable also includes the grand total. Remember that Excel only sends a single MDX query. Accordingly, the Analysis Services engine in Power BI must compute the cell values for the two measures based on three different granularities on the axes. This might cause the Formula Engine to trigger six or more queries to the Storage Engine, as listed in the following table.
| Avg Unit Price | Total Sales Amount | |
| Subcategory totals | Average of Unit Price from FactInternetSales grouped by Product Category and Product Subcategory for 2014 | Total of Sales Amount from FactInternetSales grouped by Product Category and Product Subcategory for 2014 |
| Category totals | Average of Unit Price from FactInternetSales grouped by Product Category for 2014 | Total of Sales Amount from FactInternetSales grouped by Product Category for 2014 |
| Grand total | Total Average of Unit Price from FactInternetSales for 2014 | Total of Sales Amount from FactInternetSales for 2014 |
Looking again at the PivotTable screenshot, it is relatively obvious that the Formula Engine could calculate the category totals and grand total for each measure based on the subcategory totals. In other words, the two SE queries for the category totals and the two SE queries for the grand totals are not necessary. This alone is a significant optimization.
Perhaps less obvious is the fact that both measures, Avg Unit Price and Total Sales Amount, access columns from the FactInternetSales table and use the same where and group by clauses, making it possible to combine even the remaining subtotals queries in a straightforward way. Hence, a single combined (or fused) SE query can replace the previous six SE queries for a nice boost in MDX query performance: Sub-Average of Unit Price, Sub-Total of Sales Amount from FactInternetSales grouped by Product Category and Product Subcategory for 2014.
A comparison of the following two query traces helps to illustrate the impact of MDX Fusion. The first is a trace from SQL Server Analysis Services 2019, which shows the six separate SE queries, plus an additional SE query for the date dimension attributes. The TextData column reveals enough detail to correlate the SE queries with the pseudo expressions from the previous table.
The second trace shows the processing with exactly the same Excel MDX query against a comparable dataset in Power BI. As you can see, the number of SE queries dropped from seven to two. The MDX query not only returns faster, MDX Fusion also helps to reduce the workload on the data source in DirectQuery mode as Power BI sends significantly fewer DirectQuery queries. Of course, MDX Fusion is also available in import (Vertipaq) mode.
MDX Fusion is an important Analysis Services Tabular capability that can help improve the user experience in Excel and other client applications that use the MDX query language against tabular models and Power BI datasets. Check it out by deploying your datasets in Power BI and then connecting with Excel by using Analyze in Excel, the newly announced Power BI Datasets within Excel feature, or better yet connect to your datasets on Power BI Premium through the XMLA endpoint.