question on using median to aggregate a measure

Hey all,

I'm still learning SSAS + MDX and am not sure how to go about using the MEDIAN function (instead of sum or avg) to aggregate my measure.

In my dataset, I have Geography, Time, and 'Use Type' dimensions that splice a Sale Value measure. I'd like to construct a measure that uses the Median instead of the traditional avg or sum aggregations.

I read the MSDN page on the median function (http://msdn2.microsoft.com/en-us/ms145570.aspx), but I'm still lost on what to do in the SSAS designer to add this measure. Any suggestions?

Thanks!

I'm not aware of a native "median" aggregation in SSAS - you could use the Median() function at the measure group fact dimension granularity, as in this Adventure Works example; but performance may be poor:

>>

With Member [Measures].[Median Sales Amount] as

Median(Exists([Sales Summary Order Details].[Sales Orders].[Order Line],,

"Sales Summary"), [Measures].[Sales Amount]),

NON_EMPTY_BEHAVIOR = [Measures].[Sales Amount],

FORMAT_STRING = "Currency"

select {[Measures].[Order Quantity], [Measures].[Average Sales Amount],

[Measures].[Median Sales Amount]} on 0,

Non Empty [Product].[Category].Members on 1

from [Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2002]

--

Order Quantity Average Sales Amount Median Sales Amount
All Products 60,918 $8,308.44 $809.33
Accessories 5,207 $260.49 $60.56
Bikes 24,908 $7,509.60 $2,181.56
Clothing 16,927 $754.02 $91.18
Components 13,876 $5,142.58 $418.51

>>

|||Hi Deepak,

Yeah - I figured out how to use the Median function when creating arbitrary MDX expressions, but why can't you create preprocessed cubes using an arbitrary aggregation method? When I splice and dice my cube, I wanted the numbers to be the pre-processed Median at the level I was at, instead of the sum or average. Is this really not possible?|||Does anyone know if there's a way to create a custom aggregator? In this case, using Median instead of SUM/Avg?|||This is an interesting idea, but not possible in Analysis Services 2005.|||Hmm - actually, it sounds like I might be able to get what I want via MDX Scripting. Does anyone know if this is possible? Essentialy, I want a measure where every cell is computed as the median of whatever dimensional intersection is specified.