SQL Server 2008

Although the way of defining the KPI status and trend is very flexible it is also somehow circumstantial if you simply want to rate a KPI by its target value. In this case you would have to calculate the status of the KPI as being between –1 (worse) and +1 (best).

In many cases we find simple case statements here:

case
when KPIVALUE("TestKPI") >= 0.95 * KPIGOAL("TestKPI") then 1
when KPIVALUE("TestKPI") < 0.7 * KPIGOAL("TestKPI") then -1
else 0
end

This results in the KPI status being –1, 0 or +1. But if you want to blend smoothly between these values you have to use a formula that is a little bit more complex. Especially  if you need to this computation for many KPIs it’s not really nice.

I had some examples for AS stored procedures (sprocs) in this blog before. So here is a very simple one to calculate a linear approximation between the boundaries:

public static double KPIStatusBanding(double bound1, double bound2, double actual)
{
    double factor=0;

    if (bound1 < bound2) factor = 1;
    else if (bound1 > bound2) factor = -1;
    else return 0;

    if (actual < bound1) return -factor;
    else if (actual > bound2) return factor;
    else return factor*2 * (actual – bound1) / (bound2 – bound1) – 1;
}

The parameters are as follows:

If bound1 < bound2 bigger values are better, if bound1 > bound2, lower values are better. With this function the calculation is quite easy. First let’s test the function itself with simple MDX queries:

with
member test as ASStatistics!KPIStatusBanding(100,200,175)
select test on 0
from [Adventure Works]

This results in a status value of 0.5.

In order to use the same boundaries as in my first example, I would use this KPI status expression:

ASStatistics!KPIStatusBanding(
0.7 * KPIGOAL("TestKPI"),
0.95 * KPIGOAL("TestKPI"),
KPIVALUE("TestKPI"))

As we can see from the KPI browser the values now gets approximated smoothly:

SQL Server 2005 | SQL Server 2008

In the last post we had an issue with multiple members sharing the same name but with different key attributes. Every approach of addressing such members with MDX like

[Dimension].[Hierarchy].[Name] or [Dimension].[Hierarchy].&[Key]

fails because this fetches only one particular member while the measure data may be associated with one of the other members sharing the same key.

Ok, usually you shouldn’t be in this situation. If for example you’re using the business key (instead of the surrogate key) as the attribute key, you only get one member and all the fact data is associated to that member. Therefor the above member expressions work fine.

But with parent-child attributes you cannot do this easily and writing the MDX from the previous post in order to filter all elements with a given name is somehow painful. And if you’re keeping historical elements in the parent-child hierarchy you may want to find all elements with a given name.

The last post showed a solution to achieve this using MDX by filtering the elements by name, but this is somehow painful especially with many elements. So for today’s post we’re trying a different approach with a stored procedure.

The stored procedure simply takes a hierarchy and a string with comma separated member names and returns all matching members in the correct order. This may also be helpful for report development if you need to provide a specific subset of elements (say cost centers, regions, branches etc.) in a given order. Usually you can put those elements in a report parameter (maybe a hidden one) and use the strtoset-function to expand the elements to a set. But as stated above, this doesn’t work with elements that share the same name and it might also be difficult to achieve a manually given sort order.

So, here’s how the function works in the Adventure Works cube:

select {[Measures].[Amount]} on 0,
ASTools.GetMembers([Account].[Accounts],’Assets,Current Assets, Other Assets’) on 1

from [Adventure Works]   

The result looks like this:

Note that although we just specified ‘Other Assets’, both accounts with that name are now in the list. This is the behavior we wanted to achieve. As stated in the last post, in reality, the two account are not the same, but for our example, we pretend they are as the situation would be exactly identical with slowly changing parent-child structures.

For clarification, take a look at the following query:

select {[Measures].[Amount]} on 0,
{
[Account].[Accounts].[Assets],
[Account].[Accounts].[Current Assets],
[Account].[Accounts].[Other Assets]
} on 1

from [Adventure Works]

In this case the account ‘Other Assets’ is specified by it’s member’s unique name which refers to only one member of the dimension. As expected, the result looks like this (note the missing $75K):

Finallly, here is the source code for the procedure:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdServer;

namespace SSASTools
{
    public class SSASLibrary
    {
        public Set GetMembers(Hierarchy h, String memberList)
        {
            SetBuilder sb = new SetBuilder();
            Expression ex = new Expression();

            String[] members = memberList.Split(‘,’);

            foreach (String cmembername in members)
            {  
                ex.ExpressionText = "filter(" + h.UniqueName + ".allmembers, " + h.UniqueName + ".currentmember.name=’"+ cmembername.Trim() + "’)";
                Set s = ex.CalculateMdxObject(null).ToSet();

                foreach (Tuple t in s) {               
                    sb.Add(t);
                }         
            }

            return sb.ToSet();
        }
    }
}

SQL Server 2005 | SQL Server 2008

Stored procedures offer a really powerful extensibility for Microsoft OLAP. But the more complex the functions get, the more you need debugging capabilities. Of course you will want to put the business logic of your procedure into a separate class file that can be tested and debugged without any of the SQL Server components. But often enough, the problems happens just in the context of the OLAP engine. Usually you’re using objects within the Microsoft.AnalysisServices.AdomdServer scope like Set, Expression, Tuple etc. Code that relies on these objects can only be executed in the context of the OLAP engine. This makes testing a lot more complicated.

Below is the procedure for debugging stored procedures by attaching to the OLAP service. You might also want to read the corresponding technet article.

First you build your assembly as usual and register it in Analysis Services. I’m using SSMS for that. Be sure to check the option for "Include debug information" (this includes the pdb file of the assembly as well as you can see by looking at the generated script) and to set permissions to ‘unrestricted’ as shown in the screen shot below:

(click to enlarge)

Now you switch to Visual Studio and set the breakpoints in your code.

(click to enlarge)

Ok, now for the most important part, you choose Debug / Attach to process… in your Visual Studio environment.

In the following dialog make sure that you allow attaching to managed code (press the ‘Select…’ button in case managed code is not in the list). You should also active the option to show processes in all sessions as shown in the screenshot above.

Now locate the entry for msmdsrv.exe in the list, select it and click ‘Attach’.

(click to enlarge)

Visual Studios now enters debugging mode waiting for the breakpoint to be reached.

In order to make the code stop at your breakpoint you need to trigger some action on the server that causes the code with the breakpoint to execute. For example you could open a report in Report Manager or you could execute an MDX query in SSMS.

In our example we could execute this query that runs the stored procedure with our breakpoint:

WITH
  MEMBER sparkplot AS
    ASStatistics.SparkPlot(
    ([Date].[Date].&[1] : [Date].[Date].&[1100]),
    [Measures].[Internet Gross Profit])
SELECT
  {
    sparkplot,
    [Measures].[Internet Gross Profit]
  } ON columns,
  {
    [Product].[Subcategory].&[1],
    [Product].[Subcategory].&[2]
  } ON 1
FROM [Adventure Works]

SQL Server 2005 | SQL Server 2008

In a previous post I showed an example of how to return a dynamic bitmap graphic from a SSAS stored procedure and we also displayed a Win/Loss chart using this technique. Now, I just want to add some other ideas for creating such sparklines.

Of course, the first idea is to plot some micro line charts. And it really isn’t difficult. The result looks somewhat like this:

The main code for the plot looks like this (I’ve removed some lines to increase readability):

// Create Bitmap
Bitmap bmp = new Bitmap(plot_width, plot_height, PixelFormat.Format32bppArgb);
Graphics gBmp = Graphics.FromImage(bmp);

// calculate min and max values
…

// do the output
for (i = 1; i <= count; i++)
{
    float x1 = (float)(i * ((double)plot_width) / (double)count);
    float y1 = (float)((q[i]-t_min) * scale);               
    gBmp.DrawLine(Pens.Black, x0, plot_height – y0, x1, plot_height – y1);
    x0 = x1;
    y0 = y1;
}
gBmp.Dispose();

// Serialize the output
MemoryStream IS = new MemoryStream();
bmp.Save(IS, ImageFormat.Png);
IS.Flush();
return Convert.ToBase64String(IS.ToArray());

The code is very similar to the code I posted about two weeks ago. First, we create the Bitmap and Graphics object (the canvas to paint on). Then we’re plotting the line and finally we serialize the output to a base 64 encoded string.

Another idea might be turn the output of the stored procedure for the quantile calculation from my last post into a nice graphic.

The result may be rendered to a histogram output like this:

Another to display this in a more granular way is shown in the example below. Like in the first plot, the red area is below the quantile.

The code for both outputs is a bit lengthy but the basic idea is still very simple. Just create a canvas with the code posted before and draw the graphic on this canvas. After that serialize the canvas to a base 64 encoded string. For the report it just requires an image component that takes the base 64 encoded string is its source. Therefor the string needs to be converted using the function Convert.FromBase64String(…).

So, rendering graphics in stored procedures can be a really flexible way of creating small data-aware graphics. 

Such graphics can be really helpful in understanding the meaning of a report at a glance. But they can also be very confusing. So, in my opinion, it’s best practice to keep sparkline graphics (and all other small graphics that are repeated per line) as simple as possible.

In most cases I really don’t recommend creating sparklines or other kinds of micro graphics using the approach above. There are excellent toolsets out there, for example by BonaVista Systems or Bissantz that really simplify the process of drawing sparklines a lot and that even work with Excel, Reporting Services or other frontend clients. So in most cases you will want to use such tools. Only for the rare cases that you really need to create a special chart that is not supported by such toolsets, you might consider returning the graphic by creating your own stored procedure like shown above.

SQL Server 2005

In a previous post some weeks ago I wrote about calculating a quantile in MDX. Of course you could also think of using a SSAS stored procedure for the calculation as stored procedures offer the full range of .Net programmability and libraries for doing the calculations. In my last post about quantiles I simplified the calculation a lot by assuming that our computation results in a single element. This made the calculation easy to be written in MDX. But if you need the quantile at a higher precision you could approximate the values between two discrete elements.

First let’s take a brief look at the simplification we did when calculating the quantile in MDX: The calculation of the quantile is simple in the case that (n-1) * p% gives an integer (n=number of cases, p%=percent of the quantile). In our example we had 10001 scenarios (n=10001) and computed a 5% quantile (p%=5%) giving (n-1)*p% = 500. So we simply have to take the 501th element from the sorted set of cases.

If y:=1+(n-1)*p% is not an integer, we have to use the following calculation:

Q = ( S(b) – S(a) ) * ( y – a )

where a is the integer part of (n-1)*p%+1, b=a+1 and S(a), S(b) the scenario values at index a or b. There are other special cases for the computation but I don’t want to get into too much detail here.

The following code just shows the simple calculation (sort the elements and take the Nth element) but it can easily be extended to a higher sophisticated calculation:

using System;
using System.Collections.Generic;
using Microsoft.AnalysisServices.AdomdServer;
using Microsoft.AnalysisServices;

namespace ASStatistics
{
    public class Statistic
    {
        public static double Quantile(Set inputSet, Expression inputExpression, double quantile)
        {

            if (inputSet.Tuples.Count == 0) return 0;

            System.Diagnostics.Debug.Assert(quantile >= 0 & quantile < 1, "Error in Quantile calculation");                       
            double[] q=new double[inputSet.Tuples.Count];
            int i=0;

            foreach (Tuple tuple in inputSet)
            {
                q[i] = inputExpression.Calculate(tuple).ToDouble();
                i++;
            }

            Array.Sort(q);

            int position = (int)(quantile * i);
            return q[position];
        }

    }
}

After installing this library with the stored procedure to our SSAS server (e.g. using SSMS), we can test it using MDX:

with
member [Quantile_0] as ASStatistics.Quantile([Date].[Date].[Date],[Measures].[Gross Profit],0)
member [Quantile_5] as ASStatistics.Quantile([Date].[Date].[Date],[Measures].[Gross Profit],0.05)
member [Quantile_20] as ASStatistics.Quantile([Date].[Date].[Date],[Measures].[Gross Profit],0.20)

select {[Quantile_0],[Quantile_5],[Quantile_20]} on 0
from [Adventure Works]

So, the worst profit at one day was -599576$ but only in 5% of the days the profit was below 986$ and only in 20% of the days the profit was below 4576$.

Now, the code above should be easy to adapt to all the cases for the quantile computation. If I find some time to do so, I’ll post it later on.

So it’s really easy to do the calculation in a stored procedure. But how about performance? I used the following queries to measure the performance (both formatted by the current release of Mosha’s MDX Studio). First the query for the MDX calculation:

WITH  
  MEMBER [QuantileMDX] AS
    max(
      bottomcount(
        [Date].[Date].[Date],
        (int(((Count([Date].[Date].[Date]) – 1) * 0.05)) + 1),
        [Measures].[Gross Profit]),
      [Measures].[Gross Profit])
SELECT
  [QuantileMDX] ON 0
FROM [Adventure Works]

And here’s the query for the stored procedure approach:

WITH
  MEMBER [QuantileStoredProcedure] AS
    ASStatistics.Quantile(
    [Date].[Date].[Date],
    [Measures].[Gross Profit], 0.05)
SELECT
  [QuantileStoredProcedure] ON 0
FROM [Adventure Works]

Before measuring performance it’s important to take the OLAP cache into account. The cache can be cleared using a simple XMLA script like this (see here for more details):

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Adventure Works DW</DatabaseID>
    </Object>
  </ClearCache>
</Batch>

So here are the result of the above queries (tested on my notebook):

The values differed a little so I took the average over 10 executions (removing the best and worse case before). Of course the comparison is not absolutely accurate because the calculation method, the implementation of sort in both languages etc. also have an influence on the result. Furthermore, both functions could by optimized (maybe you want to use the MDX tail function instead of max or try another approach).

I still think the result is significant. While there is not a big difference with the cold cache, the difference becomes much bigger with the warm cache. The MDX variant takes more profit from the cache than the stored procedure approach.

While experimenting with the stored procedure I also added another variant that only takes a set and the quantile as parameters:

public static double Quantile(Set inputSet, double quantile)

The only difference to the version above is that I evaluate the set results using

MDXValue.FromTuple(tuple).ToDouble();

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

instead of

inputExpression.Calculate(tuple).ToDouble();

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This way, I can call the function in both ways (with two or three parameters):

ASStatistics.Quantile(([Date].[Date].[Date],[Measures].[Gross Profit]), 0.05)

or

ASStatistics.Quantile([Date].[Date].[Date],[Measures].[Gross Profit], 0.05)

It’s quite interesting (and I didn’t expected this) that the variant with the two parameters is much slower. Even with a warm cache it took about 1500ms to compute the above result (more than 9 sec on a cold cache).

So, although passing sets to stored procedure is no longer a bottleneck for performance, you should still be aware of performance issues.

SQL Server 2005

Sascha Lorenz posted the source code for creating win/loss charts in his blog. I really like this kind of charts as it is especially useful for sparklines. Sascha created the bitmap for the chart dynamically on demand using ASP.NET. I thought this could be a great idea for our interactive graphics from the previous posts so that we can easily define them inside the cube and use them in a SSRS report.

First, let’s look at the result:

The underlying data is just an example. It shows the days on which the order amount is higher (green) or lower (red) compared to the previous day covering the last 32 days.

The MDX query for our report looks like this:

WITH
  MEMBER [WLPlot] AS
    ASStatistics.WinLossChart(
    tail(
      nonempty(
        [Date].[Date].[Date],
        [Measures].[Sales Amount]),32),
    [Measures].[Sales Amount], (
    [Measures].[Sales Amount],
    [Date].[Date].currentmember.prevmember))
SELECT
  {
    [WLPlot],
    [Gross Profit Margin]
  } ON 0,
  [Product].[Category].[Category] ON 1
FROM [Adventure Works]

I turned Sascha’s function into a stored procedure named WinLossChart. WinLossChart takes three arguments:

If the value is higher than the threshold we paint a green bar, if it is lower, we paint a red bar. For the simple example above we made Sales Amount the value expression and the Sales Amount of the previous day the threshold expression. Of course, the threshold could also be a simple constant.  

So, here’s the code for the stored procedure. The first function returns the bitmap and is very close to the code Sascha posted in his blog entry:

public static Bitmap CreateWinLossBitmap(double[] q, double[] threshold)
{
    int i;
    int l = q.Length;
    Bitmap objBitmap = new Bitmap(5 * l + 20, 22);
    Graphics objGraphic = Graphics.FromImage(objBitmap);

    objGraphic.FillRectangle(Brushes.White, 0, 0, 5 * l + 20, 30);
    for (i = 0; i < l; ++i)
    {
        if (q[i] == Double.MinValue)
            objGraphic.FillRectangle(Brushes.Gray, 10 + (i * 5), 9, 4, 4);
        else
            if (q[i] > threshold[i])
                objGraphic.FillRectangle(Brushes.Green, 10 + (i * 5), 2, 4, 7);
            else
                objGraphic.FillRectangle(Brushes.Red, 10 + (i * 5), 10, 4, 7);
    }          

    return objBitmap;
} 

And here’s the function wrapping the above code into a SSAS stored procedure. This is the function we’re calling from MDX:

public static string WinLossChart(Set inputset, Expression valueExpression, Expression thresholdExpression)
        {
            double[] q = new double[inputset.Tuples.Count];
            double[] thr = new double[inputset.Tuples.Count];
            int idx = 0;

            foreach (Tuple tuple in inputset)
            {
                q[idx] = valueExpression.Calculate(tuple).ToDouble();
                thr[idx] = thresholdExpression.Calculate(tuple).ToDouble();
                idx++;
            }

            Bitmap bmp = CreateWinLossBitmap(q, thr);
            MemoryStream IS = new MemoryStream();
            bmp.Save(IS, ImageFormat.Png);
            IS.Flush();

            return Convert.ToBase64String(IS.ToArray());
        }

For building the library you need references to

• Microsoft.AnalysisServices (Microsoft.AnalysisServices.dll)
  Microsoft.AnalysisServices.AdomdClient (Microsoft.AnalysisServices.AdomdClient.dll)
  Microsoft.AnalysisServices.XMLA (Microsoft.AnalysisServices.XMLA.dll)
  msmgdsrv (msmgdsrv.dll)

If you don’t have the above assemblies installed on your machine you can take them from a SQL Server installation and install them to the global assembly cache (GAC) using

Gacutil.exe /i assembly_name.dll

You also have to add references on these libraries to your C# project like this:

<ItemGroup>   
    <Reference Include=”Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL”>
      <SpecificVersion>False</SpecificVersion>
    </Reference>
    <Reference Include=”Microsoft.AnalysisServices.AdomdClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL”>
      <SpecificVersion>False</SpecificVersion>
    </Reference>
    <Reference Include=”Microsoft.AnalysisServices.XMLA, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL”>
      <SpecificVersion>False</SpecificVersion>
    </Reference>   
    <Reference Include=”msmgdsrv, Version=9.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86″>
      <SpecificVersion>False</SpecificVersion>     
      <Private>False</Private>
    </Reference>
     …   
</ItemGroup>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Of course the code above is just to show the basic idea and should be rewritten for a productive environment.

Thanks to Sascha for the visualization. Again, I recommend reading his blog entry for the original code.

SQL Server 2005

I just played with a simple way to return an image from a stored procedure by returning a base 64 encoded string of the image in a certain image format (e.g. PNG or JPEG).

Our sample stored procedure looks like this:

public static String SampleImage()
        {
            Bitmap bmp = new Bitmap( 80, 20, PixelFormat.Format32bppArgb);
            Graphics gBmp = Graphics.FromImage(bmp);
            System.Random RndObject = new System.Random();

            gBmp.CompositingMode = CompositingMode.SourceCopy;

            int y=10;
            int y1 = 0;
            Pen p = new Pen(Color.Black);
            gBmp.DrawLine(p, 0, 10, 80, 10);

            p.Color = Color.Blue;

            p.Width = 1;
            for (int i=10;i<=80;i+=10) {
                y1 = RndObject.Next(1, 19);
                gBmp.DrawLine(p, i – 10, y, i, y1);
                y = y1;
            }

            MemoryStream IS = new MemoryStream();
            bmp.Save(IS, ImageFormat.Png);                       
            IS.Flush();

            return Convert.ToBase64String(IS.ToArray());
        }

The function draws a very simple random line chart (we provided no real data but in some later posts I will provide some samples) and returns the resulting in-memory bitmap as base 64 encoded string.

Therefore the MDX code 

with member bmp as ASStatistics.SampleImage()

select {bmp} on columns,
[Product].[Product].[Product] on 1

from [Adventure Works]

returns a list of products together with the base 64 encoded image string. We use this MDX query for the report data source. In order to show the image on a report, we have to use the .Net function Convert.FromBase64(…). So, first we place an image object on our report, then we set the value property of the image to

=Convert.FromBase64String(Fields!bmp.Value)

That’s all folks. The result looks like this:

Ok, this isn’t exactly nice, but it still gives us some freedom in designing custom data aware graphics, like sparklines or other micro charts, and put them on a report.

SQL Server 2005

In the last post we discussed some background on statistics. Now let’s turn this into a KPI target. If the parameters of the test are well known in advance the limits for our KPI can be computed for example using Excel formulas.

But if the parameters are based on user input, the computation of the thresholds has to be done in our OLAP engine (MDX). For example, in risk management we compute the Value-at-Risk (VaR) for each day. The VaR is a measure giving us the maximum loss with a certain confidence level. If our confidence level is e.g. 5% the VaR gives our maximum loss for 95 out of 100 days. In the remaining 5 days we expect our loss to be higher (so the VaR is actually a quantile of the probability function of our win/loss). The computation of the VaR requires some kind of model and during the backtesting process we make sure that the model is working properly. So our hypothesis is that the model is correct while the alternative hypthesis is that the model is wrong. Our model is correct if the VaR computed based on the model meets the really observed losses. We count the days in which the actual loss was higher than predicted by the VaR. If it’s 5% we can be say, that our model is correct. If not, it may be that the model is incorrect or the sample isn’t representative.

If the number of days (maybe the period) is a parameter (maybe a report parameter or a value taken from another dimension), we have to do the computation in the OLAP model.

For this, we have to start with the binomial distribution given by

where n is the total number of cases (days for which we’re doing the backtesting), k is the number of observed losses higher than the predicted loss, p is the probability for such a higher loss (in our example 5%), q=1-p (the confidence level of our VaR calculation) and

For example for k=0 we get

As we’ve seen in the last post, we need the aggregated probability which looks like this:

Now we can define our KPI status for example as

The calculation of the above function is a little tricky and cannot easily be done in MDX. We could call the Excel-Function BINOMDIST, but I didn’t get it to work (not all Excel functions are also available for MDX). So here is an approach using an SSAS stored procedure.

Instead of computing the sum over the binomials we actually compute the incomplete beta integral which works much better than dealing with the large numbers resulting from the factorials. There is an excellent set of statistic function available at http://www.alglib.net for free and I used the code from there (see http://www.alglib.net/specialfunctions/distributions/binomial.php to download the code).

An SSAS stored procedure doesn’t have much requirements. In its minimal form, it consists of a single static class with one or more public static functions. The code has to be compiled to a class library (Assembly DLL-File) which can then be referenced from SSAS (by simply adding it to the assemblies using Management Studio). There are many code samples available at Codeplex: http://www.codeplex.com/ASStoredProcedures

In our case, the code for the class (C#) simply looks like this:

using System;

namespace ASStatistics
{
    public class Statistic
    {
        public static double BinomialDistribution(int k, int n, double p) {
            return binomialdistr.binomialdistribution(k, n, p);
            }

    }
}

The function call is taken from the AlgLib library above. The MDX code for calling the library function (once the Assembly is registered in SSAS) may look like this

WITH
  MEMBER bindist AS
    ASStatistics.BinomialDistribution(5, 500, 0.01)
SELECT
  bindist ON 0
FROM [Adventure Works]

This gives the following result:

So, if the number of cases if fixed, say 500, the KPI status expression for our Backtesting KPI may look like this:

case
when ASStatistics.BinomialDistribution(KPIVALUE("Backtesting"),500,0.01) <= 0.95 then 1
when ASStatistics.BinomialDistribution(KPIVALUE("Backtesting"),500,0.01) <= 0.9999 then 0
else -1
end 

The above confidence levels of 95% and 99.99% correspond to the Basel II recommendation for backtesting of the VaR.