Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services (SSAS) 2012 Standard Deviation Functions


By:   |   Read Comments (2)   |   Related Tips: > Analysis Services Development

Problem

How does the standard deviation and related statistical functions work in SQL Server Analysis Services?  Are those functions natively available?  Can you call this code in MDX?  Check out this tip to learn more.

Solution

The standard deviation function is SQL Server Analysis Services (SSAS) 2012 calculates values similar to other statistical tools yet several caveats exist when looking at the results and which figures result in errors. First, care must be taken to understand how filters and custom MDX affect the results. Second, the dimension level used impacts the standard deviation calculation. Furthermore, four standard deviation MDX functions exists in the statistical function set: STDEV, STDEVP, STDDEV and STDDEVP. Actually two of the functions, STDDEV and STDEVP are aliases for STDEV and STDEVP, respectively. Not surprisingly, the difference between STDEV and STDEVP goes back to some basic statistics. STDEV uses what is called an unbiased population (dividing by n-1....see http://www.youtube.com/watch?v=DNAnQBhGpRw) whereas STDEVP uses a biased population (dividing by n....see http://www.youtube.com/watch?v=KkaU2ur3Ymw). 

STDEV and STDEVP in SQL Server Analysis Services 2012

Deciding whether to use STDEV or STDEVP requires a good understanding of the difference in the calculation method, the data to be evaluated, and is often an academic exercise. However, the results of each can produce dramatically different values. We need to remember that standard deviation is a measure of dispersion from the mean; the key is whether the group of numbers we are evaluating is a finite group of numbers where using n or STDEVP is correct or is the list of values just a sample of the entire population. Okay enough statistical theory; let us move to some real world examples. As usual, we will use the AdventureWorks 2012 DW SSAS database which can be downloaded from CodePlex. Let us look at the basic syntax of both standard deviation functions. 

STDEVP(Set_Expression [ ,Numeric_Expression ] )

or

STDEV(Set_Expression [ ,Numeric_Expression ] )

Thus, the function requires two arguments; first you must provide a set of values over which the measure will be evaluated, and then you must provide the numeric measure to be evaluated. For instance, we could say that the set expression will be all the individual date values in a date hierarchy. Secondly, we could provide a sales value as the numeric expression. Following through with this example, the below illustration uses the Date.Calendar dimension and specifically the Current Members.Children function as the set expression and the Internet Sales Amount as the numeric measure value. For the below example, we are using the biased value since we should have all values in a population. Also note that using CurrentMember.Children for the set will always look down one level from the point in the calendar hierarchy selected, and thus will return all values one level below the selected Date.Calendar hierarchy level.

STDEVP 
( { [Date].[Calendar].CurrentMember.Children},
[Measures].[Internet Sales Amount])

The results of this calculated measure are displayed in the Excel Pivot Table below; note that this calculation is STDEVP or a biased n value (dividing by n).

Excel Pivot

At a first glance the calculations seem to be working well. The figures in the green column display the SSAS calculated measure. Notice a few of the fields have #NUM! and a few have 0; the reason for the errors and zero value cells will be discussed later in this tip. The purple column displays the values Excel calculated for the standard deviation of a population while the last column displays how Excel (and SSAS) performed the calculation to arrive at the figure displayed.

Next, we can switch to using the unbiased standard deviation calculation, STDEV.

STDEV
( { [Date].[Calendar].CurrentMember.Children},
[Measures].[Internet Sales Amount])

The results of this calculated measure are displayed in the Excel Pivot Table below; note that this calculation is STDEV, so an unbiased sample (dividing by n-1) is used.

The results of this calculated measure are displayed in the Excel Pivot Table

Again the figures in the above screen shot from Excel display, for the most part, valid figures. The figures in the blue column display the SSAS calculated measure for a sample population; the remaining columns are the same as the previous example. You may be saying, so what is wrong with the calculations as Excel has validated several of the pivot data points. There are actually several points of contention that I will outline next. First, notice how the CY 2005 line item displays 0 (zero) for the biased calculation, but displays #NUM! (an error) for the unbiased calculation. Let us drill down into the 2005 figures to see what is happening.

Again the figures in the above screen print from Excel display, for the most part, valid figures

Actually, the results make sense when we work through the details of what the calculation is looking at for its inputs. For the biased calculation, the zero value is derived as follows:

  1. Two possible values potentially exist for the base Internet Sales Amount figure, one for Half Year 1 (H1 CY 2005) and one for Half Year 2 (H2 CY 2005). Remember, CurrentMember.Children looks at only the values one level below the currently selected member.
  2. However, Half Year 1 (H1 CY 2005) actually is "Nothing" or NULL in SQL speak, so that value is actually excluded from the calculation.
  3. Thus, we have one value for our calculation, so the median is that same as the original value, and our "n" value is 1. This results in a calculation of 0/1 or 0.

To the contrary, the unbiased calculation uses the following calculation process:

  1. Two possible values potentially exist for the base Internet Sales Amount figure, one for Half Year 1 (H1 CY 2005) and one for Half Year 2 (H2 CY 2005).
  2. However, Half Year 1 (H1 CY 2005) actually is "Nothing" or NULL in SQL speak, so that value is actually excluded from the calculation.
  3. Thus, we have one value for our calculation, so the median is the same value as the original value, and our "n" value is 1, but the unbiased calculation uses "n-1". This results in a calculation of 0/0 or #NUM! ERROR.

Another issue that surfaces with either standard deviation function centers on how we define the set to be evaluated. In this particular instance, we are using the Date.Calendar hierarchy and the CurrentMember.Children function to allow us to navigate the hierarchy. Unfortunately, what happens in our example, when we navigate to the "bottom" of the hierarchy, the date level, we again get an error as illustrated below. The reason for the error stems from the fact that there are no "children" data points below the Date level in the set, so the error results.

In this particular instance, we are using the Date.Calendar hierarchy

One way around this issue is to explicitly assign the level of the set to be used within the hierarchy. Thus, we can switch the set expression to use the Date.Calendar.Date members for the set, as displayed below, which means that no matter what level is displayed, the standard deviation will always be the standard deviation calculated for all values at the individual date level!

STDEVP
( { [Date].[Calendar].[Date].Members},
[Measures].[Internet Sales Amount])

OR

STDEV
( { [Date].[Calendar].[Date].Members},
[Measures].[Internet Sales Amount])

The results of this new formula are shown below; look specifically at the two right most columns. Now, a value exists for all the individual levels, but it is exactly the same for every date. You can see that defining the set values is extremely important and should be reflective of the exact data point and level needed.

Now, a value exists for all the individual levels, but it is exactly the same for every date

Of course, not all the news is "bad" about standard deviation; as you see in the below screen sho, you can drill down into various dimensions and the standard deviation does adjust for the slicer you put into place on the pivot. The pivot below drills into the Sales Territory dimension, and appropriately adjusts the value for the standard deviation based on the dimension selection.

Of course, not all the news is "bad" about standard deviation

In many instances, you may be concerned with providing a standard deviation calculated member in the cube without end users having a thorough understanding of how the calculation is made. Of course, as a result of the variations and sensitivity of the standard deviation calculation, it may be beneficial to hand write the MDX query instead of using a cube calculated measure. The syntax is somewhat similar to the cube calculated measure method, but as detailed in the below code snippet, we are using a calculated member (aka named set) for the MDX query, and then returning that value in the actual Select clause. You will also notice that for the Set expression on the standard deviation function (line 5 in the below example), we are restricting/filtering the set to only include calendar date values between 2007-01-01 and 2007-12-31. This method of hand coding the MDX provides us with a method of controlling the set used to calculate the standard deviation.

WITH 
MEMBER [Internet Sales Amount Standard Deviation-Biased-DateSet] AS
(
STDEVP
( { [Date].[Calendar].[Date].&[20070101] : [Date].[Calendar].[Date].&[20071231]}, [Measures].[Internet Sales Amount])
),
FORMAT_STRING = '#,##0.00;-#,##0.00'
SELECT
{
[Measures].[Internet Sales Amount],
[Internet Sales Amount Standard Deviation-Biased-DateSet]
} ON COLUMNS,
[Date].[Calendar].[Date] ON ROWS
FROM [Adventure Works];

The below screen shot displays the standard deviation result based on an MDX query which is filtered to just use the 2007 data set.

The below screen prints displays the standard deviation result based on an MDX query which is filtered to just use the 2007 data set

Conclusion

The standard deviation functions in SSAS, STDEV and STDEVP, provide a way to calculate the standard deviation for a specific measure within a set of data. Within the function, the exact set which is defined and is to be traversed for the calculation is ultimately very important. The set specified controls which values will be evaluated by the standard deviation function and whether or not an error is displayed. Some options which can be used to define a set include "hard coding" a set of values, using a filtered list, or utilizing a dynamic list based on a selected value, such as CurrentMember.Children. Alternately, to garner more control, running the function within a MDX query sometimes provides more control for the user to complete the standard deviation calculation. The standard deviation functions work well; however, as with many MDX and SSAS functions, a good understanding of the calculation is key to getting valid results.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, August 08, 2013 - 4:08:37 PM - Scott Murray Back To Top

Ike,

Both SSRS and SQL have a StDev and StDevP function.... with many of the same limitations.

  Take a look at: http://technet.microsoft.com/en-us/library/dd255266%28v=sql.105%29.aspx

 


Thursday, August 08, 2013 - 2:22:01 PM - IkeA Back To Top

Dear Author,

Is there anything that can do this in SSRS or my sql code?  Can you only do this in SSAS?

--IKE


Learn more about SQL Server tools