Power BI Horizontal Fusion, the query optimization we’ve been waiting for

This last September’22 Microsoft announced an awaited query performance optimization for Data Analysis Expressions – or DAX queries – aimed at processing DAX queries more efficiently.

On a nutshell, Horizontal Fusion means less trips back and forth to bring you the same result quicker than before.

ABCloudz is hard-wired to the market for both new technologies, as well as the desired advancements and requested improvements the development and IT solutions community look forward to better providing to our customers.

We invite you to closely watch for this Power BI article series while we help you explore and evaluate how the data visualization platforms and its integration features can help you better harness from your data streams and applications.

With Horizontal Fusion, your Power BI will require less computing power to deliver more on your data analysis requests. It has been named this way, to represent how the new engine will approach every DAX request, by fusing multiple smaller data source queries together into a single larger data source query behind the scenes.

In doing so, this reduces the number of times, volume of scans, and the amount of logical processing demands over large data sources. This translates to a significant gain on query scalability, meaning this is an exponential improvement on all DAX queries, including the requested DirectQuery mode.

Jumping the query scan ladder

The diagrams below give us a better idea on where you’re gaining, and how it made possible for us to invest less compute power, and yet get in return faster results that demand less from your on-premises or cloud environments.

Before Horizontal Fusion, the DAX request above resulted in a Waterfall of six data source queries, averaging about 4 seconds each for a total response query of 23.2 seconds.

After Horizontal Fusion we can see how DirectQuery was able to fuse the previous multiple Waterfalls into a single Waterfall query, with a total response time of about 5 seconds. At a glance you’ll notice a slight increase time on the Waterfall query. Yet when looking at the total time we clearly see a savings of about 19 seconds, while comparing the previous 23.2 seconds total time, against the now total 5 seconds only for the same request.

Enable, try, and experience it yourself

If you’re curious yourself on how much better Horizontal Fusion can make your Power BI and DirectQuery features come to a better life, make sure to enable it from Power BI Desktop, as by default, new features come disabled.

Before giving it a try, make sure you’re running a September’22 or later version of Power BI Desktop.

To enable it just follow the steps below:

• File > Options and Settings > Options
• From your Options dialog enable Horizontal Fusion as showing below
  

As of this moment, Horizontal Fusion is only available from Power BI Premium, but not to worry as according to Microsoft, it is already in the motion to be enabled at the following releases of Power BI Pro in the coming months.

Horizontal Fusion and Azure Analysis Services

Horizontal Fusion It is also targeted to be enabled in Azure Analysis Services this year of 2022. As fusing data queries together and avoiding unnecessary roundtrips is a tried and proven approach to boost query performance in Power BI and other query engines that adopt it before.

Multiple performance optimizations aimed at reducing the number of data source queries for DAX or MDX – Multidimensional Expressions – queries, the previously known as DAX Fusion and MDX Fusion, significantly improved query performance for DAX and MDX clients in the past regardless of a dataset’s storage mode. Horizontal Fusion now takes things to the next level.

With that in mind, we’d recommend you consider move your Analysis Services Tabular datasets to Power BI when possible – as previously covered on our XMLA Endpoints article – looking into benefit from these performance upgrades and optimizations to scale up your organization’s data processing, analysis, and visualization experiences.

Given this is the very first announcement of this new approach to Query Fusing, stay tuned for more to come in this awaited performance enhancement, and as always, you can count on ABCloudZ as your specialized source for all your Microsoft solution needs and upgrades.

Please contact us for more information and your business needs evaluation.