Chris Webb's BI Blog

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

Denying access to an entire level with dimension security

with 16 comments

Most of the time when you’re using dimension security with SSAS, you’re slicing a hierarchy vertically: for example, on a Geography dimension you only want members of a given role to see just one Country on your Country hierarchy. Occasionally, though, you may want to slice a hierarchy vertically: on a Time hierarchy, you may want to allow members of a role to drill down from Year to Month, but not see Date level data. This is also possible; a customer recently asked me how to do it so I thought I’d write up the explanation since it’s very easy to do.

Let’s take the Calendar hierarchy on the Date dimension in Adventure Works as an example. When you browse it in the cube browser in BIDS, you’ll see a hierarchy that goes down from Year at the top to Date at the bottom:

image

Now, let’s create a role that stops users from drilling down beyond Month. Create a new role and go to the Dimension Data tab, and select the Date dimension on the Adventure Works cube (note not the Date dimension further up the list – that’s the Database dimension, we want the Cube dimension, the instance of the Date dimension inside the cube):

image

Then select the Date attribute hierarchy (the Date attribute hierarchy is used as the bottom level of the Calendar user hierarchy, so by securing it you’re also securing all user hierarchies that it appears in) and select the Deselect All Members radio button.

image

You can then deploy the project with this new role, and go to the Cube Browser tab, click the Change User button and select the new role in the Security Context dialog to test it:

image

You will then be able to see that you can no longer drill down below the Month level in either the metadata pane or the pivot table:

image

Written by Chris Webb

June 22, 2011 at 11:02 pm

Posted in Analysis Services

Tagged with

16 Responses

Subscribe to comments with RSS.

  1. Nice Article Chris! Thanks

    Could this not be achieved by using an MDX statement in the “Denied member set” section of the “Advanced” tab? Something like {[Time].[Date].Members} ?

    Cheers
    Craig

    Craig Bryden

    June 23, 2011 at 12:23 am

    • Absolutely – it’s just easier to use the UI in this case. You need to use an empty set {} in the Allowed Member Set.

      Chris Webb

      June 23, 2011 at 11:50 am

  2. Good tip – thanks.

    Tim Mitchell

    June 23, 2011 at 3:37 pm

  3. [...] Chris Webb says that most of the time when you’re using dimension security with SSAS, you’re slicing a hierarchy vertically. [...]

  4. [...] to control  level access to information in a cube (by roles in the BI server). All the blog is [...]

  5. Nice article Chris!

    I have implemented a security base on organisations groups (ie different companies) and role groups (sales, purchase, etc) in a single cube with different perspectives. The organisation groups have a ‘deselect all members’ for all dimensions, except for their company. The role groups have limiteded the set of specific dimensions to the available companies. This is almost working … at least the general concept works as expected. But …

    It takes at least 10 minutes to connect to the cube, as if the cube runs all possible queries for the combined roles first. I’m not very sure where to search for the cause, as the admin account has no trouble connecting.

    In Excel 2010, I can’t drill down certain dimensions when I put them in the filter section, allthough I can when I use them as a row or column label. For example the date dimension, I now have to show it as a column label, select the period I need and then drag it back to the filter section ..

    I’m sure it’s something I will figure out in the end, but I was curious if you have had any experience with security issues like these.

    Regards, Martijn

    Martijn Sandbergen

    October 6, 2011 at 9:33 am

  6. Can we implement this for intermediate levels? if yes, will it affect all levels below the level for which I have removed the access?

    Prakash

    April 2, 2013 at 5:50 pm

    • I’m not sure (I haven’t tested it) but I suspect it won’t work for intermediate levels

      Chris Webb

      April 2, 2013 at 6:42 pm

  7. Hi, I implemented this for a dimension attribute and it seemed to work. When testing attribute members in SSMS, I could not filter on the attribute I’ve done “Deselect all members”, but I could filter on all the other ones. Yey, I was happy! But when testing in Excel, I can’t access the key level at all. The dimension is a Customer dimension, and I want to make sure that members of a specific role is not allowed to filter on Customer Advisor. Unfortunately, when I want to look at the Customers in the dimension now, I don’t get anything at all in Excel. I can use the hierarchies I’ve built (and all other attributes), but once I reach the lowest level, I don’t get any data. Any suggestions on how to be able to show all customer attributes Except the Customer Advisor?

    Kaisa Lervik (gattolina)

    September 24, 2013 at 11:01 am

    • To be clear, Customer Advisor is not the key attribute, and you want to remove access to Customer Advisor but let people see the key attribute?

      Chris Webb

      September 24, 2013 at 12:39 pm

      • Yes. Customer is the Key, Customer Advisor is an attribute.

        Kaisa Lervik (gattolina)

        September 24, 2013 at 12:54 pm

      • Hmm, I don’t know, I’d have to do some experiments. Have you tried adding all the members of the Customer attribute to the Allowed set on the Customer attribute?

        Chris Webb

        September 25, 2013 at 9:26 am

      • Regarding adding all the members of the Customer attribute: I tried as you suggested, but no luck. For once it seems that SSAS doesn’t let an “Allow” override a “Deny”.
        What happens in SSMS is that I can view the Customers, but if I try to do “Show all properties in report”, I get the following error message (which figures):
        The query could not be processed:
        o Errors in the OLAP storage engine: Either you do not have permission to access the specified member, or the specified member does not exist.

        Kaisa Lervik

        September 26, 2013 at 8:32 am

      • Oh well… I’ll try to take a look and see if I can make this work sometime over the next few days.

        Chris Webb

        September 26, 2013 at 9:06 pm

    • So to be clear, Customer Advisor is not the key attribute, and you want people to be able to see the key attribute but not Customer Advisor?

      Chris Webb

      September 24, 2013 at 12:40 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,131 other followers

%d bloggers like this: