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.

Horizontal Fusion

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).

Fusing Horizontalwise

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

EVALUATE
SUMMARIZECOLUMNS (
    DimDate[FiscalYear],
    "Hats Sales",
        CALCULATE (
            [Sum Sales],
            DimProductSubCategory[EnglishProductSubcategoryName] = "Hats"
        ),
    "Gloves Sales",
        CALCULATE (
            [Sum Sales],
            DimProductSubCategory[EnglishProductSubcategoryName] = "Gloves"
        ),
    "Shoes Sales",
        CALCULATE (
            [Sum Sales],
            DimProductSubCategory[EnglishProductSubcategoryName] = "Shoes"
        )
)

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.