Monday, July 26, 2010

Getting the last non empty value

LastNonEmpty is an aggregation function available in the Enterprise version of SQL Server. However, you can create your own with a little bit of recursive MDX. Essentially, you simply create a calculated member that returns the non empty value, or if empty, it looks in the previous member. It really is very simple, and is an elegant way of writing a last non empty query. To create a LastNonEmpty calculate measure, simple use MDX such as the following

With Member Measures.LastHits as
iif(isempty(Measures.Hits),
([Date].[Year Month Day].prevmember,
Measures.LastHits
),Measures.Hits)

Notice how the calculated member is called LastHits, and the calculation actually refers to LastHits. This is the recursion. It keeps referring back to itself until it gets a day with a non empty value. If there are no non empty values, the recursion will automatically stop at the first day in your dimension and return null.

Naturally, you can add other calculations on top of this recursive calculation. For example, you might want to sum up the month to date figures using this non empty calculation. No problem, here is a MTD calculation using the last non empty calculation above.

With Member Measures.LastHits as
iif(isempty(Measures.Hits),
([Date].[Year Month Day].prevmember,
Measures.LastHits
),Measures.Hits)
Member Measures.LastHitsMTD as
sum(PeriodsToDate([Date].[Year Month Day].[Month],[Date].[Year Month Day]),Measures.LastHits)
select
{Measures.Hits,Measures.LastHits,Measures.LastHitsMTD}
on Columns,
tail([Date].[Year Month Day].[Day],31)
on rows
from
EasternMining


For examples of real-time OLAP queries on continually updating databases see http://RichardLees.com.au/Sites/Demonstrations


10 comments:

Boyan Penev said...

Clever :) I just wonder how Microsoft would feel about this Enterprise workaround :)

Jason Thomas said...

good workaround... but I was just wondering about the performance part. Wouldn't the performance take a hit because of this recursive nature?

Richard Lees said...

Hi Boyan,
I don't think Microsoft will worry about this "workaround". There are many good reasons to use Enterprise, including LastNonEmpty.

Jason, there is a cost to the recursive query, but if you have to get the last non empty value, there will be a cost any way you do it. Having said that, I know some people physically load the data for every day, so that you don't have to ask for the last non empty. It really depends on your data and query profiles, which is best.

Jason Thomas said...

Last question, which will work faster - the LastNonEmpty in Enterprise or this workaround? or does both of them give the same performance?

But have to say, it's a good workaround for people who cant upgrade to Enterprise edition :)

Bitter Coffee said...

I used your this method for a calculation I needed to do. it works great, HOWVER on my Totals and subtotals, I am not getting the correct values. it appears to be applying the calculation on the subtotal and total rows rather than summing up the details. anyway to force a summation of the underlying values rather than applying the calcuation on the total and subtotal rows?

Richard Lees said...

Bitter, it you have different ways of calculating at different points in the cube, then you simply need to add that to your calculation. Your calculation knows where it is in the cube, so you can ask it to do different things.

Giorgio said...

Hi! Great solution! It was a long time i was seraching for it.
Only one problem. If i select a hierarchy, I have the right result of my measure in subtotal, but not in the grand total. Why?

Richard Lees said...

This is because the calculation will find the last nonempty value for the higher level, which will be when any of the lower levels have a value.
If you need to support higher levels, you will need to enhance the calculation to aggregate the descendants. ie, put a SUM(DESCENDANTS(MyDim.MyHier,MyDim.MyHier.LowLevel),Measures.NonEmptyCalc).

I should warn you that this will add to the query duration. So you should check out performance. As I said above, this functionality is available with SQL Enterprise Edition and it performs much better.

Михаил Богданов said...

Why don't use anything like this:
TOPCOUNT(nonempty ([Date].[Year Month Day].Members, Measures.Hits), 1).Item(0)
I think it'll be faster.
?

Rashmin said...

I want customer list which has value for last nonempty date in month.
Example.
Customer id is 1,2,3,4
Month : March
For customer 1,2,3 has value on 30-march.
For customer 4 has not value on 30-march but has value on 20-march.

I want customer 1,2,3 as result.

thanks in advance..