We are excited to announce the initial release of a query performance optimization aimed at processing Data Analysis Expressions (DAX) queries more efficiently than previously possible by reducing the number of data source queries required to generate the results. We call this optimization “Horizontal Fusion” to highlight the approach of fusing multiple smaller data source queries together into a larger data source query. Fewer data source queries mean fewer roundtrips and fewer expensive scans over large data sources, which ultimately results in sizeable DAX performance gains plus reduced processing demand at the data source. Not only do DAX queries run faster with Horizontal Fusion, especially in DirectQuery mode, but scalability also increases.
The following diagram shows the performance characteristics of a sample DAX query in DirectQuery mode before and after Horizontal Fusion was enabled. Without Horizontal Fusion, this DAX query resulted in six data source queries, each running between 3 and 4 seconds for a total DAX query response time of approximately 23 seconds. With Horizontal Fusion, on the other hand, DirectQuery was able to pack this entire workload into a single data source query, reducing the DAX query response time to roughly four seconds in total. Without any modifications to the DAX query, the dataset, or the report, DirectQuery was able to return results roughly six times faster than without Horizontal Fusion. If you happen to notice your DirectQuery reports running faster now, it may just be thanks to the magic of Horizontal Fusion! Of course, Horizontal Fusion can also help in Import mode, yet the performance gains will not be as noticeable because Import mode is already incredibly fast.
As with any preview feature, you need to enable Horizontal Fusion in Power BI Desktop explicitly. Display the Options dialog, switch to the Preview features tab, and select the checkbox, as in the following screenshot. Perhaps compare the performance of your DirectQuery reports with and without Horizontal Fusion to see for yourself how your reports will benefit.
Fusing data queries together and avoiding unnecessary roundtrips is a tried and proven approach to boost query performance in Power BI. Several performance optimizations in the query engine aimed at reducing the number of data source queries per DAX or MDX query, known as DAX Fusion and MDX Fusion, significantly improved query performance for DAX and MDX clients regardless of a dataset’s storage mode. Horizontal Fusion now takes things to the next level.
Note that Horizontal Fusion is already available in Power BI Premium and it will also be enabled in Power BI Shared (Pro) in the coming months. It will also be enabled in Azure Analysis Services, and SQL Server Analysis Services 2022! We recommend that you move all your Analysis Services Tabular datasets to Power BI when possible so that you can benefit from these groundbreaking innovations and performance optimizations as soon as they become available to deliver the best report experience to your users.
The initial Horizontal Fusion preview covers some common scenarios, but we are already working on broadening the scope to cover even more of the typical DAX query patterns generated by Power BI reports so that Horizontal Fusion kicks in more often to unlock even more performance gains. In other words, Horizontal Fusion is a continuous effort. So, stay tuned for even more announcements in this area in the coming months!