http://www.sqlservercentral.com/blogs/sherry-lis-bi-corner/2012/10/16/ssas-28-setup-to-learn-ssas-stored-procedures/

Printed 2014/12/20 07:54PM

SSAS #28 – Setup to Learn SSAS Stored Procedures

By Sherry Li's BI Corner, 2012/10/16

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.

clip_image002

Referencing ADOMD.NET in a C# Class Library Project

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

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



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.