Hi,

is it possible to create a measure which shows the deviation of values?
So a measure has only the calculation types min, max, sum and count.

Best regards,
Thorsten Blawatt

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/...rver.olap/msg/
77e4ce2bffd7d72a

Newsgroups: microsoft.public.sqlserver.olap
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 ***
Don't just participate in USENET...get rewarded for it!