Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Replacing Cell Security with Dimension Security

with 5 comments

Cell security, as you probably know, is best avoided because of the impact it can have on query performance; dimension security is much easier to work with and its performance impact is much less. Luckily even when it seems as though cell security is the only option, in at least some scenarios with a bit of extra cube modelling work dimension security can still do the job. Here’s how…

Let me give you an example that I worked on with a customer recently, translated to Adventure Works. The Internet Sales measure group in the Adventure Works cube contains three role-playing dimensions based on the Date dimension: Date (which represents the Order Date), Ship Date and Delivery Date. Let’s imagine that we want to implement dynamic security so that a user can only see data if either the Date or the Ship Date is in one particular year. For example if a user has access to the year 2003 they should be able to see data if the year on the Date dimension was 2003 or if the year on the Ship Date dimension was 2003, as shown in the cells highlighted in the following screenshot (note that the grand totals should also reflect only values from the two 2003 years as well, so on the last row below we should see a grand total of $387662.64 for the CY 2004 column):

image

How can dimension security be used to implement this? The trick is that it can’t without creating a new, hidden dimension to apply security to. To create this dimension, go to the DSV and create a new named query called DimSecurity with the following SQL:

select distinct OrderDateKey, ShipDateKey
, d.CalendarYear as OrderDateYear, s.CalendarYear as ShipDateYear
from
dbo.FactInternetSales inner join DimDate d
on OrderDateKey = d.DateKey
inner join DimDate s
on ShipDateKey = s.DateKey

This gets all the distinct combinations of order date and ship date from the fact table plus their related calendar years. Obviously this exact query might not be practical on a large fact table, but you get the idea – in order to apply dimension security to the range of cells we want, we need to create a new dimension designed so that its members can be mapped onto those cells.

Next, create an SSAS dimension from this table as follows, with three attributes:

  • A key attribute called OrderShipDate that has a composite key based on the OrderDateKey and ShipDateKey columns. I used the OrderDateKey column as the name column, but that’s not too important because this dimension will be hidden anyway. This attribute represents the distinct combination of order date and ship date.

image

  • Two other attributes, Order Date Year and Ship Date Year, based on the OrderDateYear and ShipDateYear columns.

image

This new dimension should now be added to the Adventure Works cube and a relationship added with the Internet Sales measure group as follows:

image

You can now set this dimension’s Visible property to False in the Cube Structure tab of the Cube Editor.

Then create a new role, grant it access to the Adventure Works cube, go to the Dimension Data tab, select the Security dimension and the OrderShipDate attribute and go to the Advanced tab. Then enter the following MDX in the Allowed Member Set box:

union(
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS, {[Security].[Order Date Year].&[2003]})
,
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS, {[Security].[Ship Date Year].&[2003]})
)

This grants access to all combinations of Order Date and Ship Date – all the members on the OrderShipDate hierarchy – that are either in the Order Date year 2003 or the Ship Date year 2003. Don’t forget to check the Enable Visual Totals box too.

image

Deploy then go to the cube browser and test the role. You should see the following results:

image

So we have the basic role working, but how can we make it dynamic? We don’t want to create a factless fact table that contains rows for every combination of Order Date and Ship Date because that could get very large very quickly; we only want to grant access at the year level.

Going back to the DSV, create a new named query called FactSecurity using the following SQL (inserting your own username as appropriate):

SELECT        ‘Mydomain\Myusername’ AS UName, 2003 AS CalendarYear

This will be our factless fact table that grants a user access to the year 2003. Then build a new dimension called User from the UName column to give you a dimension containing all your users, as you would in any normal dynamic security implementation; also build a new measure group in the Adventure Works cube from this table and give it a regular relationship with the User dimension and a regular relationship with the Security dimension at the OrderDateYear granularity:

image

Process, then go back to the role and change the MDX as follows:

union(
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS
, {nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )})
,
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS
, generate({nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )}
, {linkmember([Security].[Order Date Year].currentmember, [Security].[Ship Date Year])}
)
)
)

This is basically the same as the previous expression but it’s now dynamic and it now supports securing more than one year in the FactSecurity measure group for a single user. Let’s look at parts of this expression:

The following expression returns the set of members on the Order Date Year attribute of the Security dimension that the current user has access to:

nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )

We can use this directly in the first set passed into the Union function, but to find the equivalent Ship Date Years for each Order Date Year we need to use a combination of the Generate and LinkMember functions, as follows:

generate({nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )}
, {linkmember([Security].[Order Date Year].currentmember, [Security].[Ship Date Year])})

This takes the set returned by the previous expression and then loops over it using Generate; for each member in the set we then find the equivalent Ship Date Year by using LinkMember.

In summary, while this might seem quite complicated the MDX used for the equivalent cell security implementation would be equally bad and the performance overhead much worse, so this is a very useful technique to have in your toolkit. Thanks are due to Peter Holzner, Ramon Mueller, Beat Stuenzi, Chris Fleming and Adam Widi for working through all this with me the other week.

Written by Chris Webb

December 22, 2011 at 9:31 pm

5 Responses

Subscribe to comments with RSS.

  1. Chris,

    Thanks so much for putting this post together, this is perfect. Really appreciate it! Happy holidays.

    Mike Pugh

    December 27, 2011 at 3:44 pm

  2. Hi Chris ,
    Nice post .Could you please tell me why did you use a fact less table . In real time the dimension “Security” would be mapped to a fact , we can directly use a measure from that fact instead of taking measure from fact less fact table .
    Could we change the above logic using Referenced dimension . as below
    the dimension “User” would be mapped to dimension “Security” using referenced relation and this dimension “Security” would be mapped to fact “InternetSales” .
    Then the MDX would be as below

    nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
    , (strtomember(“[User].[Security User Name].&[" + Username() + "]“, constrained)
    , [Measures].[InternetSales]) )

    Can we do like this ,as this would elimate creating a new fact table .

    Rajesh

    January 3, 2012 at 7:23 am

    • I used a factless fact table to support the scenario where there is a many-to-many relationship between users and years; if one user has access to multiple years and one year can be seen by multiple users, it’s the only way of modelling the relationship properly.

      Chris Webb

      January 3, 2012 at 9:58 am

  3. [...] NB: On aurait aussi pu faire de la Cell Security mais euh… non… En fait. NON. Si jamais un jour une envie de Cell Security vous gratouille, rappellez vous que c’est antiperformant, et relisez Chris Webb [...]

  4. HI Chris,
    Could you please give me your advice on my dynamic dimension security problem below?
    I have a self-reference entity called BusinessUnit. I flattened it to a 6-level dimension and created a user hierarchy of 6 levels. Theoretically the number of layers do not grow. It has many-to-many relationship with SalesPerson dimension. The requirement is a sales person can only see the sales amount of the BusinessUnits the SalesPerson owns or manages. The BusinessUnits that a Sales Person owns or manages can be at any level of the BusinessUnit dimension.
    Do I have to create 6 measure groups over the SalesPerson_DimBusinessUnit relationship, one for each level to do dynamic Dimension data Security? If I use one measures group and specify the similar MDX query like below at all 6 levels for Allowed Member” set, will this work?
    nonempty
    (
    [Business Unit].[Level1].[Level1].members,
    (
    strtomember(“[Sales Person].[Sales Person].[" + UserName() + "]“),
    [Measures].[Bridge Sales Person Count]
    )
    )

    Thank you for your time!

    hxy0135

    May 18, 2013 at 12:55 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers

%d bloggers like this: