Stddev calculation error

Question

Hi, I use a cube that has the following amounts. My problem is that thw StdDev is not correctly calculated as you can see below:

Line Number                           Amt1             Amt2                                           Amt3                                                 Amt4

1                                            0             -236300000                         -136368730                         290372517

2                                            0             -236300000                         -136368730                         290519674

3                                            0             -236300000                         -136368730                         290713432

4                                            0             -236300000                         -136368730                         306752907

5                                            0             -236300000                         -136368730                         282871206

6                                            0             -236300000                         -136368730                         309795229

7                                            0             -236300000                         -136368730                         288779250

8                                            0             -236300000                         -136368730                         285043461

9                                            0             -236300000                         -136368730                         298609739

10                                          0             -236300000                         -136368730                         278132374

11                                          0             -236300000                         -136368730                         275214111

12                                           0             -236300000                         -136368730                         302671426

13                                          0             -236300000                         -136368730                         277358242

14                                          0             -236300000                         -136368730                         292122654

15                                          0             -236300000                         -136368730                         295822341

16                                          0             -236300000                         -136368730                         292903674

17                                          0             -236300000                         -136368730                         295959920

18                                          0             -236300000                         -136368730                         295789927

19                                          0             -236300000                         -136368730                         295845531

20                                          0             -236300000                         -136368730                         306927461

21                                          0             -236300000                         -136368730                         285775908

22                                          0             -236300000                         -136368730                         292614449

23                                          0             -236300000                         -136368730                         274461602

24                                          0             -236300000                         -136368730                         294659980

25                                          0             -236300000                         -136368730                         292131403

SSAS STD Deviation                     0             0                                      3.265986324                       9493840.692

                                                              

Excel- STD Deviation                     0             0                                   0                                            9493840.692

The column Amt3 is not correct. It contains all same amount but calculate a standard deviation that is different than 0.

Is there someting I am missing?

Thanks,

 

---

Ccote

Answer 1

Hi,

Are you sure that some formatting is not hiding the actual value? Try to calculate the average of Amt3 and see the result.

I've been able to construct a situation very similar to yours by assigning one of the 25 members a value of  -136368729.5 and the StDev() returns 3.2659863237109

Example on Adventure Works:

with

set p as Head([Product].[Product].[Product],25)

cell calculation [first] for '([Product].[Product].[Product].MEMBERS.Item(0), {[Measures].[Internet Sales Amount]})'
as -136368729.5

cell calculation [all] for '([Product].[Product].[Product], [Measures].[Internet Sales Amount])'
as -136368730


member x as [Measures].[Internet Sales Amount] 

member a as AVG(p, x)

member s as Stdev(p, x)

select
{x,a, s} on 0,
p on 1
from [Adventure Works]

Regards,

Hrvoje Piasevoli

Answer 2

Can you show the exact expression that you are using with the StdDev function to produce the value you show in your example? I use this function extensively in reports and do not have any problem with it. 

Answer 3

HI Jerry, thanks for your help. We currently do not have any problem when we use other fucntions like AVG, MIN , MAX or Median. The value returned is good. Only STDEV seems to have problems and only with some values: Amt2 and Amt4 are correct while Amt3 is not.

 

Thanks again

---

Ccote

Answer 4

Hi,

Can you confirm that AVG, MIN and MAX return the same values over the set in question with the problematic measure (remember to remove the formatting)?

Regards,

Hrvoje Piasevoli

Answer 5

HI Stacia, here is the expression we are using:STDDEV([Iteration].[No Iteration].&[0]:[Iteration].[No Iteration].&[24]). And we do not have problems with other functions like I said in this thread.

 

Thanks for your help!

 

---

Ccote

Answer 6

Hi Jerry, I can confirm that other functions are working fine. There is no formatting on our measures. The only thing that could matter is the datatype of the measures used. Since our base fact table uses decimal (24,8), the datatype used by AS is double. That may lead to precision issues.

But, we noticed something else. It seems that the STDEV function is limited by the number used. When I change the number to a smaler one or I use a smaller set of iteration, I get correct results. But, as soon as I increase iterations or number used in stdev function, I get incorect results. But, if I use much larger number like

5136368730

 

, it gives me the correct result.

Here is the query I am using:

with

member

test

 

(

 

 

},-136368730)

SELECT

 

NON

EMPTY

CrossJoin

(Hierarchize(AddCalculatedMembers({DrilldownLevel(Except([Periode Projection].[Priode projection].members,{[Periode Projection].[Priode projection].[All]}))})), {[Measures].[test]})

ON

COLUMNS ,

NON

EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel(Except([Iteration].[ID Iteration].members,{[Iteration].[ID Iteration].[All]}))}))

ON

ROWS

FROM

(SELECT ({[Periode Projection].[Priode projection].&[0]}) ON COLUMNS

FROM

[OLAP]) .

 

any other idea? Thanks for your help!

 

---

Ccote

{[Iteration].[ID Iteration].&[1],

[Iteration].[ID Iteration].&[2],

[Iteration].[ID Iteration].&[3],

[Iteration].[ID Iteration].&[4],

[Iteration].[ID Iteration].&[5],

[Iteration].[ID Iteration].&[6],

[Iteration].[ID Iteration].&[7],

[Iteration].[ID Iteration].&[8],

[Iteration].[ID Iteration].&[9],

[Iteration].[ID Iteration].&[10],

[Iteration].[ID Iteration].&[11],

[Iteration].[ID Iteration].&[12],

[Iteration].[ID Iteration].&[13],

[Iteration].[ID Iteration].&[14],

[Iteration].[ID Iteration].&[15],

[Iteration].[ID Iteration].&[16],

[Iteration].[ID Iteration].&[17],

[Iteration].[ID Iteration].&[18],

[Iteration].[ID Iteration].&[19],

[Iteration].[ID Iteration].&[20],

[Iteration].[ID Iteration].&[21],

[Iteration].[ID Iteration].&[22],

[Iteration].[ID Iteration].&[23],

[Iteration].[ID Iteration].&[24],

[Iteration].[ID Iteration].&[25]

/**/

as

STDEV

 

Answer 7

I can confirm that I'm getting the same results as you are with all amounts except Amount3. Datatype doesn't appear to be the issue as I tried different types. 

I get correct results if the set contains 1-11, 16, or 17 members, but an error with 12-15 members, and incorrect values (each different) with 18 or more members.

In the cases where we have used the StdDev function, we are operating on sets containing 2 or more members, up to thousands of members, but we would rarely have identical values across the entire set. If we did, it would be more likely to happen when the sets are small, which could explain why we've not seen this behavior before.

Answer 8

Hi ccote,

In the output pane, double click the data cell to open cells properties and check whether the Value and Formatted_value are identical.

thanks,
Jerry

Answer 9

Hi, I checked all 25 cells properties and they are identical except for de decimal separator which was a comma instead of a period. So I changed it in the regional settings, restarted SSMS and got same results.

E.g.
CellOrdinal              5
VALUE                     3.2659863237109
FORMATTED_VALUE   3.2659863237109
...
CellOrdinal 23
VALUE 3.2659863237109
FORMATTED_VALUE 3.2659863237109

As I said before in this thread, not all measures have this problems. And If I change the number with something else, it goes away or I get the following error:

with
member

test as
STDEV(
{[Iteration].[ID Iteration].members},836368730)
SELECT
NON EMPTY
CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel(Except([Periode Projection].[Priode projection].members,{[Periode Projection].[Priode projection].[All]}))})), {[Measures].[test]})
ON COLUMNS ,
NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel(Except([Iteration].[ID Iteration].members,{[Iteration].[ID Iteration].[All]}))}))
ON ROWS
FROM (SELECT ({[Periode Projection].[Priode projection].&[0]}) ON COLUMNS
FROM [OLAP])

If I change the number 136368730 to 736368730, I get the desired result:

CellOrdinal 1
VALUE 0
FORMATTED_VALUE 0

But, if I change the number to 636368730, I get this result:

CellOrdinal 2
VALUE     Not numeric 
FORMATTED_VALUE -1.#IND

I am wondering if there is something wrong in the way I use STDEV

Thanks for your help!

---

Ccote

Answer 10

Hi,

Would you please unpropose my post as answer as there is clearly a problem with the functions.

Here is an example that shows how the functions Stdev and Stdevp return 'strange' results for different constants as the size of the operating set changes (as Stacia has noticed). Works OK for constant 1.25, doesn't work for 1.2, 1.3, 1.4 etc. The third test is just to test that the results are the same as in excel.

with
member [Measures].[Constant 1] as 1.2
member [Measures].[Constant 2] as 1.25
member [Measures].[Increment by 1] as [Date].[Day of Year].CurrentMember.MEMBER_KEY

member [Measures].[Stdev 1]  as Stdev(null:[Date].[Day of Year].CurrentMember, [Measures].[Constant 1])
member [Measures].[StdevP 1] as Stdevp(null:[Date].[Day of Year].CurrentMember, [Measures].[Constant 1])

member [Measures].[Stdev 2]  as Stdev(null:[Date].[Day of Year].CurrentMember, [Measures].[Constant 2])
member [Measures].[StdevP 2] as Stdevp(null:[Date].[Day of Year].CurrentMember, [Measures].[Constant 2])

member [Measures].[Stdev 3]  as Stdev(null:[Date].[Day of Year].CurrentMember, [Measures].[Increment by 1])
member [Measures].[StdevP 3] as Stdevp(null:[Date].[Day of Year].CurrentMember, [Measures].[Increment by 1])

select
{
	[Measures].[Constant 1], [Measures].[Stdev 1], [Measures].[StdevP 1],
	[Measures].[Constant 2], [Measures].[Stdev 2], [Measures].[StdevP 2], 
	[Measures].[Increment by 1], [Measures].[Stdev 3], [Measures].[StdevP 3]
} on 0,
Head([Date].[Day of Year].[Day of Year],99) on 1
from
[Adventure Works]

Also, i believe the Var functions behave the same.

 

Regards to all,

Hrvoje Piasevoli

Answer 11

I opened a feedback in connect for this:

https://connect.microsoft.com/SQLServer/feedback/details/680282/analysis-services-stddev-function-returns-incorrect-results-with-same-value-set

 

Please vote for it bacause we need to have a workaround for that.

---

Ccote