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