I have just implemented security in a customer to limit the access to the data stored in the cube based on the user and the departments the user belongs to. So the user can belong to more than one department I have used the following approach:
- To create a fact table that contains all the relationships between each user and the departments
- To create a measure group that counts the number of existing relationships named as ‘Roles’
- To create a cube dimension based on the users that can access the cube (named as ‘UsersAllowed’)
- To create a relationship between the ‘Roles’ measure group and the ‘user’ dimension
- To create a relationship between the ‘Roles’ measure group and each ‘department’ dimension (‘department’ is a role playing dimension in this case so I have several cube dimensions based on that one)
- To create a role and edit the dimension data allowed so:
- Just the user connected is allow in the cube dimension ‘UsersAllowed’ (using the UserName() function) and select VisualTotal
- In the cell data allow the information just when the number of roles for the users is greater than 0 ([Measures].[Roles Count] > 0)
That approach was working perfectly, however my surprise was when the user was trying to execute a drillthrough action. He got an error from Excel and when I was running it through the SQL Management Studio I obtained the following error:
‘The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.’
Just to say that if the user doesn’t belong to the security role he was able to execute the drillthrough actions successfully, so I started to research why and I found the following KB article explaining that the error should be solved with sql 2008 sp and sql 2008 r2 Cummulative update 5, however for this particular case my tests have failed in both environments. On sql server 2008 sp1 I obtained the same error and for SQL 2008 R2 the SSAS service automatically shutdown.
After some more research, I finally identify that the many to many relationships I have defined in the cube were the reason of the error, when I was implementing the same dynamic security on a cube with no many-to-many relationships the drillthrough actions was executed successfully, but as soon as I included a many-to-many one the drillthrough action crashed. Why?
The reason was related with the cell data security, as soon as I was putting any expresion (even 1 > 0) the drillthrough action failed, so the solution needed to be change to avoid using the cell data security.
The final solution I have implemented is based on securing just the dimension data (cell data security has been removed from the role), so just those members that are valid, for the department the user belongs to, are the ones to be allowed. To apply that solution, in each dimension that has the department attribute I’ve added the following MDX sentence:
([Measures].[Roles Count], strtoset(“[UsersAllowed].[Login].&[” + UserName() + “]”))
That MDX is filtering all the departments that has a valid relationship with employees on the roles table (if the relationshipt does not exist it will returm empty and so the member will not be part of the allowed set)
In addition to that, Visual Totals has been checked also, so the All Department will not be valid and the measures are calculated just based on that set of valid departments.
I hope this can drive you to solve this kind of problem when you have many-to-many relationships combined with SSAS security and the user still wants to execute a drillthrough.