Hi there,

I am trying to use the median function in anaylsis services, but i don't
know how to define a set in my cube.

the syntax is
Median(«Set»[, «Numeric Expression»])

my fact table columns
id, name, city, age, etc...

dimensions
city, etc...

I would like to find the median age for all the records in each city.

Thanks for your help.

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/...rver.olap/msg/
88a14e95d2195740
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 ***

Deepak,

Thanks for your reply. I tried to create a city dimension table use column
from the fact table. City and Name as the members, then I use the
[city].[City] as the numeric value for Median function. It returns an error
message said "unknown dimension or member"...

Assuming city contains number 1 to 10 to identify different cities, so the
city dimension will have city as first level member, and name as second level
member. and i want to find out the median for the cities...

Thanks for your help.

Aaron



"Deepak Puri" wrote:

Hi Aaron,

As I mentioned, you will need another dimension besides[City], whose
leaf level enumerates all fact records for a given city. Assuming that
each fact record has a unique "ID", then you can create a dimension like
[Records], with a leaf level of [ID]; ie. one member per fact record (of
course, assuming that the fact table isn't huge). There should also be
an [Age] measure in the cube. Then, the Median Age for a selected ciry
willl be like:

[[City].CurrentMember}, 1), [Measures].[Age])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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