Analysis Services 2005 CLR Stored Procedures

Database engine CLR support in SQL Server 2005 is very famous.  Less known is the fact that you can extend the functionality of Analysis Services 2005 with your own CLR procedures as well. Actually, you can add stored procedures in .NET or COM code. COM procedures are supported for backward compatibility, so I will show you how to write .NET procedures only.

The name »Stored Procedure« is somehow confusing. AS actually supports procedures and functions. Functions can be used in MDX or DMX Select statement. Procedures are executed with CALL statement. Both, functions and procedures, are static (shared) methods in a .NET assembly. On the contrary to SQL Server CLR objects, Visual Studio .NET does not have any templates for the AS procedures. You have to use the plain Class Library template. After you build the project, you have to register it in Analysis Services. You can add the assembly to a BI project, or you can register it with SSMS on server or database level.

Similarly to ADO.NET 2.0, which has SqlServer object model that on the server side corresponds to the SqlClient classes on the client side, ADOMD.NET includes server version of classes in the 

Microsoft.AnalysisServices.AdomdServer namespace. Maybe the most important part of this namespace is the Context class, which allows you to reference the current context in which your procedure operates.

For a beginning, let me show two very simple functions that will use the Context object to get the context of the current server and current database. I created a class library named ServerProcs; I want to register it on server level. First you have to add a reference to AdoMDServer class library; it should be listed between .NET libraries as Microsoft.AnalysisServices.AdomdServer. If it is not there, you can browse to find the C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPbinmsmgdsrv.dll file. bellow is the complete code for my new class:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

using ADOMD = Microsoft.AnalysisServices.AdomdServer;

namespace ServerProcs

{

    public class ASProcs

    {

        // Get current server name

        public static string GetSvrId()

        {

            return ADOMD.Context.CurrentServerID;

        }

        // Get current database name

        public static string GetDbName()

        {

            return ADOMD.Context.CurrentDatabaseName;

        }

    }

}

After you build the project, you have to register the assembly. In SSMS, use the Object Explorer view to open the Assemblies folder of your target server. Right-click on it and select the New Assembly option. Find your dll file and select Safe permission set. Permission sets are similar to SQL Server permission sets for CLR assemblies: Safe, External access and Unsafe (Unrestricted in AS terminology). Now you can start using your functions. For example, I created a new DMX query in the Adventure Works DW demo UDM project:

SELECT ServerProcs.GetSvrId(), ServerProcs.GetDbName()

  FROM [Customer Clusters];

That’s it for the start. In next two blogs I will create two useful procedures.