watching Jeffrey Wang as a stay stream visitor with Reid Havens, and one of many dozen fantastic issues that Jeffrey shared with the viewers was the checklist of optimizations that the DAX engine performs when creating an optimum question plan for our measures.
And, the one which caught my consideration was concerning the so-called “Sparse measures”:
To make it easy, when you outline the measure, System Engine in VertiPaq will add an implicit NonEmpty filter to the question, which ought to allow the optimizer to keep away from full cross-join of dimension tables and scan solely these rows the place data for the mixture of your dimension attributes actually exist. For folk coming from the MDX world, the NonEmpty perform might look acquainted, however let’s see the way it works in DAX.
The factor that almost all resonated with me was when Jeffrey suggested in opposition to changing BLANKs with zeroes (or no matter specific values) in Energy BI calculations. I’ve already written how one can deal with BLANKs and change them with zeroes, however on this article, I need to give attention to the potential efficiency implications of this choice.
Setting the stage
Earlier than we begin, one necessary disclaimer: the advice to not change BLANK with 0 is simply that — a suggestion. If the enterprise request is to show 0 as an alternative of BLANK, it doesn’t essentially imply that it’s best to refuse to do it. In most eventualities, you’ll in all probability not even discover a efficiency lower, however it’ll rely upon a number of various factors…
Let’s begin by writing our easy DAX measure:
Gross sales Amt 364 Merchandise =
CALCULATE (
[Sales Amt],
FILTER ( ALL ( 'Product'[ProductKey] ), 'Product'[ProductKey] = 364 )
)Utilizing this measure, I need to calculate the overall gross sales quantity for the product with ProductKey = 364. And, if I put the worth of this measure within the Card visible, and activate Efficiency Analyzer to examine the occasions for dealing with this question, I get the next outcomes:
DAX question took solely 11ms to execute, and as soon as I switched to DAX Studio, the xmSQL generated by the System Engine was fairly easy:
And, if I check out the Question plan (bodily), I can see that the Storage Engine discovered just one present mixture of values to return our information:
Including extra substances…
Nonetheless, let’s say that the enterprise request is to research information for Product Key 364 on a each day stage. Let’s go and add dates to our report:
This was once more very quick! I’ll now examine the metrics inside the DAX Studio:
This time, the question was expanded to incorporate a Dates desk, which affected the work Storage Engine wanted to do, as as an alternative of discovering only one row, this time, the quantity is totally different:
After all, you’ll not discover any distinction in efficiency between these two eventualities, because the distinction is just a few milliseconds.
However that is only the start; we’re simply warming up our DAX engine. In each of those instances, as you may even see, we see solely “crammed” values — that mixture of rows the place each of our necessities are glad — product secret’s 364 and solely these dates the place we had gross sales for this product — if you happen to look totally within the illustration above, dates will not be contiguous and a few are lacking, akin to January twelfth, January 14th to January twenty first and so forth.
It is because System Engine was sensible sufficient to get rid of the dates the place product 364 had no gross sales utilizing the NonEmpty filter, and that’s why the variety of data is 58: we’ve got 58 distinct dates the place gross sales of product 364 weren’t clean:
Now, let’s say that enterprise customers additionally need to see these dates in-between, the place product 364 hadn’t made any gross sales. So, the concept is to show 0$ quantity for all these dates. As already described within the earlier article, there are a number of alternative ways to interchange the BLANKs with zeroes, and I’ll use the COALESCE() perform:
Gross sales Amt 364 Merchandise with 0 = COALESCE([Sales Amt 364 Products],0)Mainly, the COALESCE perform will examine all of the arguments supplied (in my case, there is just one argument) and change the primary BLANK worth with the worth you specified. Merely stated, it’ll examine if the worth of the Gross sales Amt 364 Merchandise is BLANK. If not, it’ll show the calculated worth; in any other case, it’ll change BLANK with 0.
Wait, what?! Why am I seeing all of the merchandise, after I filtered every little thing out, besides product 364? Not to mention that, my desk now took greater than 2 seconds to render! Let’s examine what occurred within the background.
As an alternative of producing one single question, now we’ve got 3 of them. The primary one is precisely the identical as within the earlier case (58 rows). Nonetheless, the remaining queries goal the Product and Dates tables, pulling all of the rows from each tables (The product desk accommodates 2517 rows, whereas the Dates desk has 1826). Not simply that, check out the question plan:
4.6 million data?! Why on Earth does it occur?! Let me do the mathematics for you: 2.517 * 1.826 = 4.596.042…So, right here we had a full cross-join between Product and Dates tables, forcing each single tuple (mixture of date-product) to be checked! That occurred as a result of we pressured the engine to return 0 for each single tuple that may in any other case return clean (and consequentially be excluded from scanning)!
This can be a simplistic overview of what occurred:
Consider it or not, there may be a chic resolution to point out clean values out-of-the-box (however, not with 0 as an alternative of BLANK). You may simply merely click on on the Date subject and select to Present gadgets with no information:
This can show the clean cells too, however with out performing a full cross-join between the Product and Dates tables:
We will now see all of the cells (even blanks) and this question took half the time of the earlier one! Let’s examine the question plan generated by the System Engine:
Not all eventualities are catastrophic!
Fact to be stated, we might’ve rewritten our measure to exclude some undesirable data, however it might nonetheless not be an optimum method for the engine to get rid of empty data.
Moreover, there are specific eventualities during which changing BLANKs with zero is not going to trigger a big efficiency lower.
Let’s study the next scenario: we’re displaying information in regards to the complete gross sales quantity for each single model. And I’ll add my gross sales quantity measure for product 364:
As you would possibly count on, that was fairly quick. However, what’s going to occur after I add my measure that replaces BLANKs with 0, which prompted havoc within the earlier state of affairs:
Hm, seems like we didn’t need to pay any penalty by way of efficiency. Let’s examine the question plan for this DAX question:
Conclusion
As Jeffrey Wang steered, it’s best to steer clear of changing blanks with zeroes (or with some other specific values), as this can considerably have an effect on the question optimizer’s means to get rid of pointless information scanning. Nonetheless, if for any cause you should substitute a clean with some significant worth, watch out when and the way to do it.
As normal, it is dependent upon many various elements — for columns with low cardinality, or if you’re not displaying information from a number of totally different tables (like in our instance, once we wanted to mix information from Product and Dates tables), or visible sorts that don’t must show numerous distinct values (i.e. card visible) — you will get away with out paying the efficiency value. Alternatively, if you happen to use tables/matrices/bar charts that present quite a lot of distinct values, be sure that to examine the metrics and question plans earlier than you deploy that report back to a manufacturing atmosphere.
Thanks for studying!
