Analysis Services Tabular improves MDX query performance in the cloud

Thanks to a clever Formula Engine (FE) optimization aimed at reducing the number of Storage Engine (SE) queries per Multidimensional Expressions (MDX) query, Analysis Services Tabular delivers significantly improved query performance for MDX clients, such as Microsoft Excel. The improvements are available in Azure Analysis Services (AAS) as well as in Power BI.

Similar to the Data Analysis Expressions (DAX) optimizations known as DAX Fusion, the MDX optimizations, knowns as MDX Fusion, optimize the most common MDX query patterns to avoid unnecessary SE queries. Fewer SE queries mean fewer expensive scans over large datasets​, which ultimately results in sizeable performance gains. MDX queries not only return faster, MDX Fusion also reduces the workload on the data sources as Analysis Services Tabular sends significantly fewer queries in DirectQuery mode. Of course, MDX Fusion is also available in import (Vertipaq) mode. For a more detailed explanation, refer to the blog article Announcing improved MDX query performance in Power BI.

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 Azure Analysis Services and then connecting with Excel, or use Power BI and 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. Note, however, that MDX Fusion is not available with SQL Server Analysis Services 2019 or earlier.