Search This Blog

Saturday, July 26, 2008

Some more samples for data aware images created in a stored procedure

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:

image_thumb1

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:

image_thumb2

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.

image_thumb4

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.

Saturday, July 19, 2008

Some more thoughts on quantiles...

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]

image_thumb

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):

  Cold Cache Warm Cache
MDX 188ms 36ms
Stored Procedure 217ms 63ms

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();


 



instead of





inputExpression.Calculate(tuple).ToDouble();





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.

Saturday, July 12, 2008

Win/Loss chart as dynamic image created in SSAS stored procedure

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:

image4

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:

Argument Description
inputset The set over which the expressions are to be evaluated
valueExpression The expression for the value
thresholdExpression The expression for the threshold

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>



 


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.

Saturday, July 5, 2008

Returning an image from an SSAS stored procedure

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.





Therefor 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:





image_thumb3





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.

Tuesday, July 1, 2008

Online version of MDX studio available

One of the great features in the current release of Mosha's MDX studio is the ability to format MDX queries. Now this feature is available online as well using this URL: http://mdx.mosha.com/. For more details see Mosha's blog entry.

If you need to format SQL queries, you can also use online services like sqlHere.