I am designing a quality control cube where I need to compute the standard
dev from the fact table information. The only way I have found to do this
is to precompute X^2 (so that I can roll up the squares of the measurements)
then I use a calculated member stdev.

Is there a better way to perform this type of computation with AS? My main
concern is that I am required to double the columns (measurement then
measurement squared) in the fact table.

Thanks (in advance!)

Bill

Hello Bill,

I am not quite sure about how you want to calculate stdev. Will you
describe it in more details:

Assuming that you always wish to calculate the standard deviation of
measure1 for all the leaf nodes under a selected member of the
[dimension1] dimension:

Stdev(Descendants([demension1].CurrentMember,,LEAVES),
[Measures].[measure1])

Have a great day!

Thanks & Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
.phx.gbl

Thanks for the response! This is a tricky issue, takes a moment for me to
wrap my mind around the issue also.

My problem, (I am almost sure there isn't an AS solution now) is that I am
trying to calculate the standard deviation from the facts, not from the
leaves. In many data collection areas there are just too many fact records,
for example 100k/day.
I would prefer to aggregate these into useful blocks of 1 hour of
production/product/machine. But in doing so, I loose the ability to
compute the Stdev back down to the fact table. Once the data is SUMed with
the Aggregate function, the information is lost.

I could make each fact it's own leaf, as you suggest, but 100k leaves would
take some time to compute the stdev for just a single day of production.
(over a couple of months or a year this looks really ugly)

Looks like the best solution is still to pre-compute the squares of my
measures and then create a calculated member for the StDev using the rollup
sum of the measures and measures squared.

Bill



"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

Hello Bill,

Based on the current situation, I agree with you this might be the only
solution at present. :-)

Thanks & Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
phx.gbl

You've likely already thought of this: instead of storing pre-calculated X^2
values in your fact table, you can of course use a calculated column in your
database, so it won't take up any extra storage space, instead X^2 is
calculated on the fly by your database.

Cheers/K

Yes, once I figured out the pre-squaring, I changed my fact table over to a
view and added columns with squares, which seemed to work out very well.

Thanks for the suggestion.

Bill


"willie" <nexus42 (AT) btinternet (DOT) com> wrote

Hello Bill,

Glad to hear the issue is resolved! :-)

Thanks & Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
<eaHrPrr4EHA.1292 (AT) TK2MSFTNGP10 (DOT) phx.gbl>
<0o9BYbz4EHA.3152 (AT) cpmsftngxa10 (DOT) phx.gbl> <cq6j3o$nhn$1 (AT) sparta (DOT) btinternet.com>
phx.gbl