MS SQL Server OLAP - Sum measures from only unique descendant names

Author

Sum measures from only unique descendant names

Karl-k

2005-09-26, 8:24 pm

I am trying to write a calculated measure formula that adds up the
total spent over the course of a year by customers in various
categories. If a customer purchased anything at all in that category,
their total spent for the year across all categories is included in
that category. So the same customer's total spent will show up in many
different categories. The Product Category dimension is 4 levels deep,
with the bottom level being the customer number. The idea is to
determine the "Impact" of the product category, where the impact is
"Customers who purchased items in this category spent this much overall
in all categories". The problem is that as you move up in the
dimension hierarchy, you might have a customer who purchased items in
more than one of that level's child members, and you only should count
that customers "Impact" once or it overstates the impact of the
category. And so forth, at the top level, the All Level should only
count each customers impact one time.

A traditional measure rolls up by just adding together everything from
the lower levels without regard to whether or not a unique customer
number has already been counted.

I found a similar formula that seems like it should work for me. It
should eliminate the duplicate customers from a set of Descendants of
the current product category. When I write this formula with the
Product Category level hard coded, it works fine. However, when I code
it the way I need it to work in the cube, where it does the calculation
at every member and level of the Product Categories dimension, it is
counting the duplicates again.

Here is a sample of what the dimension contains, along with the
"Impact" measure and how it ** should ** roll up. (Customer 1 is only
counted once at the Home Video level)

ALL Product Categories
Electronics
- Home Video: $3,300
----LCD: $2,300
------Customer 1: $1,000
------Customer 4: $500
------Customer 6: $800
----DLP: $2,000
------Customer 1: $1,000
------Customer 8: $700
------Customer 9: $300
- Home Audio
Furniture
Housewares

In the example above, the impact of LCD includes Customer 1's Impact,
and the same with DLP, but Home Video Impact should only count Customer
1's Impact once.

Here is an example of my formula with the level member hard-code: The
Answer comes out to $3,300 in my case:

Sum(Generate(Descend
ants([Product Categories].[All Product
Categories].[Electronics].[Home Video],,LEAVES) AS GenIterator,
Head( Filter( Descendants([Product Categories].[All Product
Categories].[Electronics].[Home Video],,LEAVES) as FilterIterator,
GenIterator.Current.item(0).Name = FilterIterator.Current.item(0).Name
), 1 ) ), [Measures].[Impact])

Here is what it looks like the way I need it to work across the entire
dimension. However, at the Home Video level (and all other levels) it
is incorrectly coming up with $4,300 as a total.

Sum(Generate(Descend
ants([Product Categories. CurrentMember,,LEAVE
S) AS
GenIterator,
Head( Filter( Descendants([Product Categories. CurrentMember,,LEAVE
S) as
FilterIterator,
GenIterator.Current.item(0).Name = FilterIterator.Current.item(0).Name
), 1 ) ), [Measures].[Impact])

What am I not understanding????

Darren Gosbell

2005-09-27, 3:24 am

You are very close.

> What am I not understanding????

What you are missing is the fact that .CurrentMember is context
sensitive. In the Generate function it is the current member from the
query. In the Filter function it is the current member from the Generate
() context.

The only way I could see to get around this was to 'trick' the MDX by
using a single member 'on the fly' set (which I could only do in a set
expression so I just unioned the member with itself). I then referenced
the first (and only) item in this set.

There is more information on this at the BI Best Practises blog
http://blogs.msdn.com/bi_systems/articles/162850.aspx

So I think your expression should look something like the one below
====================
=====

Sum(Generate(Descend
ants(UNION({[Product Categories.CurrentMember},
{[Product Categories.CurrentMember} as CURRENT_PRODUCT_SET)
/item(0).Item
(0),,LEAVES) AS
GenIterator,
Head( Filter( Descendants(CURRENT_
PRODUCT_SET.Item(0).Item(0),,LEAVES)
as
FilterIterator,
GenIterator.Current.item(0).Name = FilterIterator.Current.item(0).Name
), 1 ) ), [Measures].[Impact])

Below is the foodmart query I used to test this concept.
====================
======

With

MEMBER Measures.x as 'Sum(Generate(Descen
dants(UNION
({Product.CurrentMember},{Product.CurrentMember} as CURRENTMBRSET).item
(0).item(0) ,[Product].[Brand Name]) AS GenIterator , Head( Filter(
descendants(CURRENTM
BRSET.item(0).item(0),product.[Brand Name]) as
FiltIterator,
GenIterator.Current.item(0).Name = FiltIterator.Current.item(0).Name
), 1 ) ), [Measures].[Unit Sales])'

SELECT
{measures.[Unit Sales],measures.x} ON COLUMNS,
descendants([Product].[All Products],product.[brand
name],SELF_AND_BEFOR
E) ON ROWS
FROM sales

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Karl-k

2005-09-27, 1:23 pm

Darren,

Thank you very much! Your suggested solution works.

Now my only problem is performance. I expected this would be the case,
but was hoping it wouldn't be a show-stopper. It is borderline at this
point. If you have any suggestions, or links to a different approach
to solving a similar problem I would greatly appreciate it.

-Karl

Darren Gosbell

2005-09-27, 8:24 pm

Karl,

I did think that performance was going to be an issue, depending on the
size of your dimensions.

The only other thought I had when I was looking at your problem was that
it may have been easier if customers were in their own dimension, rather
than leaves of the product dimension. I think this would make things
easier for the impact calculation as you would already have the
customers total spend aggregated.

If you did not want to restructure the cube (which I understand could be
painful especially if there are other cubes using the same dimensions)

You could create a cube specially for the impact analysis and use a
virtual cube or the LookupCube function.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links