Improving cell security performance in SSAS

Reposted from Jason Thomas blog with the author's permission.

Now this one was in the coming for quite some time. All of us who have tried to implement cell security would have had some or the other woes to relate with performance. At least for me, the MDX query which I had beautifully tuned to come out in 2 sec suddenly became an embarrassment in the form of a hideously slow 2 min query the moment I switched over to a security based role. And this blog is a direct result of my quest to understand how SSAS works beneath my mean machine (yeah, my laptop is damn cool!).

Before I start with all the technical jargons and make you guys feel stranded in the desert, I would recommend reading through the following article (in case you are new to this) to have a general feel of how data security is implemented in SSAS:-

Granting User Access (SQL Server 2008 Books Online)

Now let me take you through my experiences. I have noticed that performance is not affected when I implement dimension security but the moment I implement cell security or dynamic cell security, performance takes a back seat. On closer investigation, I found out that the cornerstone for this issue is because Analysis Services (AS) applies dimension security before it evaluates the MDX script of the cube (which sounds pretty reasonable considering the fact that the expressions within the MDX script has to be evaluated within the context of the dimension security) and cell security is applied only after the MDX script is evaluated. So suppose you have some complex MDX expression specified as a part of the cell security expression, AS will have to apply this while it calculates the value of each cell at runtime. Based on this knowledge, I came up with the following approach:-

1) Handling simple Cell Security

In cell security, certain measures need to be secured from a particular set of users while they should be visible to another set of users. A very common scenario is when you want your sensitive data like profit margin to be displayed only to the top management level, and deny the access to this measure for normal employees. The way that I took this forward is detailed in one of my previous posts - Implementing measure security in SSAS 2008.

2) Handling complex Cell Security and/or Dynamic Cell Security

By complex cell security, I refer to those cases where you would need to write a complex expression for cell security. Let us say, a scenario where a certain set of users should always be blocked to see the sales of strategic countries like Afghanistan, Iraq and Palestine. Usually we implement this by specifying the following expression

not [Geography].[Country].currentmember IS [Geography].[Country].&[Iraq] AND not [Geography].[Country].currentmember IS [Geography].[Country].&[Afghanistan]

In this case, the blocked country is static but there could also be scenarios where a certain set of users should only be allowed to see the sales happening in their own country. This is known as dynamic cell security. Here, we would have to write an expression which will link the current user using the UserName function (you can get more info in this post) to the list of users in the database, and the find out the country of that user. As you can see, the resulting expression would be complex and would have to evaluated for all cells at runtime, which would clearly explain why most of us face performance problems.

To improve the performance, follow the steps below:-

1) Create a hidden calculated member called MeasureSecurity in the MDX script with 0 as default value.

CREATE MEMBER CURRENTCUBE.[Measures].MeasureSecurity

AS 0,

VISIBLE = 0

2) Write a Scope statement based on the complex security expression, and assign the value as 1.

SCOPE([Geography].[Country].&[Iraq],[Measures].[MeasureSecurity]);

THIS=1;

END SCOPE;

You can modify the Scope statement to handle any required expression.

3) Open the Role and go to Cell data tab. Assign the below expression to the cell permissions

[Measures].[MeasureSecurity] = 1

4) Deploy the changes and you would be able to see the secured measures showing #N/A in the cube browser when you are logged in using the restricted role.

This method would be faster because the complex logic for evaluating cell security has been replaced with a simple comparison. In the example given above, the gains would be insignificant but when you have complex logic involved like mapping the username to a member in the dimension or calculating the ancestor or descendants of the current member, the gains would be pretty big. As for me, this method got my query running back in 3 sec :)