I have a MDX problem that I have to solve (and I thought it would be simple)
but is proving to exceedingly difficult.

I need to create the standard deviation measure.

I have time dimension called 'Time Financial' with the values of '2003-2004'
and '2004-2005'. I have some geography dimensions too
Country->State->SD->SSD->PostCode->SLA. My fact is 'download speed' which is
the user's average download speed for that financial year (it measures
broadband speeds). I also have 'test count' which is the total tests a user
has performed.

I can readily create an average ('Avg Download') by doing a '[Measures].[Sum
of Download]/[Measures].[Count Of Users]'. I have verified that this works
by running SQL queries at a variety of geographical levels.

However, I also need to create a standard deviation. This is where the
wheels fall off.

I can create a calculated member like:

Stdev([Time Financial].[All Time Financial].children, [Measures].[Avg
Download])

But after some checking I released this is wrong since it is the standard
deviation of 'Avg Download' instead of the standard deviation of the
'Download'. I think it is effectively taking the standard deviation of the
2003-2004 average and the 2004-2005 average. Its standard deviation is
therefore very small.

I really want to go Stdev([Time Financial].[All Time Financial].children,
[Fact].[Download]) but this syntax is not allowed since the second parameter
needs to be a measure. Can I convert an array of [Fact].[Download] into an
measure?

Does anyone have any ideas? I really am an amateur at this MDX stuff. I have
searched the web with little luck. Two people have had similar problems with
no real solution.

http://groups.google.com/groups?hl=e...com%26rnum%3D1

http://groups.google.com/groups?hl=e...com%26rnum%3D2

In AS, in the 'aggregate function' there is Sum, Count, Min, Max, and
Distinct Count. Why isn't there also Average, Standard Deviation, etc ? Is
it in the next version.

thanks!

Dave

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.


"Dave A" <dave (AT) sigmasolutionsdonotspamme (DOT) com.au> wrote

Wow - thanks! I have not tried it yet but this is an awesome suggestion. I
would never have thought of it.

Dave

"Avi Perez" <avi.perez (AT) irisbi (DOT) com> wrote