Monday, March 29, 2010

MDX - Aggregating by member_caption

Here is an interesting MDX puzzle. Try aggregating a dimension hierarchy by member captions, where the members have unique keys, either by different hierarchies or just different keys. When you try to sum() a set, you have a tendancy of losing the position of the member on the axis.

Here is a solution to the puzzle using recursive MDX. If the preceding member has the same name, it moves to the preceding member and adds the measure. I have used the client geography dimension in my Weblogs cube. There are many city names that exist in multiple states and/or multiple countries. See 'Springfield' appears in 7 states. The challenge is to get the sum of all distinct city names. I know this is not a likely query, but if it was a customer name that was in many states, it might be a real query.

member as [Client Host].[Client Geography].member_caption
set MyCities as Order(nonempty([Client Host].[Client Geography].City,Measures.Hits)
,[Client Host].[Client Geography].member_caption,BASC)
member measures.r as rank([Client Host].[Client Geography],MyCities)
member measures.CityTot as iif((MyCities.item(Measures.r-2),[Client Host].[Client Geography].member_caption,
member Measures.CityTotal as
iif((MyCities.item(Measures.r),[Client Host].[Client Geography].member_caption,
select {Measures.CityTotal} on 0,
non empty
MyCities on 1
from EasternMining

You can slice and dice the real data on

No comments: