Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
MSBI 49
#BI 39
BRH 39
SSRS 35
#MSBI 29
SQL Server 28
#SSRS 22
SSAS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Improving cell security performance in SSAS

Apr 25 2010 12:30AM by Jason Thomas   

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]

setting cell security

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;

Calculated member script 

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

Setting cell security with new expression

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 :)

Tags: BRH, MSBI, SSAS,


Jason Thomas
19 · 9% · 2997
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • I have CRM as data source and in that I have Set of user groups and rights. In CRM when we access data we access using filtered view and view takes care of what user can and what not. Now, I need to get CRM data into OLAP for analysis and slicing. Any idea how to implement such serurity in the cube so that user sees data pertaining to him.

    commented on Aug 1 2011 5:30PM
    singhraj4u
    2227 · 0% · 6
  • You will have to implement the same security in the cube by replicating the same set of securities in the CRM. After the OLAP layer is built, the users will not be touching the CRM data. The users will only be touching/querying the OLAP database, and hence the securities need to be replicated in the OLAP layer.

    commented on Aug 2 2011 4:46AM
    Jason Thomas
    19 · 9% · 2997
  • Thanks Jason for your input.

    commented on Aug 2 2011 4:36PM
    singhraj4u
    2227 · 0% · 6
  • Hi Jason , I want your help in implementation of cell based security in our cube . Could you please look into the below post where i have mentioned my case .

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/84704017-5ee1-4cca-bbbd-501b57d79b38

    and could you post your answer again in above msdn post , so that i would be easy to track my post . Please help me to find a solution for this problem . Waiting for you valuable response .

    commented on Apr 5 2012 2:39AM
    sivavenkat4u
    3082 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Improving cell security performance in SSAS" rated 5 out of 5 by 3 readers
Improving cell security performance in SSAS , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]