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:
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):
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.
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:
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:
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
Absolutely – it’s just easier to use the UI in this case. You need to use an empty set {} in the Allowed Member Set.
Good tip – thanks.
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
Are you finding that your connection is slow only in Excel 2010? If so, then it could be the problem described here: http://support.microsoft.com/kb/2535767
Can we implement this for intermediate levels? if yes, will it affect all levels below the level for which I have removed the access?
I’m not sure (I haven’t tested it) but I suspect it won’t work for intermediate levels
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?
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?
Yes. Customer is the Key, Customer Advisor is an attribute.
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?
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.
Oh well… I’ll try to take a look and see if I can make this work sometime over the next few days.
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?
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
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?
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 🙂
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.
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!
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….
Have you tried setting security on the Address attribute while AttributeHierarchyVisible=true, then setting the property to false after that?
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)
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.
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.
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.
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
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.
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.
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.
thanks for all the help. i will be thinking about the redesign and hopefully smth good will come out of it.
Can this be implemented in an sql server Tabular model?
We’ve done a similar solution denying access below a certain level in a dimension in one role and then allowing detailed access depending on username in an other role. Everything using multidimensional cubes. The way Tabular cubes works, this sounds tricky. Do you know it it is at all possible