In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube
(Screen Capture 1).
Usually, it’s a good idea to secure the data at Shared Dimension level as the permissions gets passed down to Cube Dimensions. This is especially helpful if the Dimension Data security is automated using AMO code – less code to maintain.
This works in most scenarios, except when the dimension is used as Role Playing Dimension. For e.g. Time, Currency, Organization Structure, Staff dimensions are commonly role played. Analysis Services does not permit securing the data of Shared Dimension if it is used as role playing dimension. Exceptions are thrown when the cube if browsed, if you attempt to do so.
If a dimension is role playing, the only way data security can be implemented is by securing the individual Cube Dimension. Cube Dimension Data Security overrides Shared Data Security.
The following screen capture shows the effect of securing the role playing dimension- using Date Dimension from AdventureWorks. Here Date Dimension (Shared Dimension) role plays as Delivery Date (Cube Dimension) and Ship Date (Cube Dimension).
Securing Cube Dimensions can be quite daunting especially if you are considering automating cube security because all the related dimensions need to be considered individually, but that’s the only way out.
Benny Austin 
Hi Benny,
I’ve done as outlined for a role dim, however I noticed when you define cube dimension permissions, its necessary to also specify a default member. However a default member is not necessary when dimension permissions are specified at database lvl. Do you know why this is?
Craig, I am not aware of any such requirement. What happens if you don’t specify a default member for role playing dimension ?
Okay, I’ve configured it the way you described above. Its doing an “and” instead of an “or”. I can send you a screenshot – you will have to instruct me on how.
Upload your screen shots to skydrive.live.com on the public folder and send me the link
I’ve tried different stuff (should have saved it off) since then. 😦 So I no longer have a screenshot of what you wanted me to try. I’m starting to think this isn’t possible.
Also, when I’m done I was going to secure by using the following statements for each Owner/Coowner Branch dimension. Would this work?
Exists([Owner Branches].[Owner Branch].MEMBERS,StrToMember(“[Step User].[Step User].&[” + Username + “]”))
Exists([Co-Owner Branches].[Co-Owner Branch].MEMBERS,StrToMember(“[Step User].[Step User].&[” + Username + “]”))
I really do appreciate all the help you have provided thus far.
It would be better if you could just secure only the User dimension with StrToMember(“[Step User].[Step User].&[” + Username + “]“). Additionally you could also set the default member with the same MDX expression
I am making head way with your suggestion above. However, I’v run into a problem. For # 4, you said Link the OwnerBranch dim to the Owner/CoOwner dim using many-to-many relationship, did you mean something else. I wasn’t aware that you could link to dimensions together…
I have tried the following:
1) Created a user dimension (userID, username)
2) two bridge tables (Factless UserBranch and Factless UserCoBranch) – (UserID, BranchID)
3) I already had a Velocity fact table.
4) I linked the User Dimension to the Factless UserBranch by UserID
5) I linked the User Dimension to the Factless UserCoBranch by UserID
6) I linked my already created Owner Branches dim to the factless UserBranch by OwnerBranchID
7) I linked my already created CoOwner Branches dim to the factless UserCoBranch by CoOwnerBranchID
8) I linked the User Dimension to the Velocity to the Factless User Branch(with many-to-many relationship)
9) This is where I think the problem is. I have no where to the Factless User CoBranch. The User Dimension is already used.
Please help me to know what I’m doing wrong.
The second half of 7) is where the problem is. Remove the link between User Dim and Velocity fact. Instead link Branch Dim to Velocity Fact in M2M relationship using Factless UserBranch as bridge. Similarly link CoOwner Branch Dim to Velocity Fact in M2M relationship using Factless UserCoBranch as bridge.
Could I apply this same theory to Active Directory Groups? This is how we currently secure the data, thru roles not users.
You could use AD groups , however there is no MDX expression to get the AD group of current login user
Benny, Can you help me with the following:
I’m implementing security on a new SSAS cube. I’m trying to setup up Dimension Data Security b/c Cell security does not work with our 3rd party app.
Here is my setup:
1)The database has a dimension Branch.
2) The cube has two dimensions: Owner Branch and CoOwner Branch.
I need to allow each Owner/CoOwner to query data in their particular branches. i.e. they can select TX from Owner Branch OR TX from CoOwner Branch. If they select TX in Owner Branch, they should see all data regardless of CoOwner Branch. When I set up dimension security for both Owner and CoOwner branch of TX, I only see where it exist in both.
Is there a way to do this in dimension data security.
Thanks in advance for any help you can provide.
For the scenario you mentioned, you could create 2 roles – one for the Owner Branch and another for CoOwner Branch. In the Owner Branch role allow access to only TX member on Owner Branch dim and no restriction on members of CoOwner Branch dim. Similarily for the CoOwner Branch role allow access to only TX member on CoOwner dim and no restriction on members on Owner Branch dim. In this way you can control access on members who belong either to Owner or CoOwner Branch. If there are members who belong to both the Owner and CoOwner Branch then you can grant access to both these roles. However be aware of SSAS behaviour when dealing with multiple role membership https://bennyaustin.wordpress.com/2010/09/19/membership-in-multiple-roles-part1/
Thanks Benny, for your reply. When I tried this, i get everything returned. Probably those surprises you talk about in your article. Any other ideas?
You could try this.
1.Create a Owner/CoOwer dimension using the windows login of user .
2. Also create two bridge tables – one for Owner/CoOwner-OwnerBranch and another for Owner/CoOwner-CoOwnerBranch.
3. In the dimension usage, link the Owner/CoOwer dim to the bridge tables in a regular relationship.
4. Link the OwnerBranch dim to Owner/CoOwner dim using Many-to-Many relationship.
5. Do likewise for CoOwner branch dim with Owner/CoOwner dim.
6. Now secure the Owner/CoOwer dimension based on current login. You could also set the default member to current login user.
Mr Austin, I’m not sure to properly understand what you mean by “Cube Dimension Data Security overrides Shared Data Security.”
In my case, I’m trying to combine two roles, but it appears that wether i define them at cube or shared dimension level, I can’t make them work properly.
To sum up the case :
Role 1 restricts dimension data to only one member A from attribute O (dimension Organisation).
Role 2 allows only one member L from attribute N (dimension Line). This member L is related logically to member B from attribute O.
When one combine role 1 & 2, user can see every member from attribute N of dimension Line related to member B from Organisation. The desired result would be instead to get every member of Line related to member A from Organisation, and only member L from those related to B.
This happens even if role 1 if defined at Shared dimension level (Organisation) and role 2 at cube dimension level (Line). It seems to mean that since every member from attribute N is allowed by role 1 at shared dimension level, one cannot exclude some of these members at cube dimension, at least with a second dimension.
Hopes that example can illustrate my point.
Thanks, M. Ledoux
Eric, the scenario you mentioned in your example is a typical Analysis Service behaviour. If there are multiple roles in Analysis Services, the least restrictive role overides the most restrictive role irrespective of whether it’s a shared or cube dimension.This is what happens in your example. Role 1 allows only member A from attribute O of Organisation dim AND allows all members from Line dim since there is no restriction specified on Line dim in Role 1. Similarily Role 2 allows only member L of attribute N from Line dim AND all members of Organization dim. Thus the net effect of Role 1 and 2 is all members of Organisation and Line dim can be browsed. This has nothing to do with the fact member L and member B are logically related. The best way to address this scenario would be to just create a single role and define dimension data security on both Organisation dim and Line dim. Hope that helps.
Need help please.
I try to use two cube dimension but different attribute:
Product Brand
and
Product Principal,
how do I mix these two attributes?
thanks.
abed.
Abed, could you explain bit more on what you are trying to do ?
Greetings – many thanks for this interesting posting.