Chris Webb's BI Blog

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

Denying access to an entire level with dimension security

with 32 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

32 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

  8. Hi Chris, I have an attribute for which I defined some member properties as described in this article http://www.bidn.com/blogs/DustinRyan/bidn-blog/2087/defining-member-properties-for-an-attribute-in-ssas-2008. Is it possible to create a role for which the access to all these member properties is restricted? I found nothing on this on google, hope you’ll be able to enlighten me. Thanks

    alina

    February 20, 2015 at 12:55 pm

    • Hi Alina, unfortunately you can’t use dimension security to restrict access to attributes whose AttributeHierarchyEnabled property is set to false. Remember that you don’t have to set AttributeHierarchyEnabled=false to see member properties though – they are always there when you set up attribute relationships – so maybe you could leave AttributeHierarchyEnabled as true, set the AttributeHierarchyVisible property to false instead and then use dimension security?

      Chris Webb

      February 21, 2015 at 4:24 pm

      • I use AttributeHierarchyEnabled=false hoping (it was not tested in prod yet) that it will improve the performance on some reports (pivot tables in excel) which are running out of memory when i display multiple attributes of this dimension combined with other dimensions. the cube browsing works fine (maybe a little slow, but works) when dragging the same columns, but the excel generates some complicated mdx behind, which as far as i know cannot be changed, and triggers the run out of memory error. but if it’s impossible to put security on them, then I definitely have to roll this back and find another solution for improving the performance. thanks for the help! i’m glad i finally asked you because i already lost 2 days trying to find smth regarding this on google :)

        alina

        February 22, 2015 at 10:11 am

      • Setting AttributeHierarchyEnabled=False won’t improve performance on its own. What might change performance is making your end users select certain attributes as properties rather than dragging them into the PivotTable, but they can do this when AttributeHierarchyEnabled=True too. So you don’t need to look for another solution! You can leave AttributeHierarchyEnabled=True, set AttributeHierarchyVisible=False, use Dimension Security, and then users will have to display these attributes as properties.

        Chris Webb

        February 22, 2015 at 10:29 am

  9. i think i already tried that about a year ago and it didn’t work, and now, that i have to tackle this issue again, after some search on the net i found that article mentioned initially and i thought this additional setting – AttributeHierarchyEnabled=False (which implies they cannot be sliced and diced on) would help. i will try it again, maybe i missed something last time. thanks again for the help!

    alina

    February 22, 2015 at 10:50 am

  10. an update for the issue above: assuming i have attributes FullName and Address; for Address, I set AttributeHierarchyEnabled=True, AttributeHierarchyVisible=False (so they cannot drag it, but just display it as a property);in attribute relationship the Address is related to FullName. I want to create a role that has access to FullName, but not to Address. But it doesn’t work. I go to Dimension Data tab. in the Cube dimension, attribute Address doesn’t appear as it is set up to False, so I can add the security on the Database dimension only (which is fine, as this is the only cube on this db). I deselect all members for FullName. Then I test the modification and no change: i see the values in the Address property as well. Just to be sure that setting the security only on the database dimension works without setting it on the cube dimension as well, I made the same change on an attribute which has AttributeHierarchyVisible=true by deselecting all its members in the database dim only and this one worked.
    So is it possible that the security cannot be applied on member properties? Because, as I mentioned in a previous post, there is not one article on google that describes smth like that – or at least I couldn’t find one….

    alina

    February 23, 2015 at 1:52 pm

    • Have you tried setting security on the Address attribute while AttributeHierarchyVisible=true, then setting the property to false after that?

      Chris Webb

      February 23, 2015 at 3:08 pm

      • tried – not working. I set the security on Address while having AttributeHierarchyVisible=true – works fine. without trying to make it invisible yet, if I create the relationship between FullName and Address (second related to first), after processing the cube the access to FullName gets restricted as well. So if I have an attribute with restricted access and I want to add it as a property to another attribute, the restriction gets applied automatically to the source attribute as well. I already tested and the other attributes of the dimension don;t get affected (expected behavior, but wanted to double check)

        alina

        February 24, 2015 at 11:58 am

      • Are you sure you have the attribute relationship the right way round? As the blog post above shows, it should be possible to deny access to everything on Address but see everything on FullName.

        Chris Webb

        February 24, 2015 at 1:26 pm

  11. yes, i am very sure. the Attribute Relationships tab shows that Fullname is the source attribute and Address is the related attribute. The blog above is about security applied vertically on the dimension, while here is not the case. Address and Fullname are not part of an hierarchy; Address is just a property, so it;s somehow ‘horizontally’. And I don;t want them to be part of a hierarchy as I already tested the hierarchy solution performance-wise and it does not help.

    alina

    February 24, 2015 at 1:55 pm

    • Sorry, I should have been clearer – what I mean is that if you try, can you use the technique in this post to restrict all access to your Fullname attribute and display the values on Address (assuming that that attribute is enabled and visible)? If so, then you’ll need to redesign your dimension so the attribute relationships go in the opposite direction – then you’ll be able to restrict access to Address and display Fullname.

      Chris Webb

      February 24, 2015 at 2:53 pm

      • from what i understand from the post above, the security is added on the levels of the dim. My two attributes cannot be on different level – they have to be attribute and property – and they can t be two attributes either (as i mentioned before, i also tested this:initially two attr, restrict access to one, then add the latter as attr of the first – the role uses access to the first attr as well).
        so i might not understand what you are actually suggesting, cause i really can t see how can i apply it to my situation

        alina

        February 24, 2015 at 6:09 pm

      • The confusing thing here is that levels on a hierarchy and member properties are really just different ways of seeing attributes. You can only ever apply dimension security to attributes on a dimension; when you do that, you’ll see security applied to any levels or properties that are based on those attributes.

        Chris Webb

        February 24, 2015 at 7:45 pm

  12. so i am right and it can;t be done in my case, correct? because my case implies having attribute a1 and its property a2, putting restriction on a2 automatically puts the restriction on a1 as well, which is contrary of what i want: all users to have access on a1 and some to have access on its property, a2.

    alina

    February 24, 2015 at 7:54 pm

    • It can’t be done if you leave your attribute relationships the way I think they are. However I think it is possible if you redesign your dimension so the attribute relationships are in a direction that allows you to use dimension security in the way you want.

      Chris Webb

      February 24, 2015 at 7:59 pm

      • thanks for all the help. i will be thinking about the redesign and hopefully smth good will come out of it.

        alina

        February 24, 2015 at 8:26 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,867 other followers

%d bloggers like this: