I’ve recently had the pleasure of working with Microsoft’s 2008 BI stack. I say pleasure because Microsoft has really pulled out the stops.  The architecture is sound, the performance is outstanding.  Gartner has ranked Microsoft’s BI offering in the top quartile of providers.  This puts them in the same league as Cognos (Oracle), Business Objects (SAP) and usual players in the space.  I have used Cognos and Business Objects in the past and have had the opportunity to use or see most of the other vendor offerings.   Each Vendor offers tradeoffs.  Below is my experience with Microsoft’s BI solution up to this point.

Microsoft’s BI offering is centered around SQL Server.  In this case my team and I used Microsoft server 2008 and SQL Server Enterprise 2008.  I wish I could tell you it was because we wanted to take advantage of the performance improvements, However our logic was simpler.  If you’re going to build a new stack just use the latest and greatest.  The most important thing to the team was to use SQL Server 2008 so that we could take advantage of what R2 was going to provide.

The first thing that really struck us was the modular nature of Microsoft’s strategy.  SQL Server is the core, but in terms of delivery Microsoft relies heavily on the fact that most business users utilize Microsoft Office as their main office productivity suite.  It’s a safe bet considering that Microsoft has an incredible penetration rate in this space.  However, external facing customers might prove to be challenging in terms of delivery.  We chose to put a proxy between our Reporting Services implementation and the outside world.  This allowed us to implement URL rewriting and offer another level of security.  Unlike Cognos, which offers an easy to implement portlet we had to custom make a solution for implementation into our ALUI portal engine.  Of course all of this can be solved if you just use Microsoft Share Point.  This was not an option for us.

Typically I’d view this module nature as being a hindrance, or even a lack of integration features.  What we ended up finding is this is actually a strength of the Microsoft BI stack.  Microsoft has a great API/services to access all the functionality.   Building out a custom solution for integration was not that bad compared to the limited options for customization that other vendors offer.  For example, Users can natively access cubes and analysis right from Excel 2007.  However, because the API was so rich, we chose to write our own ‘Excel tab’ to make accessing and running analysis functions as brain dead as possible.  A solution that users appreciate.

So far, as a strategic play, Microsoft’s BI offering is proving to be a good investment.

I had a chance to play around with this new feature in SQL 2005 the other day.  After reading a few articles here is the quick and dirty on how to implement.

A quick and dirty guide as to when is the best time to use:

http://www.developer.com/net/net/article.php/3528601

1) Create a Class project in Visual Studio

2) Create a new class and cut and paste the following code:

using System;
using System.Collections.Generic;
using System.Text;

namespace SampleCLR
{
    public class CLRTestClass
    {

        public static string Freak(string name)
        {
            return name.ToString() + " Is a FREAK!!!";

        }
    }
}

3) Compile.

4) Next

Register the assembly (in SQL 2005) by right clicking ‘Assemblies’ > ‘New Assembly’ under Programmability.

5) Run these scripts:

  SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION dbo.clrWhosFreak(  @name as nvarchar(200) ) RETURNS nvarchar(4000)  AS EXTERNAL NAME [SampleCLR].[SampleCLR.CLRTestClass].[Freak]  

sp_configure 'clr enabled', 1

go

reconfigure

go