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.

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.

Question on learning SQL

Hi, I am new to this forum. I have a question about learning SQL. I never really had much of a reason to learn anything dealing with server programs. I now would like to learn about SQL. Is there a program that anyone knows about that can help me learn?sql server 2005 developer edition was 45 USD on amazon last I checked. Microsoft has a free e-learning class for it on their web site. Books online which ships with the product is an excellant resource. The 2 new INSIDE SQL SERVER 2005 books come out on June 4 I believe...|||This should teach you enough to be dangerous:
http://www.w3schools.com/sql/default.asp|||Thank you both. I appreciate your help.|||Books online are very complete. If you have a minimal knowledge in database, you can learn it yourself without any problem (I did it).

After, some books are also goods (like "Inside SLQ Server").|||After, some books are also goods (like "Inside SLQ Server").
Yea,I too want to know,whats inside "SLQ" ;)|||Wow ! My fingers were a little bit mad ! :rolleyes:|||Wow ! My fingers were a little bit mad ! :rolleyes:Yeah, I've never had that happen, but I read about it in this book once!

-PatP