Calculating Quartiles in Analysis Services

Histogram with quartiles

In SQL Server Analysis Services 2008, there is a native Median function in MDX, but no native Quartile function. If you need to calculate quartiles, there are a few different ways to do this. You can either use native Excel functions, code the quartile algorithm in MDX or write the algorithm as an SSAS stored procedure in .NET. In this post, I cover each of these options and provide sample code for the MDX and stored procedure options.

Excel Function

The first option is to use the Excel QUARTILE() function. Many native Excel functions can be used directly from MDX via  the EXCELMDX assembly that ships with Analysis Server. One immediate problem with this is that you must have a copy of the desktop Excel client installed on the SSAS server. In many environments, this may not be an option. Even if it were possible to install Excel on the server, I’d be concerned that there could be a performance issue with SSAS having to launch EXCEL.EXE processes and then make out-of-process COM calls for every calculation. For these reasons, I have not tried to use Excel functions. If this is something that you need to use, it’s my understanding that you need to use SetToArray() if you want to pass the values of a set to an Excel function that expects an array parameter.

MDX

The second option for calculating quartiles is to implement the algorithm in MDX. Before you do this, you’ll ovbiously need to pick a quartile algorithm. In the absence of specific requirements, it was a safe bet to assume that most of my users were going to expect that my results match what they could produce in Excel, so I chose to use the Excel algorithm in the query below.

WITH SET OrderedData AS ORDER(
	NONEMPTY([Product].[Product].[Product], [Measures].[Internet Sales Amount]),
		[Measures].[Internet Sales Amount], BASC)

MEMBER [Measures].[RowCount] AS COUNT (OrderedData)

MEMBER [Measures].[i25] AS ( .25 *  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i25Lo] AS FIX([i25]) - 1
MEMBER [Measures].[i25Rem] AS ([i25] - FIX([i25]))
MEMBER [Measures].[n25Lo] AS (OrderedData.Item([i25Lo]), [Internet Sales Amount])
MEMBER [Measures].[n25Hi] AS (OrderedData.Item([i25Lo] + 1), [Internet Sales Amount])
MEMBER [Measures].[Quartile1] AS [n25Lo] + ( [i25Rem] * ( [n25Hi] - [n25Lo] ))
	,FORMAT_STRING='Currency'

MEMBER [Measures].[Quartile2] AS MEDIAN(OrderedData, [Internet Sales Amount])
	,FORMAT_STRING='Currency'

MEMBER [Measures].[i75] AS ( .75 *  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i75Lo] AS FIX([i75]) - 1
MEMBER [Measures].[i75Rem] AS ([i75] - FIX([i75]))
MEMBER [Measures].[n75Lo] AS (OrderedData.Item([i75Lo] ), [Internet Sales Amount])
MEMBER [Measures].[n75Hi] AS (OrderedData.Item([i75Lo] + 1), [Internet Sales Amount])
MEMBER [Measures].[Quartile3] AS [n75Lo] + ( [i75Rem] * ( [n75Hi] - [n75Lo] ))
	,FORMAT_STRING='Currency'

SELECT {[Measures].[Internet Sales Amount], [Measures].[Quartile1],
    [Measures].[Quartile2], [Measures].[Quartile3]}  ON 0 ,
    {[Product].[Product].&[486]} ON 1
FROM [Adventure Works]

This query compares the Internet Sales Amount of a product (&[486] , “All-Purpose Bike Stand”) from the Adventure Works sample database to the quartile values of its peers. It works, but it is a lot of code to have to repeat if you need to calculate quartiles over many different measures, members or sets. It’s entirely possible that there’s a simpler or more generalizable way to do this, but this was the best I was able to come up with.

Stored Procedure

The last alternative for calculating quartiles is to write an SSAS stored procedure in a .NET language and call it from within MDX.

Below is a version of a quartile function for SSAS written in C#. It’s been slightly simplified from my original source.  It assumes that the set has already been sorted and stripped of empty values. In preliminary testing, I found that in situations where you needed to run a calculation over the same set, this appeared to perform better than a version that implemented the sorting and nonempty filtering internally.

public static MDXValue QuartileFromOrderedNonEmpty(Set set, Expression exp, int QTile)
{
    if (QTile < 0) throw new ArgumentOutOfRangeException("QTile");
    if (QTile > 4) throw new ArgumentOutOfRangeException("QTile");

    List<double> values = new List<double>();           

    //convert the set to an array of doubles
    foreach (Tuple t in set.Tuples)
    {
        MDXValue mdxVal = exp.Calculate(t);
        values.Add(mdxVal.ToDouble());
    }  

    //calculate the quartile for the specified QTile (e.g. 1,2,3)
    if (values.Count >= 0)
    {
        int count = values.Count;
        if (QTile == 4 || count == 1)
        {
            return values[count - 1];
        }
        double percent = ((double)QTile) / 4.0;
        double qIdx = (percent * (double)(count - 1)) ;
        double qFloor = Math.Floor(qIdx) ;
        double qFrac = qIdx - qFloor;
        return values[(int)qFloor] + (qFrac * (values[(int)(qFloor + 1)]
            - values[(int)qFloor]));
    }
    else
    {
        return null;
    }   
}

Update 2011-Aug-12: The code above was simplified from the original that used an array instead of a List<> and I left in a call to values.Length. I changed this to values.Count.

If you do prefer to perform the sort within the function, it’s easy enough to add a call to Array.Sort<double>() after you create the array of values. If you need to filter out nonempty values within the function, you will find that there is no IsEmpty property on the MDXValue class. Assuming you do not want to treat empty values as zero, you’ll need to exclude these from your array of values. I have found that if you cast an MDXValue to char, empty values are converted to the null character so you can use the following line to test for empty values.

bool isEmpty = (mdxVal.ToChar() == 0x00);

When the method is compiled into a DLL and loaded into a server or database’s assembly cache, you can use it in an MDX query in the same way you would use the Median function, except that it takes an additional parameter specifying the desired quartile number. The example below shows how to use the Quartile stored procedure.

WITH SET OrderedData AS ORDER(
    NONEMPTY([Product].[Product].[Product], [Measures].[Internet Sales Amount]),
        [Measures].[Internet Sales Amount], BASC)

MEMBER [Measures].Q1 AS
    Quartile.QuartileFromOrderedNonEmpty(
        OrderedData, [Measures].[Internet Sales Amount], 1)
    ,FORMAT_STRING='Currency'

MEMBER [Measures].Q2 AS MEDIAN(OrderedData, [Measures].[Internet Sales Amount])
    ,FORMAT_STRING='Currency'

MEMBER [Measures].Q3 AS
    Quartile.QuartileFromOrderedNonEmpty(
        OrderedData, [Measures].[Internet Sales Amount], 3)
    ,FORMAT_STRING='Currency'

SELECT {[Measures].[Internet Sales Amount],
    [Measures].Q1 , [Measures].Q2 , [Measures].Q3 }  ON 0 ,
    {[Product].[Product].&[486]} ON 1
FROM [Adventure Works]

For now, using the stored procedure option appears to be the best alternative for my current project. I have used this function in a PerformancePoint Services solution where users may browse a multilevel user hierarchy and see where the current member in the hierarchy stands within it’s peer group by displaying a chart comparing the member’s measure value to the quartile values generated over its siblings.

It also lets a user select any measure from a scorecard and updates the chart to show the member values and quartiles for the new measure. To allow this, I used the calculation dimension technique described in Chapter 6 of Expert Cube Development with Microsoft SQL Server 2008 Analysis Services to create a Benchmarks dimension with Actual, 25th Percentile, 50th Percentile, and 75th Percentile members.

For more information on SSAS stored procedure development, see Mosha’s Best practices for server ADOMD.NET stored procedures, and take a look at some of the code in the Analysis Services Stored Procedure Project. You might also want to bookmark the AdomdServer namespace on MSDN.