2

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:

enter image description here

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.

| improve this question | |
3
+200

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])
| improve this answer | |
3

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';
| improve this answer | |
1

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].

| improve this answer | |
  • Now that I understand this a bit better that would have been an awesome solution. But you can't use an OVER clause in the OLAP DMV. But this works in SQL SELECT [DETL_DataSourceSurrogateKey] , [Value], stdevp([Value]) OVER (Partition BY DETL_DataSourceSurrogateKey) as STDDEV FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries] – Sir Swears-a-lot Nov 9 '16 at 3:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.