Starting with SQL Server 2019 Cumulative Update (CU) 5, DAX-based clients can use SuperDAX functions and query patterns against multidimensional models to boost performance. This is especially important for Power BI users because interactive Power BI reports submit DAX queries to multidimensional models. You can unlock these performance gains right now by upgrading your on-prem Analysis Services servers to CU5 or higher and downloading the latest version of Power BI Desktop. Existing interactive reports in the Power BI Service can benefit without any additional steps, as Power BI generates the optimized SuperDAX queries automatically.
Analysis Services supports both Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) across all server modes so that MDX clients, such as Excel, and DAX clients, such as Power BI Desktop, can seamlessly connect to any type of data model. However, prior to SQL Server 2019 CU 5, Power BI users would notice a significant performance difference when querying multidimensional models in comparison to tabular models because SuperDAX enhancements were not available in multidimensional mode. When Power BI detected that it connected to a multidimensional model it fell back to less performant standard DAX functions and query patterns.
SuperDAX was the original codename for DAX query optimizations introduced with Power BI and SQL Server Analysis Services 2016 for tabular models. SuperDAXMD is the codename to bring these enhancements to the multidimensional area SQL Server Analysis Services 2016 and the latest versions of Power BI. Like SuperDAX against tabular, SuperDAXMD against multidimensional aims to reduce the chattiness between DAX clients and Analysis Services. For example, the vast majority of Power BI visuals take advantage of SuperDAX. They have been optimized to consolidate multiple operations within a standard DAX query into fewer operations for a corresponding SuperDAX query, which in turn results in fewer storage engine queries, which in turn results in noticeably improved query performance. Depending on query type or measure, SuperDAX might help to eliminate hundreds of storage engine queries.
While Power BI visuals can automatically switch to SuperDAXMD, you might also want to double-check the measures in your datasets and optimize the DAX query patterns if possible. In general, optimized queries use the SUMMARIZECOLUMNS function to replace the less efficient standard DAX SUMMARIZE function, and might use DAX variables to calculate expressions only once at the place of definition and then reuse the results in any other DAX expressions without having to perform the calculation again. Other, and perhaps less common SuperDAX functions are SUBSTITUTEWITHINDEX, ADDMISSINGITEMS, as well as NATURALLEFTOUTERJOIN and NATURALINNERJOIN, ISONORAFTER, and GROUPBY. SELECTCOLUMNS and UNION are also SuperDAX functions. For all the details, refer to DAX for multidimensional models in the product documentation.
With SuperDAXMD, Power BI Desktop can now use efficient SuperDAX queries when using a live connection to a multidimensional model. Use the following download link to deploy SSAS 2019 CU5 on your servers and make sure you keep updating your Power BI Desktop clients with every monthly release.
- Cumulative Update 5 for SQL Server 2019 https://support.microsoft.com/help/4552255/cumulative-update-5-for-sql-server-2019
- Power BI Desktop latest updates https://docs.microsoft.com/power-bi/fundamentals/desktop-latest-update