A Hello World Custom Assembly in SSAS

This is my second post i a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this new blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This one is going to be about how to extend Analysis Services (SSAS) with your own custom code.

The example in this post will be the well known Hello World example in the context of SSAS, and I trust this will illustrate the possibilities with this technique well enough, for you to apply your own solution, to your challenges.

Creating the Custom Code

First of all, we have to create the custom code. This can be done with the free tool Visual Studio (download). To get going, we create a new project  [Ctrl]+[Shift]+[N] or via the dialog below:

New Project Dialog

In the next dialog, we then select both which programming language to use for the project and also what kind of output the compiler will provide. I select a Class Library output, programmed in Visual C#, in this example. This will provide us with an assembly, which we can import directly into the SSAS Server.

Class Library Dialog

First a couple of house keeping routines. Even though this is just a tiny example, there’s no need to not do the right thing. Naming conventions should be agreed on, before diving into the code.
We begin by renaming the entire solution:

Rename Solution

I name this one ‘CustomAssembly’

Next we rename the project:

Rename Project

Finally we need to rename the Class File:

Rename Class FileI have named mine HelloWorld.cs. With this in order, we are now ready to implement our custom code.
Of course the code in this example is simple, but I trust you will be able to spot the potential of the technique.

The Class implementation looks as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CustomAssembly
{
    public static class HelloWorld
    {
        public static string GetValue()
        {
            return "Hello World";
        }
    }
}

As I said, very simple. This function only returns the same text, for every call.
In order for us to leverage this via SSAS, we need an assembly to register. We now just need to successfully build the project in Visual Studio and we are off.

Right click the Solution name in the top of the Solution Explorer ([Ctrl]+[Alt]+[L]) or press [Ctrl]+[Shift]+[B] to build.

Build SolutionThis will produce the desired output and put it in a predefined location. Right click the Project and select ‘Open Folder in File Explorer’

Open Folder in File Explorer

In the bin folder, depending on your deployment settings (Debug/Release), you will find your assembly. Remember the location or move it to a known location. Issues can occur, if you register the assembly from the same location as you build.

Registering the Assembly with SSAS

We begin by opening SQL Server Management Studio (SSMS) and connect to the server we want to test our custom code on. When connected, we can right click the Assembly collection and select to add a new Assembly.

Add New AssemblyThis opens a new dialog, in which we can specify different settings, most importantly the settings on security and impersonation. For details on this, please see this MSDN description. The different levels of permission settings:

Permission Setting Description
Safe Provides internal computation permission. This permission bucket does not assign permissions to access any of the protected resources in the .NET Framework. This is the default permission bucket for an assembly if none is specified with the PermissionSet property.
ExternalAccess Provides the same access as the Safe setting, with the additional ability to access external system resources. This permission bucket does not offer security guarantees (although it is possible to secure this scenario), but it does give reliability guarantees.
Unsafe Provides no restrictions. No security or reliability guarantees can be made for managed code running under this permission set. Any permission, even a custom permission included by the administrator, is granted to code running at this level of trust.

In my example, I will not have a dedicated account, which I would highly recommend, if you were to go into production with this.

I have selected the path where Visual Studio is configured to put the output, and I have selected Permission Setting Safe and I am impersonating the Current User.

Register Assembly Dialog

When we click OK, the dialog will close, and the collection of assemblies ion the instance will be updated, to contain our newly created custom assembly.

Assembly Registered

Run the Query

The above steps enables us to query the function created, directly from MDX as shown in the screenshot below:

Result Set

Note! The Assembly Name applied in the registration, is also the name I use in the MDX query. Had I named the Assembly MyFirstCode, the MDX would look like this:
My First CodeThink of this as an alias for the Class, the method name does not change.

Next steps

As described above, we can extend Analysis Services with all kinds of thinkable custom code. We can even send back parameters, in order to have some sort of context for the algorithm in question. We can of course also have multiple algorithms per assembly, allowing us to select specific ones, for each specific MDX query.

An assembly can, as shown above, be registered at the server level. But we can also register our assembly in the private assembly collection of any number of databases on the server. This allows us to differentiate the code base, based on the solution at hand.

Now, I am not saying custom assemblies are the solution to all of our challenges on the platform, but in the course of my career I have implemented a couple of solutions where I have used this technique. The end result was far more scalable, robust and better performing than the equivalent MDX voodoo it was set to replace.

 

Loading

Permission Scope in Analysis Services

This is my first post i a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this new blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This one is going to be about permission scope in Analysis Services (SSAS).

What this post will not be about: The how to setup basic dimension security in SSAS nor How do you manage Security.

In this post, I will highlight the difference between standard NTFS permission scope and the way SSAS handles Allowed and Denied sets when dealing with multiple roles. So if you define multiple roles on your solution, you should be on the lookout, because SSAS has some surprises.

NTFS

In NTFS, permissions are defined so that deny generally takes precedence over allow. As illustrated in below diagram, we can define an Allowed set as a sub set of the total set. The blue rectangle is to be seen as the full set.

NTFS Allowed SetAnd as we apply a Denied set, this will restrict the previous Allowed sub set, the intersection of the two sets, illustrated below: (The color indicates the finally allowed set.)

NTFS Denied Set

 SSAS

In SSAS however, the Allowed set takes precedence over Denied. So if you apply your usual NTFS logic to your dimensional security in SSAS, you may well be in for a surprise.

When no security is defined in a cube, everything is accessible to everyone. This color indicates the allowed set. (the complete rectangle)

Entire Cube Space

As soon as you introduce the first role, the cube is locked down to everyone not a member of said role.

Role w/ Allowed Set Introduced

If you then introduce restrictions in another role, you will get a different result than in the NTFS based security. The members of the role will still be able to see the full set of the Allowed set. Even though we just Denied that!

Role w/ Denied Set Introduced

Example

By creating two test roles, we can easily bring forward the, to some extend, unexpected behavior of SSAS.

Allowed Set Defined
Allowed Set Defined

As seen in Role Test 1, I have defined the Allowed set to contain Weeks 41 and 42 of the Year 2015. By browsing the Cube through SQL Server Management Studio, we can identify the Allowed set is working:

Allowed OK
Allowed OK

As I the introduce a new Role Test 2, and in that denying the week 42 of 2015, I would expect the browsing result to be only displaying only Week 41, but…

Denied Set
Denied Set

The result we get, when browsing the cube using both roles, shows all dates with data: (WTF! – Yes, you said it!)

Denied Set actually allowing everything?
Denied Set actually allowing everything!?

Fix

Clearly that was not the intention, by denying Week 42. So, how to fix the above violation to the Allowed set?

By adding an empty Allowed set ‘{}’ to the role containing the Denied set, in this case Test 2, as depicted below:

With this Empty Set ‘{}’ in place, we can browse the Allowed set again, but the Denied set does not restrict the result set.

Allow-Denied-Meh

Final Thoughts

While the way SSAS interprets the Allowed and Denied Sets may be straight forward, for simple security tasks. I think the above examples shows just how much testing one needs to do, in order to be totally sure the desired permissions are reflected in the end user experience.

As a side note, adding an empty set to the Denied Set of the first Role (Test1) does not alter the outcome.

 

Loading