Custom Security in SQL Server Analysis Services 2005/2008

Sometime you might be stuck in a situation for Analysis Services where you have to pull of security information from an external system to get the information about dimension or cell security. Implementing that is not that hard as you might think of. Though there are some things to keep in mind regarding the reference in the BOL.

What type of custom code can I use in Analysis Services ?

In common you can use any CLR language to code you assemblies as well as COM languages like VB or C++ to create your assemblies (although COM will be deprecated in SQL Server 2008).

How do I register the class in Analysis Service ?

After creating your static methods for accessing your logic within your custom class you can register them in your Analysis Service (project) (Make sure you pick the right permissions for your purposes)

How do I call the custom method ?  

You can call the method of the class using the following naming convention:

 AssemblyName.FullClassName.MethodName(Argument1, Argument2, ...)

For every person doing normally non-related developing stuff the FullyClassName is (Namespace.Classname not just ClassName), making an Assembly with the namespace MyNamespace to be called as (the BOL is a bit unclear on that):

AssemblyName.[MyNamespace.FullClassName].MethodName(Argument1, Argument2, ...)

What type should I return to AS ?

Remember to return directly the appropiate type to Analysis Services.If you are returning a string and use the STRTOSET() Function to convert it to a set, this might cause performance degradation and bring you to the limit of the function in terms of length. Better use the .ToSet() method from the Microsoft.AnalysisServices.AdomdServer.Set class.

-Jens