Hi All,
I am working on client sight from a s/w company as a data
warehoue developer. I am facing a great challenge to calculate
Corrected mean in MDX in SQL server 2005. I am struggling since from 4
days. the calculation of corrected mean says that

(Mean - Standard deviation) < X < (Mean + Standard Deviation)

Where X is the series of value (i.e 122,133,300,23,50). I need to
calculate the mean of ' subset of X' which setisfy the above criteria.
I have calculated the mean and standard deviation. The problem is when
I calculate this above formula in MDX it dosent give me correct value.
because MDX calculate every formula on the dimension which we take at
run time. so when i take the the dimension with this formula , first it
does sum of 'X' on that dimension and then it compares (Mean -
Standard deviation) < X < (Mean + Standard Deviation) because the 'X'
created as measure as sum(X). I need to compare X 's individual values
with mean - standard deviation and Mean + standard deviation and
discard that value which doesnt come under this cirteria. Is there any
way In MDX by which i can do this. Please Reply me As soon as possible
I am struggling a lot for that. I need to give solution to client as
soon as possible.

Thanks and regards
Dhaval

Hi Dhaval,


What is the size of the fact table - presumably, each fact record
represents a sample of X - and does it have a unique key? It might be
possible, though slow, to compute this corrected mean by using a "fact"
dimension.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

1.531
MB

Time_Key int Primary Key
User_Key int Primary Key
Tracking_Code_Key int Primary Key
Local_Campaign_Key int Primary Key
Marketing_Agent_Key int Primary Key
Global_Campaign_Key int Primary Key
Channel_Key int Primary Key
Transaction_ID bigint
Deposit_Amount decimal(18, 4)
Deposit_Sqr decimal(18, 4)
Mean decimal(18, 4)
Stddev decimal(18, 4)
Assuming that Transaction_ID is a unique key for the fact table, how
many values (rows of fact table) are there?

One appraoch (depending on number of rows) would be to add a
[Transaction] dimension, with [Transaction_ID] as the leaf level. Using
the earlier calculated measures for Mean and StdDev, the corrected mean
could then be computed:

Not IsEmpty([Measures].[Deposit_Amount]) And
Abs([Measures].[Deposit_Amount]
- ([Measures].[MeanX], [Transaction].[All Transaction]))
< ([Measures].[SigX], [Transaction].[All Transaction])))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

I have used the quick formula way to compute the Std Dev that you have
posted in
http://groups.google.com/group/micro...960fc774d82 6

and that
[DIM Deposit] is the dimension made up of the members of the
Transaction_ID.


COUNT(FILTER([Deposit].[DIM Deposit].[DIM Deposit].Members,
ABS([Measures].[Deposit Amount - Transaction]-([Deposit Amount -
Mean],[Deposit].[DIM Deposit].[All]))
<= ([Deposit Amount - Standard Deviation],[Deposit].[DIM
Deposit].[All])))

seems to give the count of something, but it seems non-additive -
meaning, when slicing by any of the other dimension keys mentioned in
Dhaval's post (User_Key, Tracking_Code_Key, Local_Campaign_Key, etc),
it is showing some bigger numbers that looks like it is filtering the
whole [DIM Deposit] rather than the additional dimension.

How can we filter/slice by additional dimension?


Deepak Puri wrote:

My guess is that you need to exclude [DIM Deposit] members which are
empty for the given filter/slice:

Not IsEmpty(([Measures].[Deposit Amount - Transaction]))
And ABS([Measures].[Deposit Amount - Transaction]
-([Deposit Amount - Mean],[Deposit].[DIM Deposit].[All]))
<= ([Deposit Amount - Standard Deviation],
[Deposit].[DIM Deposit].[All])))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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