Oh, Quick Question Before You Go…
My customers are pretty smart cookies who are more than proficient in googling. So I know that that last quick question is going to be something rather tasty; something they’re really (really) stuck with, and more often than not something I’ll have to go learn myself.
So this week’s ‘OQQBYG’ was concerning a new preview feature that I hadn’t had a chance to play with: Horizontal Fusion.
What’s Horizontal Fusion? It’s a pretty cool new optimisation feature in Power BI.
First, to get an overview of what Horizontal Fusion is (Spoilers: it’s a fancy
WHERE ... IN clause) check out the announcement here (so I don’t have to type it up again).
And now, a worked example. I’ll be using a subset of AdventureWorksDW restored to an Azure SQL DB, and Direct Query from Desktop. It’s a teeny DB with around 100K fact rows.
And, in DAX Studio, this rather terrible DAX just to highlight the issue.
Patsy was right, You can never have enough hats, gloves or shoes.
Running the DAX, you can see 3 Storage Engine queries are issued, one for each calculate / filter:
Note the total execution time of ~300ms and the single where clause.
Enabling the Fusion preview feature in Desktop…
…and rerunning the query…
…you can see the three calculate filters being fused into a single query, and the execution time dropping to 70ms. That’s pretty cool.
Wondering what the
SELECT TOP (1000001)is all about? It’s a hard limit in Direct Query to prevent super huge resultsets (and can be overridden if you have a premium capacity) (https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-workloads?tabs=gen2#max-intermediate-row-set-count)
But There’s More
That ‘OQQBYG’ had a supplimentary. Does this work with Direct Query datasets?
We can test that out easily. First, I deployed the AdventureWorksDW model to the Power BI service, then created a new report connected to our new DQ Dataset and connected DAX studio to this. I then kicked off a SQL trace in Azure Data Studio and re-ran my sketchy DAX.
And there you have it, one fused query from a DQ dataset. Patsy would be proud.