Problems with MDX’s STDEV Function

I spent quite a bit of time this morning wrestling with the quirkiness of MDX’s STDEV function (SQL Server Analysis Services 2008 R2). For the most part it works well, but there are a few things to know that will save you a ton of debugging time:

  • The STDEV function doesn’t like NULL values. NULL values aren’t converted to a zero.
  • If STDEV is being calculated across only one tuple, it will return -1.#ND. I had hoped it would return a 0. So I need to perform a check to ensure the set has more than one row.
  • Worst of all, it just doesn’t seem to like some numbers.

Try out this MDX on AdventureWorks (for SQL Server 2008 R2):

WITH MEMBER StDevTest as STDEV([Date].[Calendar].CurrentMember.Children, [Measures].[Sales Amount Quota]) SELECT StDevTest on 0, [Date].[Calendar].[Calendar Quarter].Members ON 1 FROM [Adventure Works]

You’ll see that the value for [Q1 CY 2002] is correctly zero, whereas the value for [Q2 CY 2002] is -1.#ND. Both quarters consist of three children (months) with the same value (1583333.33 and 1689333.33, respectively). So the STDEV should be zero for both quarters. But SSAS doesn’t seem to like 1689333.33. I tried an STDEV in SQL on that value. It returned zero. I also created a very stripped down cube using those values, but this time, I received something like

The reason I say the third point is the worst is because I can’t seem to figure out how to get around that one. The first two points have workarounds, although they are tedious – remove null tuples and test for only one tuple in the set for which we’re applying the STDEV. There doesn’t seem to be a way to test for NaN values like C#’s double.IsNaN function – which BTW means an SSAS “stored procedure” would be an answer for this one.

For my situation, this wasn’t just a matter of having an ugly -1.#ND show up in a cell. I was attempting to determine the granularity of a measure by testing at what level the measures of children are not the same as the parent. For example, if all months under each quarter had the same value as its parent quarter, it’s possible that the values are at least not at the month level. The STDEV for the months of a quarter should be 0 if all the months have the same value. Run this MDX which does just that:

WITH MEMBER StDevTest as SUM([Date].[Calendar].[Calendar Quarter].Members,STDEV([Date].[Calendar].CurrentMember.Children, [Measures].[Sales Amount Quota])) SELECT StDevTest on 0 FROM [Adventure Works]

You’ll see one value of -1.#ND. If you remove [Q2 CY 2002], a valid sum displays:

WITH MEMBER StDevTest as SUM([Date].[Calendar].[Calendar Quarter].Members-[Date].[Calendar].[Calendar Quarter].[Q2 CY 2002],STDEV([Date].[Calendar].CurrentMember.Children, [Measures].[Sales Amount Quota])) SELECT StDevTest on 0 FROM [Adventure Works]

The actual MDX I used to find the granularity was much more complex and tougher to debug:

WITH  MEMBER [MEASURES].[Granularity Level] AS ([MEASURES].[Granularity Level Recursive],[Date].[Calendar].Levels([Date].[Calendar].Levels.Count-1).Members.Item(0))  MEMBER [MEASURES].[Granularity Level Recursive] AS   IIF ([Date].[Calendar].CurrentMember.Level.Ordinal=0, [Date].[Calendar].CurrentMember.Level.Name, IIF (SUM([Date].[Calendar].CurrentMember.Parent.Level.Members, IIF (NONEMPTY([Date].[Calendar].CurrentMember.Children,[Measures].[Sales Amount Quota]).Count>1, STDEV([Date].[Calendar].CurrentMember.Children, [Measures].[Sales Amount Quota]),0))=0,    ([MEASURES].[Granularity Level Recursive], [Date].[Calendar].CurrentMember.Parent), [Date].[Calendar].CurrentMember.Level.Name))  SELECT  {[MEASURES].[Granularity Level]} ON 0  FROM  [Adventure Works]

Actually, this method doesn’t work in AdventureWorks because although the [Sales Amount Quota] measure is quarterly, it is SCOPEd to allocate monthly values. This would actually work on the particular cube I’m working on since values at different granularities are keyed to a day key (ex: Values for Q1/2001 are keyed to 1/1/2001).

Why I was doing this and why I needed to resort to this method is well beyond the scope of this blog, but the result is that the -1.#ND value for [Q2 CY 2002] screwed up my entire calculation. I should be happy that the day has finally come where the STDEV function is actually something I need to engage it deeply enough to run into such quirks.

Note: I apologize for the bad formatting of the MDX. WordPress does a lot of weird things with the formatting cut directly out of SSMS, so it’s easier to strip out the formatting to plain text. Here is a cleaner version of that granularity MDX with some embedded comments.

About Eugene

Business Intelligence and Predictive Analytics on the Microsoft BI Stack.
This entry was posted in BI Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s