Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

SSAS #28 – Setup to Learn SSAS Stored Procedures

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.

clip_image001

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.

clip_image002

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\.

clip_image003

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.

clip_image004

Run the first ASSP Like()

The Like() stored procedure is simple enough.

clip_image005

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….


Comments

Leave a comment on the original post [bisherryli.wordpress.com, opens in a new window]

Loading comments...