SSAS - Custom Total

Question

I'm trying to create a cube where the total is taken from an attribute on the fact and not calculated as an aggregate of the children in SSAS 2005. For example

Assume I have 10 stores which all had 100 customers visit on a particular day. There was however some overlap where some customer visited multiple stores. The total visitors for the region to which all the stores belong therefore is a manual figure provided in the fact data e.g.

Storename  Date  StoreTotal  RegionTotal  
Store 1  2008-01-01  10  95  
Store 2  2008-01-01  13  95  
Store 3  2008-01-01  11  95  

etc etc

How can I design the cube, using a calculated measure presumably, to return the attribute as the aggregate total instead of summing the measure from the children?

Looking forward to any ideas.

cheers

Answer 1

Er, if I understand correctly you are trying to return RegionTotal as is? How about setting "Aggregate Function" for the measure to "None".

Answer 2

I added an MDX statement in the cube to return the alternative measure at total level. The problem I had then was that it was returned for every row at leaf level and therefore the total was summing the RegionTotal where I only wanted the first entry. I ended up removing all but 1 region total per key combination which returns the correct result but is a really dirty workaround. I'll try to come back to this at some point unless someone far more proficient in MDX can come up with an alternative.

Answer 3

Couldn't you just create a distinct count measure based on the CustomerID? The cube would then calculate this for you and you would not have to do any sort of manual manipulation.