I am trying to calculate the Standard Deviation for 200,000 Student scores
in a fact table.

When I create a measure using Cube Editor called 'MathScore' for the 200,000
Students, there is no option for a Stdev aggregate type.

Nor can I create a student dimension for the 200,000 student members. I get
the error that you cannot create a dimension with more than 64,000 members.
If I could do that, I would be able to use MDX and call the Stdev function
in my MDX query.

I don't want a dimension larger than 1000 members, but I have to get the
standard deviation of 200,000 scores. How can I do this type of aggregation
on a measure?

tx

http://groups-beta.google.com/group/...rver.olap/msg/
b20ac36a41463296?hl=en
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com>
Date: Thu, 24 Feb 2005 14:18:36 -0800

Subject: Re: StDev calculation Problem

Does this previous post apply in your scenario?

http://groups-beta.google.com/*group...serv*er.olap/m.
..

Deepak Puri Feb 21, 9:07 pm show options

Newsgroups: microsoft.public.sqlserver.ola*p

Date: Mon, 21 Feb 2005 21:07:03 -0800
Subject: Re: Calculate deviation

If the Standard Deviation is to be computed at the fact table row
granularity, then it's useful to add a base measure that sums the square
of the fact table value:


http://groups-beta.google.com/*group...serv*er.olap/m.
..

Avi Perez Sep 6 2004, 12:16 pm show options

Newsgroups: microsoft.public.sqlserver.ola*p
From: "Avi Perez" <avi.pe... (AT) irisbi (DOT) com>
Date: Mon, 6 Sep 2004 15:16:16 -0400

Subject: Re: MDX Stdev

this is a typical headache problem with OLAP - and one not really
addressed
well in AS2K

its the same issue with distinct count - because you really want to
evaluate
a formula based on the atomic level of your data - not an aggregated
level.
Even if you were to run a stdev in the database - you'll lose you
ability to
slice and dice it when you bring it into the cube - since a stdev
calculate
is not additive - and will produce different results depending on what
choices you make with the other dimensions.

The good news is that there is a solution - depending on your exact
setup.

the trick is to load up your base measures as follows:
load in a simple SUM measure (x)
load in a simple SUM measure of x squared (x2)
load in a counter called cnt.

Then using the formula for stdev, create a measure in the cube as
follows:

((x2 - ((x^2)/cnt))/cnt)^0.5

if you're using the sampled approach, you'll need to use

((x2 - ((x^2)/cnt))/(cnt-1))^0.5

best part is, it is dynamic, and will work as you twist the cube around.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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