outstanding post: I have a related question also regarding Time
dimension...I have 2 of them, one for Date and one for Time...not sure
if this is the best approach for this:
but the following was an outstanding question on SQLSERVER ANALYSIS
Forum.


Hi all,

I was wondering if there are any examples in MDX for calculating
stddev.

I know there is a function STDDEV that I can use but the scenario below
might not work.

Fact table is event based facts.

I have 1 date dimension (date portion only), 1 user dimension, 1
application dimension

there is also a Time dimension (24 records, hour only, 12 AM, 1 AM,
etc..)

1. given 2 date ranges (including time), calculate the STDDEV and
STDDEV ERR

the formula is:

STDDEV = Square Root ( (Summation of (X - Xavg)^2) / (N-1) )

STDDEV ERR = (Xmax - Xavg) / STDDEV

I was thinking of the following example:

for date range between Date1 and Date2

Count of Events Time

3 8 AM

2 9 AM

5 10 AM

6 11 AM

3 12 PM

8 1 PM

7 2 PM

5 3 PM

5 4 PM

3 5 PM

----------------------------------------

47 events / 10 time intervals = 4.7 Average

STDDEV = SQRT ( ((3 - 4.7)^2 + (2 - 4.7)^2 + (5 - 4.7)^2 + (6 - 4.7)
^2 + (3 - 4.7)^2 +

(8 - 4.7)^2 + (7 - 4.7)^2 + (5 - 4.7)^2 + (5 - 4.7)^2 + (3 - 4.7)^2 ) /
(10 - 1) )

= approximately 1.9

STDDEV ERR = (8 (which is the max) - 4.7 (which is the average) )
divides by STDDEV (which is 1.9)

ATTEMP SOLUTION:

1. I created a Count Measure

2. I created Max(TimeKey) Measure to be used as N in the STDDEV formula
above

3. I created a Sum Measure of the above Count Measure in #1

4. so Average = Sum in #3 divides by N in #2

so based on these and the formula

STDDEV = Square Root ( (Summation of (X - Xavg)^2) / (N-1) )

STDDEV ERR = (Xmax - Xavg) / STDDEV

QUESTIONS:

1. how do I get sum of (X - Xavg) ?

2. how do I get Xmax ?

3. is this the right way to do this? any resources would be helpful.

The idea is that the user wants to raise a red flag if the Counts of
events (fact records) is more than a certain STDDEV threshold.

thank you