- Ciao, friends. In this video, I want to show you how to optimize a DAX expression that uses two nested iterators. Using nested iterators is always dangerous because nested iterations iterate for the multiplication of the cardinality of the base table that you use and numbers grow up very quickly. So a very good practice is that of reducing as
much as possible the size of the tables that you
need to iterate on top of. But let's see that with an example because it's much easier to
understand it looking at code than with me talking. So let's go straight, look at the demo. I am using our usual Contoso model but as you can see here, I have a product discount column for each product, so each
product has its own discount and the same is happening
for the customer. Each customer has a specific discount. Now, given these two numbers, now I want to compute the sales amount, applying both discounts at the same time and slice it by year. I already have a report that shows the sales sliced by year. I want to add the discounted sales, applying both discounts. I can compute this value
in a lot of different ways but we will author it
using two nested iterators. We'll iterate first on customers and then on product so that we have a row context
with both columns available and then we perform our multiplication. Let's start by writing the code. So we start with a new measure. Let's call it discounted sales. And we iterate over customer. Then we iterate over product. We store in a variable
the product discount, which is the product discount. In another variable, the customer discount and I need two iterations in order to have access to this column. Let's write customer customer discount. Then we need the sales amount and here I just call sales amount and finally, we can go for the RETURN where we take the sales amount and multiply it by one minus the product discount. Product discount. And again, we multiply it by one minus the customer discount. Customer discount. And that's it. This code computes the discounted sales, so the sales after we have
applied both discounts at the same time. We only need to format
it as a decimal number. Reduce its size a bit and then we put that in the report. It takes not too long and it computes the values. So we have 1.1, 997 and so on. So the measure by itself works but as you have seen, it's not really super fast. You don't have the feeling
that it runs very quickly because it took one,
two seconds to appear. And we can measure it. We can simply open View, look at the performance
monitor, performance analyzer. We start recording, refresh the visual and the query runs in around two seconds. Let's zoom in a bit so you read it. It's around 2.2, no, yeah,
a bit less than two seconds, the time needed to execute the DAX query. So it is slow. We need to understand why it is slow. In order to understand the speed, we will first try to
understand what the engine does and what we ask the engine to do. Then we will write different
versions of the code until we have good performance. So let's start by doing
an analysis of the code. We can get rid of the performance analyzer and just look at the code. We are asking to do one
iteration over customer here and another iteration over product. Now, how many customers do we have? That I don't know, I need to check it. If we look at the customer table, we have around 18,000 customers. So these are 18,000 customers and regarding the products, again I don't know how
many products we do have. So let me check it. We have 2,500 products. So potentially, we have an outer iteration that runs 18,000 times and an inner iteration
running 2,500 times. And the question is how many times are we computing the inner calculation? That is the multiplication of the two because for each product, it
will iterate every customer and then it will repeat the
process again and again. So it's not hard to do some simple math. We just open the calculator and we have 18,000 times 2,500, that is 45 million times. So this inner iteration, this piece of code from line six to 10, potentially runs 40 million times. And for 40 million times, we are grabbing the values of two columns and worse, we are performing
context transition in order to compute the sales amount. That is the reason why
this code is so slow. We need to rewrite it in a different way and try to optimize it. So let's do that together. But instead of working with... Instead of working with Power BI, we move to DAX Studio because we now want to
analyze performance. So let me just save it
and open DAX Studio. Open in the wrong window. We need a query. So we just write something like EVALUATE SUMMARIZECOLUMNS by date calendar year. And then we want amount, which is our discounted sales. Let's put it here. And while we are here, we also add the definition of discounted sales, so we have all the code available in the same window. Let's get rid of that, reduce the font. No, we need that. Okay, we open the server timings. We have our query and we start measuring performance. We already know that will
run in around two seconds. So we can look at the server timings and start reasoning on top of it. 1.6 seconds is the time that it takes. A lot of formula engine,
a bit of storage engine. And there are three
storage engine queries. Let's take a look at the
storage engine queries because you see 2,500, 18,000. These are the two queries that retrieve the list of products and the list of customers. There is nothing to look at. But then we have a query that
materialize 1.8 million rows and if we look at the code of this query, we need to reduce the font a bit, it retrieves quantity times net price and the product key, the calendar year and the customer key. It's retrieving all the combinations of product key, customer key and year, which are the columns that are needed in order to resolve the query, computing the sales
amount for all of them. So it actually did not
iterate 45 million times. It iterated 1.8 million times because it only used
the distinct combination of product key and customer key. That's exactly what we asked for. We ask it to iterate by
product, by customer. Then we added the year in the query and we ask to compute the sales amount and multiply it by a discount. The thing is the query
is definitely too slow. We need to find a way to avoid
calling context transition, this context transition so many times. A first option might be that of removing entirely
the context transition. Why iterating over customer and product when we can iterate over sales? Because from sales, using RELATED, we can grab the product discount and the customer discount using RELATED to follow the relationship. Therefore, one single iteration over sales will solve the problem. Sales, in this model, has
around 12 million rows but iterating over 12 million rows to perform some simple
calculation is not a big deal. Let's give that a try. I can change this code. Well, actually I can do
that in another window. So we still have all the
values available here. We enable server timings and in the meantime, we rewrite this code. Let's rewrite discounted sales. Instead of iterating over customer, we iterate over sales. We get rid of this second iteration. Now, product discount, I can no longer use
product product discount, I need to use RELATED and same here, RELATED. And get rid of that. Too many. I also now need to get
rid of context transition. And the way I can call context transition during an iteration over the fact table but because I'm iterating
over the fact table, I can just write sales quantity times sales net price because now I do have a row
context on the sales table. And then the RETURN part is the same. I compute my SUMMARIZECOLUMNS. Server timings is enabled, let's run it. And it is way, way better. First of all, 58 milliseconds
compared to 2,000 milliseconds that was 1,500 milliseconds, that was the value earlier. Much better, we have nearly
no formula engine used here and there is only one storage engine query that is not easy to read but it's basically computing the expression quantity times net price, does some rounding and
it iterates over sales, following a join with product, with date and with customer. So now the query is entirely executed inside the storage engine,
which is way better and that's the reason why
this code works way better. The thing is, removing
the double iteration and replacing it with an
iteration over the fact table actually required me to
rewrite the entire calculation because if we look at the code, now I'm no longer using sales amount. I had to rewrite the
expression of sales amount inside the iteration, which is not the best option because sales amount is
just a simple multiplication but that measure can be much more complex. We need to find a way
to optimize this code without rewriting entirely the code. This is not always possible but in this specific case, it is. Let's go back to the original query. The problem is I'm iterating 18,000 rows and 2,500 rows, so a lot of rows. But do I really need to iterate
over customer and product? The answer is no, I don't. Because the only column that I need is the product discount
from the product table and from customer, I only
need the customer discount from the customer table. All the customers with the same discount, for the purpose of this
calculation, they are the same. I can group all of them together and the same for the product. So instead of iterating over customer, let's do that on a third window. Let's get rid of this and enable server timings also here. So instead of iterating over customer, I can iterate over VALUES of customer customer discount, that's
the only column that I need from the customer table. And in a similar way, I
iterate over the VALUES of product product discount. Let's use single quotes just in case. The remaining part of
the code is the same. I have a row context that lets me access product discount, another row context for
the customer discount. I can compute sales amount. The thing is, sales amount is no longer computed 45 million times. It's computed for a much
smaller number of times. And most important, I'm also providing to the engine a very
important information. I'm telling the engine
that the cardinality of this calculation is
neither the customer, nor the product. It is the customer discount
and the product discount. So I'm providing more information. I'm telling the engine from which column my
calculation depends from. And this might turn on
further optimization. Let's run the query. It doesn't work because I'm missing a
comma here hopefully. Okay, now it run. I have my result. And it's even faster than before. Now it's 21 milliseconds. A single storage engine query and the storage engine query does nearly the same calculation. It's a bit simpler than earlier because it computes
quantity times net price, retrieving... this is named column, it's actually the customer discount the product discount,
the customer discount, following the two joins. So by reducing the cardinality
of the calculation, we improved the speed further. Whenever you have multiple iterations, it's always good to reduce their number as much as you can. Or try to reduce the entire calculation to only one single iteration. We can do that here because here, I'm iterating over customer discount and product discount. How can I make this code
with one iteration only? Well, I can actually SUMMARIZE sales by customer discount and by product discount. Again, I don't need to change
the inner part of the code because now this
SUMMARIZE part is grouping by customer discount and product discount and then I'm iterating and doing the math. So instead of having two iterations, I now have only one
iteration on two columns. Only the columns that I
need for my calculation. Let's give this version a try. And it is again 25 milliseconds. There is actually no difference between 21 and 25 milliseconds. So what we have seen are two things. First, reduce the granularity
of your iterations and try to use one
iteration instead of two. As you have seen... paying attention to
simple details like these greatly improves the
speed of your calculation. What comes in the mind as the first solution
using multiple iterators is by far not the best one. If you are interested in
learning more, go to sqlbi.com. There we have an article that describes the
technique in further detail and you can also download the file to be able to play with it. Besides, if you like optimizations, I personally love them, we have a full training about optimization that is definitely worth looking at. Enjoy DAX.
