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.
