I am working on a cube to return median lab test results. I am getting no
where. I can locate virtually no samples. I am trying to show the min,
max, average and median value of a lab test for a particular admitting
service. I have a date dimension, an admitting service dimension, and an
ordering provider dimension and min, max and result sum measures. This
should be simple but no combination of dimension set or measures product any
accurate median result numbers. The returned median number is always far
greater than any min or max value for any dimension. Does anyone have any
tips or clues in using the median function?



TIA -- Ron

http://groups.google.com/group/micro...olap/msg/bdc8a
43a6ad27a42?hl=en&
From: Deepak Puri
Date: Fri, 10 Jun 2005 19:10:33 -0700

Subject: Re: AS2K Median function

One issue with computing Median in a cube is whether the cube has the
desired dimensional granularity. In your case, if you want to compute
the Median over fact table records, then there should be a dimension
with fact-record granularity at leaf level (like [Records].Id]), over
which Median() can be computed for the desired data slice (like for a
city). This previous post explains in more detail:

http://groups-beta.google.com/group/...lserver.olap/m...

Newsgroups: microsoft.public.sqlserver.olap
From: "Sean Boon [MS]" <seanb... (AT) online (DOT) microsoft.com>
Date: Tue, 9 Dec 2003 08:39:36 -0800

Subject: Re: MEDIAN and QUARTILE Excel functions


The problem may not be the Excel functions themselves. I'm not sure what
you
mean when you say that it only sums the numbers.


My guess is that the real issue is that you are expecting the MEDIAN and
QUARTILE functions to operate at the granularity of the fact table. In
other words, just as if you have a SUM, COUNT, MIN, MAX aggregates as
available aggregate types, you're expecting MEDIAN and QUARTILE Excel
functions to give you a Median aggregate. This is not possible since
those
functions operate at the granularity of the cube, which is often not the
same as the granularity of the fact table.


For exmaple, suppose you have a fact table that tracks each individual
sale
event at a cash register. You then decide to build a cube on top of
this
data, but more than likely you won't include a transaction dimension.
At
this point, the granularity of the cube is not the same as the
granularity
of the fact table. If you then need to determine the median transaction
value, you will not be able to do so. The only way to be able to use a
function like Median is to insure that the the granularity of the cube
matches the granularity of the data that you need to compute that value.


--
Sean


--
Sean Boon
SQL Server BI Product Unit

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***