SSAS How to calculate Standard Deviation

Question

I'm trying to calculate a variety of statistical calculation across a range of data such as Standard Deviation and Average.

The data comes from many different "Data sources". These are are held in a Dimension.

I tried using STDDEVP function as shown in this example which operates on a measure.

But the results were wrong. I assume it's wrong because it's working on totals rather than raw data. [Sum of Values] is a standard aggregate measure summing the data in the fact table.

Following the link supplied by Greg I am now using this:

CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation]
 AS  (([Measures].[Sum of Values]^2 - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values])/[Measures].[Count of Values])^0.5),
FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact TS Data'; 

Which gives me this:

Which still isn't correct.

Using SQL If I calculate STDDEV for Air Temp with the following query:

  SELECT stdevp([Value])
  FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries]
  where [DETL_DataSourceSurrogateKey] = 78

I get: 6.13770334742149

How do to get the MDX query to calculate a STDDEV per Datasource in my Dimension?

I've also tried using a Measure with "No aggregation" but it didn't return anything.

Answer 1

The StDev function does operate at a rollup grain per the first parameter you pass in. If you always want to calculate the StDev at the lowest level then you can write some mathematically equivalent measures and avoid the StDev MDX function. This approach is described here. Let me know if that doesn't make sense. The MDX would be:

(([Measures].[Sum of Square] - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values]))/[Measures].[Count of Values])^0.5

See Peter's answer for a full explanation of the steps he took.

Also, if you do stick with the MDX approach you are currently using you will want to fix the first parameter as follows. Your code is factoring the grand total into the calculation. This MDX does not:

STDDEVP( {[Data Source].[Data Source Name].[Data Source Name].MEMBERS},[Measures].[Total])

Answer 2

To get this to work you need:

1. To add a column in your DMV to produce the square of the raw value.

   SELECT [Value], square([Value]) as ValueSquared
   FROM factTable 
   

2. A measure which sums the raw values. [Sum of Values]

3. A measure which sums the square of the raw values. [Sum of Square]

4. A count measure [Count of Values]

5. The Calculated measure syntax is:

   CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation]
   AS (([Measures].[Sum of Square] - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values]))/[Measures].[Count of Values])^0.5,
   FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 ,
   ASSOCIATED_MEASURE_GROUP = 'Fact TS Data';
   

Answer 3

If you want to use it on Raw data (that means, on the entire SQL table) it should run on the SQL level, no need to aggregate for that. So the standard deviation on code 1 works, so I would suggest: In you DMV add a column which is calculated for the standard deviation for the entire column over the [data source name].