CallBackID issues: Cardinality

Abstract, from a SQLBI article. Link to the original article on SQLBI.com click herehttps://sql.bi/816722. I just could recommend to read every single article from SQLBI, Alberto Ferrari XOR Marco Russo in full length. Below some key pattern and notes from my side as my personal abstract.

Optimizing callbacks in a SUMX iterator

What was the reason for this article from SQLBI, Alberto Ferrari. The function SUMX is iterating over Sales in the contoso database.

 Sales[Sales Amount] =
        SUMX (
            Sales,
            Sales[Quantity] * Sales[Net Price]
        )

Marco Russo would say „so far so good“, but happens when ‚Sales[Net Price]‘ will be wrapped by the ROUND function.

Sales[Sales Amount] =
        SUMX (
            Sales,
            Sales[Quantity] * ROUND ( Sales[Net Price], 1 )
        )

Now, the intervention of the formula engine is required and we generate a callback.

If there’s no possibility to avoid callbacks between SE & FE, this approach aims at reducing the number of rows by taking advantage of the cardinality from Sales[Net Price] and so reducing the number of neccecary iterations responsible for the callbacks. The sales table in that case has around 200mio rows, but Sales[Net Price] only around 25k unique values.

Sales[Sales Amount] =
        SUMX (
            SUMMARIZE (
                Sales,
                Sales[Net Price]
            ),
            CALCULATE ( SUM ( Sales[Quantity] ) ) * ROUND ( Sales[Net Price], 1 )
        )

Therefore, instead of iterating over the sales table, the Sales table will be grouped by Sales[Net Price].

Variation

Introducing variables in that case, will make the code more readable but hurts performance.

Sales[Sales Amount] =
        VAR RoundedNetPrices =
            ADDCOLUMNS (
                SUMMARIZE ( Sales, Sales[Net Price] ),
                "@Rounded Net Price", ROUND ( Sales[Net Price], 1 ),
                "@Sum Of Quantity", CALCULATE ( SUM ( Sales[Quantity] ) )
            )
        VAR Result =
            SUMX ( RoundedNetPrices, [@Rounded Net Price] * [@Sum Of Quantity] )
        RETURN
            Result

This pattern with ADDCOLUMNS will be slower, because it requires two scans of the sales table, details see https://sql.bi/81672.