Having worked with SSAS for a while and done some reporting with MDX queries, I started to explore opportunities to write SSAS “stored procedures”. I stumbled on a few blocks along the way to even set up the development environment properly before I got chance to identify scenarios to justify the effort. 

Learn from Expert – Analysis Services Stored Procedure Project on CodePlex

I find that the best place to start is to learn from expert. The above project has been around for quite a while, and is a wonderful place to start. But gluing all the pieces together is still tough.

Here are the notes from my exploration.

What are Assemblies on Analysis Services?

Analysis Services stored procedures are not written in MDX (as TSQL stored procedures written in TSQL). They are really “functions” that are associated with a common language runtime (CLR) or Component Object Model (COM) class. The class is in the form of a dynamic link library (DLL) , and needs to be registered as an assembly on the Analysis Services server or in an Analysis Services database.

Register the DLL file on the AS Server

Once I have the DLL file, I can go to the the AS Server, and register it as a server assembly.

A few things about the registration.

• As for the Type of assembly, we have choice of either.NET Assembly or COM DLL. Since the ASSP project used C#, choose .NET Assembly. Although Analysis Services supports both COM and CLR assemblies, CLR assemblies are recommended because of the enhanced security available to CLR assemblies. See Using Stored Procedures (MDX).
• Assembly Name: ASSP is the namespace of the ASSP project, and all the functions will need to be prefixed with ASSP for execution.
• Permissions: I am not exactly certain what each level of permissions entail, but certain functions in the ASSP project need “Unrestricted” permission.
• Impersonation should be left as Default.

Referencing ADOMD.NET in a C# Class Library Project

Creating a Class Library project in C# is straightforward, except the References setting.

• ADOMD.NET has exposed both the Analysis Services server and client components through ADOMD.NET.
• Microsoft.AnalysisServices.AdomdClient: this is the namespace in ADOMD.NET that allows all client side programming against the AS. The file Microsoft.AnalysisServices.AdomdClient.dll was found under C:\Program Files\Microsoft.NET\ADOMD.NET\90.
• Microsoft.AnalysisServices.AdomdServer: this is the namespace in ADOMD.NET that allows all server side programming against the AS. The DLL file name msmgdsrv.dll is found on my PC under C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\.

Versions of Target .NET Framework

Both the client and server references of ADOMD.NET  on my PC are version 9. After a few trial and error, I found that I can set the Target .NET Framework to 2.0, 3.0 or 3.5 (except 4.0), and the DLL worked for the AS 2008 R2.

Run the first ASSP Like()

The Like() stored procedure is simple enough.

So much work….

but I still haven’t found a scenario where using an Analysis Services stored procedure is justified….but I am pretty sure it’s worth the effort to set up a development environment, just in case….