Max as a semi-additive aggregation over time

I got sent this question recently, but when I tried to reply the senders email address bounced, so I am posting the question and answer here.

Q: "I have a problem with aggregate function in cube measure.

My Measure is customer count and this fact have time dimension and location dimension.

this measure transfered monthly.

When aggreagated with time customer count aggregate function is MAX and if aggregated with location dimension aggrefate function is SUM.

What is the solution for this problem because I think SSAS cube measure can only have one aggregate function ? "


A: A calculation like this is a little bit unusual, but I don't think it is impossible. It just might not be the fastest measure as we are "fighting" against the standard aggregations.

There are probably other ways of doing this, but the first thing that came to my mind would be to do this using two measures, one normal sum measure and one calculated measure.

1) create a standard count aggregate (you can set this to visible = false if you do not want the users to see it)

2) create a calculated measure something like the following

Max(EXISTING [Date].[Date].[Date].members, Measures.[Customer Count])

Where measures.[Customer Count] is the measure from step 1) and [Date].[Date].[Date] is the lowest level date attribute.

This calculation basically says to get the aggregated amounts for all the lowest level date members that exist in relation with the currently selected date member and find the maximum from there.

Print | posted on Thursday, October 25, 2007 5:47 PM