We will be giving demos at the Infragistics booth (#616) at Tech Ed 2011 in Atlanta. Come by and say hello!

- Trevor

In previous posts we demonstrated calling CenterSpace’s NMath libraries from Excel. Some customers asked if we could call these libraries from Microsoft’s SQL Server so we decided to give it a shot. It turns out the problem is a substantially harder problem to solve as the SQL environment is an order of magnitude more complex. This post will show it is possible, but with a few caveats.

There are a myriad of ways SQL Server can be setup and for this post, we took whatever shortcut we could find. Our goal was to get this running and worry about doing it “right” later. We found ourselves on a fairly steep learning curve without a lot of examples to work off of. Consider this more of a “proof of concept” post than the definitive “how to”.

As mentioned above there are lot of different approaches to this problem. In our minds, we wanted to see if we could couple our library at the server side and access results via Reporting Services and Report Builder. We believed that the computations would be more efficient to be handled at the server while the result could take advantage of the graphing packages in Report Services.

Our approach was to build a standalone system to develop and test this work on. We choose a standard PC platform running Windows 7. The first task at hand was to decide which version of Microsoft SQL Server to work with. We chose SQL Server 2008R2 full version as we needed the Report Services support. Microsoft supplies a six month trial version that has all services available which was perfect for our project. We installed the full version with support for the Management Studio, Report Services, and Analysis services. In addition, we downloaded Microsoft’s AdventureWorks sample databases for our testing environment.

We got this all running and ran some sample scripts to make sure we had everything running correctly before moving on. Since we were going to be developing code we installed Visual Studio 2010 next and made sure we included the necessary development templates for the SQL Server environment.

After installing CenterSpace’s NMath 5.0 library, we were ready to write some code.

We will start by creating a new project in Visual Studio called NMathSQL.

Creating a new SQL CLR project in Visual Studio

We then asked to pick a server and identify the database we will be using.

Connecting to the Database

In the next screen we will want to enable SQL/CLR debugging.

Enabling SQL/CLR debugging

At this point we need to start running SQL scripts as part of the overall setup. We could exit VS and perform these tasks in the Management Studio, but VS can do the job. By selecting from the VS menu bar Data>Transact-SQL Editor>New Query Connection, we get a new window to enter scripts to be run on our database.

Accessing the Transact SQL Editor in Visual Studio

We found it easier to copy and paste our scripts into this windows as we needed to run SQL statements.

Our first task at hand is to load the assembly NMath.dll into the database we have selected to work in. It is at this point we hit our first real obstacle. It turns out that in our shipping NMath.dll we are using some instructions to automatically load x86 or x64. These are illegal in the SQL CLR. We were forced to remove these instructions and build an entirely separate version of NMath.dll for SQL Server. Stepping back and looking at the big picture, we decided that this was an opportunity to also include some of the “glue” code we knew we wanted to write.

We had been looking at writing some User Defined Types (UDT) and User Defined Aggregate (UDA) functions to handle the calls into our library. It seemed to make sense to extend our libraries type definitions to be T-SQL types. The result of this approach would enable T-SQL to use NMath types such as DoubleVector and DoubleMatrix. We needed to have some way to load our new data types so we built UDAs LoadDoubleVector, LoadDoubleMatrixByRow, and LoadDoubleMatrixByColumn. After building our new, custom NMath.dll, we can then run a script to load our assembly into the database.

Before we could do this, though, we discovered that we needed some basic assemblies that SQL server had left out of their CLR. The following SQL script loads those assemblies.

Loading Supporting Assemblies to a SQL Database

We are now ready to run the following SQL script to load NMath for SQL.

Loading NMath assembly in the SQL Database

The next step is to add the reference to our project. From the main toolbar launch the Solution Explorer and right click on Reference then select Add Reference. Add the new assemblies we have loaded so far.

Adding NMath reference to the SQL/CLR project

We will now have to tell SQL about the new types and functions we have added. We will start by adding the new UDTs DoubleVector and DoubleMatrix.

SQL commands to add UDTs for LoadDoubleVector

Now that SQL knows about our new types we can add the UDA functions with the following SQL commands.

SQL Commands to add NMaths UDAs to the Database

At this point we are ready to test our library. We have chosen a problem that represents the work expected to be perform. We will create our sample datapoint tables rather than use tables from the AdventureWorks database. We will then load the data from the tables into DoubleVectors and call our library using a User Defined Function(UDF). Our UDF will be a call to library to find the function values for a curve that fits our datapoints. From our function solution we will create a table with datapoints that represent our curve function to compare against our original datapoints.

We will start by creating the UDF call to our library. From the Solution Explorer, we will add a UDF template called NMathFunction.cs.

Here is the C# code for this function call:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CenterSpace.NMath.Core;
using CenterSpace.NMath.Analysis;
 
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DoubleVector NMathFunction(DoubleVector xValues, DoubleVector yValues, DoubleVector start)
    {
        NMathFunctions.GeneralizedDoubleUnaryFunction f = AnalysisFunctions.FourParameterLogistic;
        OneVariableFunctionFitter fitter = new OneVariableFunctionFitter(f);
        return new DoubleVector(fitter.Fit(xValues, yValues, start));
    }
}

We can now tell VS to build the assembly NmathSQL.dll and use SQL to load the assembly to our database.

Loading our custom NMathFunctions to the database

In order for our UDF NmathFunction to be recognized we need to run a SQL create function command. Note that this command must be the only command in the script.

SQL Commands to add our UDF to the database

We have now loaded our NMath function call in the database that can be called by scripts. We are ready to write a script to solve the problem we described above.

We will start by using the same data from the example we did with our excel post.

Sample Data for our example

We can now build a script that uses the functionality we have built in with our library to find the solution.

-------------------------------------------------------
Declare @xv DoubleVector
Declare @yv DoubleVector
 
select @xv = dbo.LoadDoubleVector(xcol) from xvalues
select @yv = dbo.LoadDoubleVector(ycol) from yvalues
 
Declare @sPar DoubleVector
Declare @solution DoubleVector
 
set @sPar = '[ 0.1 0.1 0.1 0.1]'
 
select @solution = dbo.NMathFunction(@xv, @yv, @sPar)
 
select @solution.ToString()
 
declare @newxval float
declare @newyval float
declare @inc float
declare @a float
declare @b float
declare @c float
declare @d float
 
set @a = dbo.DVItem(@solution, 0)
set @b = dbo.DVItem(@solution, 1)
set @c = dbo.DVItem(@solution, 2)
set @d = dbo.DVItem(@solution, 3)
 
create table SolutionTBL (newxval float, newyval float)
	set @newxval = 0
 
	while @newxval < 35.10
	begin
 
	    set @newyval = @d+((@a-@d)/(1+POWER((@newxval/@c), @b)))
 
	    insert into SolutionTBL values(@newxval, @newyval)
 
	  set @newxval = (@newxval + 0.33)
	end
	select * from SolutionTBL
	go
---------------------------------------------------------------------

Here is the output from this script

Returned Solution from NMath library call

At this point we can pull data from tables, call our Math libraries, and put the solution in a table to be displayed. In production, this last script would be a stored procedure that would be run from the Management Studio. All of this work would reside on the SQL Server.

We can now move on to the Reporting Services to see how this solution could be displayed.

After launching Report Builder and establishing connection to the database. The data is accessed by setting up a dataset with a query into the table for the necessary data.

Selecting the chart data in Report Builder

From there it is merely Report Builder to build the necessary graphs. Unfortunately, the chart wizard doesn’t include scatterplots. It is best to select a chart type of Line and then run through the wizard. After your chart is set up you can change its properties to scatterplot.

Report Builder ScatterPlot

We can include our solution in the same report and generate a smooth line as we did in excel. The Report Builder is a very powerful tool which we are still learning.

Solution Fitted Line

Our results are equivilent to our previous excel post and demonstrate that the approach on the SQL Server is as accurate.

In conclusion, we have demonstrated that we can call the CenterSpace NMath libraries from SQL Server and display the results using Report Services powerful charting capabilities. We plan to work on more examples and compare how we might improve on what Analysis Services can produce. We certainly will entertain feedback on useful approaches that should be examined. As a reminder this interface is not possible with our current release of NMath 5.0 and require a “special” version of our assembly. Depending on customer interest and feedback we may or may not decide to develop a product for this interface.

CenterSpace Software will be at SC 10 in New Orleans. Our team will be delivering a presentation at the Intel booth on November 18th at noon. Our CEO, Trevor Misfeldt, and CTO, Paul Shirkey, will be presenting a customer success story on NMath and MKL.

CenterSpace Software is proud to announce the release of the Student Edition of its NMath and NMath Stats numerical libraries.

NMath Student Editions are fully functional versions of the NMath libraries that expire in one year.  Perfect for the student taking .NET programming courses focusing on math, statistics or engineering.

“More and more students are requesting our libraries to help them with their programming projects.” says CEO Trevor Misfeldt  “This new edition allows them to use the NMath libraries for a longer period of time at a very low cost.”

This year you can go back to school with NMath!

Student versions are licensed for non-commerical use only, proof of student status is required after purchase and before delivery.

CenterSpace CEO Trevor Misfeldt, and CTO Paul Shirkey, will be attending the Intel Developer Forum on Sept 13th & 14th in San Francisco. 

Any one who would like to meet with them personally during their time in the Bay Area can contact Amy to schedule an appointment.  541.896.1301