- Ciao friends!
I already introduced LOOKUPVALUE in a previous video.
Now I want to go deeper and analyze
the performance of LOOKUPVALUE.
To do that, let's start taking a look at the data model.
We have the usual Contoso model with sales, date,
all sales are stored in US dollars.
And I want to be able to dynamically convert
them in different currencies.
Let's say we want euro.
That's why we have the daily exchange rate,
which provides the exchange rate day by day
in various currencies.
I can leverage LOOKUPVALUE to extract
the exchange rate of one specific day.
And that's exactly what I did with a calculated column
in sales that computes the daily exchange rate to euro.
I use LOOKUPVALUE.
So I first save the current date.
And then I use LOOKUPVALUE to get the current rate
where the currency code is EUR and the date
is the current date.
See I'm using the expanded table in order to place a filter
on the currency code knowing that this filter
will go to the daily exchange rate table.
This version works just fine using LOOKUPVALUE,
but you need to take into account that LOOKUPVALUE
is actually converted using a simpler CALCULATE.
This code is completely equivalent
to this version of the code,
which is longer and does not use LOOKUPVALUE.
Let's see what it does.
It takes the current date and then it uses CALCULATE.
It retrieves the SELECTEDVALUE of the daily exchange rate
providing a default value of zero.
And it does a FILTER over ALLNOBLANKROW currency code
and another FILTER over ALLNOBLANKROW
over the date,
applying the filter on the currency code and on the date.
Finally, when LOOKUPVALUE is translated into CALCULATE,
it always adds a REMOVEFILTERS on the table.
All these details are important.
The reason is when the engine translates
LOOKUPVALUE into CALCULATE,
it uses ALLNOBLANKROW and REMOVEFILTERS
in order to avoid circular dependencies,
in case you create multiple columns with LOOKUPVALUE
in the same table.
Beside it is also important to have SELECTEDVALUE
with the default value here.
This is important because the set of filters
needs to find only one row.
And if it does not find one row,
then it returns zero.
Why is this important?
Because performance-wise,
the presence of SELECTEDVALUE means that the engine needs
to check row by row that there is actually only one value
for the daily exchange rates rate.
Let's see that with a more complex query
that uses a very similar technique.
I have already prepared the query in DAX Studio.
What I'm doing here is again use LOOKUPVALUE.
This measure is computing the sales amount in EUR.
So it first summarizes sales by date
in order to go at the correct granularity.
And then for each day,
it retrieves the exchange rate where the date
is equal to the same date and the currency code
is equal to EUR by using LOOKUPVALUE.
What if it does not find any exchange rate?
Well in that case,
this CALCULATE computes the average exchange rate
to EUR over all time.
So if an exchange rate is not found,
then it will use the average over all time
as a default value.
I can run this query and as you're going to see
it takes a while.
it is scanning 12 million rows
and it executes in around 1.7 seconds,
no 2.2 seconds actually.
There are several details which are important here.
First of all the execution time is very high.
And if you look at the number of storage engine query
that gets generated is 932.
So it runs a lot of queries in order to return the result.
Why is that?
Well, if you look at all these queries,
they are all needed in order to guarantee
that there is actually only one value for the exchange rate
in that specific date to EUR.
The engine needs to do that because we are using
the default value of LOOKUPVALUE.
Now, if you are sure that your model either contains
one row or no rows at all,
so you will never find more than one row
for the exchange rate in a given date,
then a better way of altering the same code
is not to use the default value of LOOKUPVALUE
that requires checking for the unicity of the value,
but instead, leverage the COALESCE function.
This code is the same as before.
But you see I'm not using the third argument
of LOOKUPVALUE.
Instead I'm putting it inside COALESCE.
If I run this query,
it will not take 2.2 seconds, it is much faster,
it runs in 35 milliseconds.
And you see that I no longer have
all those storage engine queries.
The reason is, I have no need to guarantee
that there is only one value and exactly one value.
In case there are multiple values,
this code will break.
But if you can guarantee that you can obtain
much better performance,
avoiding the third argument ... not the third,
but the default argument of LOOKUPVALUE,
and instead leverage the COALESCE function.
There is more about LOOKUPVALUE
that is worth investigating.
A lot of times people use LOOKUPVALUE
as a replacement for a relationship.
Relationships are always much, much faster than LOOKUPVALUE.
And I want to demonstrate to you that.
Let's go back to the model for a second.
I have already a relationship between sales and customer.
That relationship is in place.
And what I want to compute is the average age
of my customers, at the time where they purchase from me.
A way of doing it is to use LOOKUPVALUE,
I can do an AVERAGEX over sales,
and then I use LOOKUPVALUE to retrieve the birth date
of the customer where the customer key is identical
to the sales customer key.
This code is suboptimal,
but I'm using LOOKUPVALUE in order to mimic
a relationship which is already in place,
but I'm not using it in this code.
And then I compute the difference in here between
the order date and the customer birthdate.
If I run this query,
you will see that it's not extremely slow,
but it takes slightly less than one second.
And there are other issues here,
look at this row, it is materializing
1.4 million rows.
Why's that?
Well, because I'm using LOOKUPVALUE inside
an iteration over sales and LOOKUPVALUE
is translated into CALCULATE.
Therefore, context transition is happening.
And context transition requires the materialization
of large amount of data, if it is happening
during an iteration over the sales table.
I can obtain the very same result
but instead of relying on LOOKUPVALUE,
I can just use the relationship.
This version of the code does an AVERAGEX over sales,
and it uses RELATED instead of LOOKUPVALUE.
RELATED uses the internal relationship
therefore it will not be translated into CALCULATE
and it will just go straight to the customer birthdate.
If I run this query I obtain the very same result.
But instead of taking 800 milliseconds
it just takes eight milliseconds.
And most important, you'll see
that there is no materialization at all.
The engine is able to, with a single storage engine query
to retrieve the result and produce the number that I want.
There are many more details which are interesting about
LOOKUPVALUE but they do not fit in a short video.
So if you are interested in these topics come to SQLBI.com,
read the full article.
There you can download the file,
play with it and learn more about LOOKUPVALUE.
Enjoy DAX.