Analysis Services · Security

Replacing Cell Security with Dimension Security

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


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
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.

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

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

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:

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.

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

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:

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

, {nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )})
, 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.

8 thoughts on “Replacing Cell Security with Dimension Security

  1. 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 .

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      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.

  2. 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?
    [Business Unit].[Level1].[Level1].members,
    strtomember(“[Sales Person].[Sales Person].[” + UserName() + “]”),
    [Measures].[Bridge Sales Person Count]

    Thank you for your time!

  3. Hi Chris,

    I have a similar requirement. Tried using your approach but was not successful.
    Here is the issue in details.

    Problem Statement – Apply dimension security on a dependent dimension

    We are trying to implement dimension security on [CompensationItem] which is having dependency on [Scenario].

    So an user should be having access to only those members of [CompensationItem] which are mapped to corresponding [Scenario] members .

    Mapping table created with the following attributes (Mapping Table : olap_user_comp_security) :

    [CompensationItem]. ID

    [Scenario]. ID


    Dimension Tables :

    HR_DIM_COMP – CompensationItem

    OLAP_SCENARIO – Scenario

    OLAP_USER – User

    Tried below approach :

    Current Implementation :

    1. Added mapping table to DataSource View

    2. Defined the relationships as described above.

    3. Measure Group created in the cube with the Mapping/Bridge table

    4. Added the relationships in the Dimension Usage Tab

    5. In the security Roles, added the below condition in the allowed member set :

    Exists([Dimension B].[Attribute Name].Members, CrossJoin(StrToMember(CustomData()),[Dimension A].CurrentMember), “[Mapping Table]”)

    6. Checked Enable Visual Tools Checkbox

    Issue :

    Security is not evaluated based on [Scenario], its only evaluated with respect to the user data passed in customdata(user data) connectionstring.

    Security should be evaluated based on user and scenario both for CompensationItem.

    1. Hello Sudi,
      Please can you help me to do the dynamic security in the cube ssas multidimensionnal, i have using CustomData but it not works .

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.