Hi...

Please help, How can I use StDev function in cube ?
Here is my case : I have 100,000 total records. I need to compute StDev for
sales by State. I have measure called "SALES" and that is sum of all 100,000
records sales.

When I use StDev({[State].CurrentMember}, [Measures].[Sales]) . I am getting
error message. What I understand is StDev can not be used with aggregated
numbers, like in my case sum of sales. Is there any other way to use Stdev
function to solved my problem?

Thanks
-Kris

Does this previous post apply in your scenario?

http://groups-beta.google.com/group/...rver.olap/msg/
3938dbe165a016d5

Newsgroups: microsoft.public.sqlserver.olap

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/...lserver.olap/m...

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

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!

BEWARE!!!

If ((x2 - ((x^2)/cnt))/(cnt))=0, you get overflow when you take the
square root (!?!).

For MS OLAP, 0^0.5 -> overflow, not 0.

Add a couple of iifs to stear clear of divide by zero overflows and
square root of zero overflows, as follows:

iif(cnt>0, iif(((x2 - ((x^2)/cnt))/(cnt))>0, ((x2 -
((x^2)/cnt))/(cnt))^0.5, NULL), NULL)





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