With Member Measures.LastHits as

iif(isempty(Measures.Hits),

([Date].[Year Month Day].prevmember,

Measures.LastHits

),Measures.Hits)

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:

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

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

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.

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 :)

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?

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.

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?

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.

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.

?

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

Post a Comment