Implement SSAS MD cell security using dimension security with blazing performance

SQL Server Analysis Services (SSAS) Multidimensional (MD) is a great product, and in my opinion it’s still the only real option to go for when building complex enterprise BI solutions. It’s still very fast when implemented correctly and it’s mature and therefore very stable.

 

The only real downside is cell security, which, in my opinion, is useless. It makes performance drop dramatically because it evaluates security cell-by-cell. I have seen reports that run in a few seconds without cell security taking 20 minutes with cell security implemented! Try to explain that to your customer.. It’s obvious that you can’t.

 

Quite some workarounds exist for quite a while:

 

• Make your measures invisible and create MDX calculations that either show or hide the measure value based on a dummy dimension. Drawbacks are the measure is hidden and not really secured and you need to create dummy dimensions/attributes and maintain them. http://www.sqljason.com/2010/04/implementing-measure-security-in-ssas.html
• SSAS offers us the “Measures Dimension” which give you the possibility to secure measures like dimension members. Great, this is just what we need, but implementing it and creating MDX calculations based on the secured measures will give you errors for SSAS roles that do not have access to these measures. This is caused by the fact that the MDX script is executed after the security has been implemented. So if a user that doesn’t have access to a measure (set by dimension security) tries to connect to the cube while the MDX script contains a reference to this secured measure, this raises an error.
• Create a hidden dimension on which you apply dimension security like described here: http://cwebbbi.wordpress.com/2011/12/22/replacing-cell-security-with-dimension-security/ Unfortunately this doesn’t work for measures.

 

For a few years I’ve always implemented a workaround that uses the measures dimension. To prevent errors in the MDX script as described by option B above, I’ve added IsError() checks around all my calculation parts that could raise an error. For example, a simple calculation like Quantity * Price, where the price measure could be secured, looks like this:

 

CREATE MEMBER CURRENTCUBE .[Measures].[Amount] AS
  IIf (
    IsError ( [Measures].[Price] ),
    NULL,
    [Measures].[Quantity] * [Measures].[Price]
  ),
  NON_EMPTY_BEHAVIOR =
  {
    IIf (
      IsError ( [Measures].[Price] ),
      NULL,
      [Measures].[Price]
    )
  } ;

 

This calculation would not raise an error but NULL if a user doesn’t have permission to the Price measure. Quite straightforward, only trick here is the IsError() check in the NON_EMPTY_BEHAVIOR, of course you would have to do this in the script view of your SSAS calculations tab. In the form view this will look a bit strange but it doesn’t cause any errors: 

 

 

Using this approach you are able to implement measure security using the dimension security while you are still able to create MDX calculations with NON_EMPTY_BEHAVIOR based on these secured measures. This made the report I talked about before to run in 5 seconds instead of the 20 minutes. I’ve used this approach for quite some years now, and it has always been sufficient.

 

At the moment I’m working on a very big enterprise cube that contains almost 100 dimensions, 25 measure groups and millions of records. I’ve also implemented measure security like this and after a while I noticed some really heavy queries (reports) took quite some time, for example 30 seconds or 1 minute. Reason enough to re-think this approach. When I was at the SQL Server Days in Belgium last year, I’ve discussed this approach with Chris Webb. Of course he could understand what I was doing here and we agreed to email about this approach later on. Chris emailed me about a blogpost of him from some time ago: http://cwebbbi.wordpress.com/2008/01/22/dimension-security-tips-tricks-and-problems/. In this post Chris explains how to create secured calculations while using dimension security by using named sets and scope statements:

 

CREATE MEMBER CURRENTCUBE .Measures.Test AS
  NULL ;

 

CREATE SET myset1 AS
  IIf (
    IsError ( StrToMember ( “Measures.[Internet Sales Amount]” ) ),
    { },
    { Measures.Test }
  ) ;

 

SCOPE ( myset1 ) ;
This = Measures.[Internet Sales Amount] ;
END SCOPE ;

 

CREATE SET myset2 AS
  IIf (
    IsError ( StrToMember ( “Measures.[Internet Sales Amount]” ) ),
    { Measures.[Internet Tax Amount] },
    { Measures.[Internet Sales Amount], Measures.[Internet Tax Amount] }
  ) ;

 

SCOPE ( myset2 ) ;
This = Measures.CurrentMember * 2 ;
END SCOPE ;

 

I did not know about this approach, so I tried it out. To be able to get good test results I used a very heavy query that hopefully nobody would ever try: a calculation over all sales data (millions of records) against all customers, all products and all time.  Unfortunately, the results were not very pleasing yet:

 

1. Calculation with IIF/IsError checks in both the calculation part and the non empty part: ran in 50 seconds.

 

2. Calculation with named set/scope approach: ran longer than 5 minutes, after that I stopped the query.

 

So Chris and I emailed again and I was wondering if the lack of NON_EMPTY_BEHAVIOR (NEB) could be the cause of the slow performance of the named set/scope approach. Chris said that since SSAS 2008 the usage of NEB was not necessary anymore, something I’ve heard about before. I always had the idea adding NEB did make a difference in some cases so I never stopped using it. So I kind of merged Chris’ and mine approach, adding the NEB using an IsError() function and I replaced the StrToMember check with a direct reference to the measure in the named sets because in my opinion that part was not necessary:     

IsError ( StrToMember ( “Measures.[Internet Sales Amount]” ) ) à became: Measures.[Internet Sales Amount].

 

The result was almost unbelievable, the query now took not 5 minutes, not 50 seconds but only 2 seconds!! So adding NEB still can make a huge difference sometimes! After finding out about this, I contacted Chris again and he was also very surprised by the result.

 

I’ve created an MDX calculation template with some explanation added as comments. The big performance gain is achieved because the IsError() check doesn’t need to be executed every time the calculation is executed because it’s already executed when the static named set is created (one single time), afterwards this value just seems to be reused. I think the same applies for the IsError() check in the NEB, it seems it’s only executed once and is reused. Anyway, the result is, your MDX calculations are just as fast with security applied as without. Something that was not possible for a long time for me and I guess everybody else too. Just use the template below as a standard for creating your calculations and benefit from this solution:

 

–Calculation Template:

—————————————-

   ———-1: CHECKED CALCULATION: The secured “end product” used in other calculations———-

CREATE MEMBER CURRENTCUBE.[Measures].[Quantity_CHECKED] AS

  NULL

  , VISIBLE = 0 ;

 

   ———-2: CHECK NAMED SET: Actual permission check performed here———-  

CREATE SET [Quantity_CHECK] AS

  IIf (

   IsError ( [Measures].[Quantity] )

   , { }

   , { [Measures].[Quantity_CHECKED] }

  ) ;

   ———-3: SCOPE: Assignment of either the measure or “nothing” to CHECKED calculation (1)———-  

SCOPE ( [Quantity_CHECK] ) ;

    This = [Measures].[Quantity] ;

END SCOPE ;

   ———-4: Second secure calculation created here———-  

CREATE MEMBER CURRENTCUBE.[Measures].[Price_CHECKED] AS

  NULL

  , VISIBLE = 0 ;

CREATE SET [Price_CHECK] AS 

  IIf (

   IsError ( [Measures].[Price] )

   , { }

   , { Measures.[Price_CHECKED] }

  ) ;

SCOPE ( [Price_CHECK] ) ;

    This = [Measures].[Price] ;

END SCOPE ;

 

   ———-5: Calculation based on the secure calculations.

   ———-Non_Empty_Behavior set with IIf/IsError on measure. Using calculations inside NEB is not possible———-

CREATE MEMBER CURRENTCUBE.[Measures].[Sales Amount] AS

  [Measures].[Quantity_CHECKED] * [Measures].[Price_CHECKED]

  , NON_EMPTY_BEHAVIOR =

  {

   IIf (

    IsError ( [Measures].[Price] )

    , NULL

    , { [Measures].[Price] }

   )

  }

  , VISIBLE = 1 ;

   ——————————————-

 

Your calculations tab in SSAS will look like:

When I was investigating the inner working of the first part of the script I’ve added some comments to make it easy for myself to remember. I guess it’s also useful for anyone that want to know what’s really happening here:  

CREATE MEMBER CURRENTCUBE.[Measures].[Quantity_CHECKED] –> Calculation is initially NULL and will be filled based on user rights later on.

AS NULL,

VISIBLE = 0; –> Make it invisible as this is a pure technical calculation that should not be used by end users. End users can use the measure this calculation is based on, if they have permission (set by dimension security on the Measures dimension).

CREATE SET [Quantity_CHECK] AS –> Named Set will be used to perform the actual check for user rights, as this is a static named set this check will only be executed one time, at initialization, and will be reused.  

IIf(IsError([Measures].[Quantity]) –> An IsError on the measure that needs to be checked will raise an error if a particular user has no rights on it because in that case the measure will simply not exist.

, {} –> If an error was raised by the IsError function set the value of this set to nothing: {}

, {[Measures].[Quantity_CHECKED]}); –> If no error was raised the user has rights to access the measure, in this case set the value of the set to the required CHECKED calculation created in the previous step.

SCOPE([Quantity_CHECK]);  –> If in the Scope of the CHECK calculation, either the CHECK calculation can be {} (Nothing) or it can be the CHECKED calculation (based on user rights).  

This=[Measures].[Quantity]; –> Assign the measure to This. This can be the CHECK calculation if the user has rights which will pass the measure through to the initial CHECKED calculation or it can be {}/Nothing which will pass the value of the measure to nothing instead of to the CHECKED calculation.

END SCOPE; — So this Scope function either passes the measure to the CHECKED calculation or to nothing (the empty set {})