We had a requirement to implement dynamic security in SSAS with multiple roles for different dimensions.
But due to the Additive design of SSAS security, it is causing issues and people think that is impossible.
But I found the below useful link, which mentioned some of the alternative approaches.
http://blogs.msdn.com/b/psssql/archive/2014/07/30/the-additive-design-of-ssas-role-security.aspx
Actually to have multiple roles for different dimensions, you need to consider below scenarios
- If you allow access in one role, you need to restrict that in the other roles.
- Need to consider the scenario that the user is not belongs to any of the roles and restrict access in that scenario as well.
- Also make sure that you put the tick for the Enable Visual Total check box in the security applied attribute.
Instead of the custom assembly approach mentioned in the above link, I was able to get done the same using a reference table and have that as a Non-Additive fact in the cube.
As an example let say we have two dimension as Customer and Region and we need to restrict the access for those based on the Sales Person. To provide the access for Customer data, there is a role as Role_Customer and to provide the access for Region data, there is a role as Role_Region.
To achieve that there are two mapping tables are maintained as SECURITY_REGION (Columns as SalesPersonId, RegionId) and SECURITY_CUSTOMER (Columns as SalesPersonId, RegionId) to keep the relevant mappings. Two measure groups are created based on those mapping tables as SECURITY REGION and SECURITY CUSTOMER to use the mapping tables in the cube.
In the SECURITY_REGION role, security is provided for the Region dimension using the below expression. You need provide expression for the Allowed member set of the RegionId attribute of the Region dimension, under the Dimension Data.
Exists
(
{[DIMREGION].[REGIONID].[REGIONID].Members},
STRTOSET("[LOGIN].[PERSON NAME].&["+username()+"]"),
"SECURITY REGION ")
LOGIN Dimension maintains the log in details of the users and it uses to capture the relevant logged user.
In the same way, security can be provided for the Customer dimension using SECURITY_CUSTOMER role, by using the below mdx expression for the CustomerId attribute in the Customer dimension.
Exists
(
{[DIMCUSTOMER].[CUSTOMERID].[CUSTOMERID].Members},
STRTOSET("[LOGIN].[PERSON NAME].&["+username()+"]"),
"SECURITY CUSTOMER ")
But if you only follow above steps, security will not be applied due to the Additive design of SSAS Security.
To avoid it we need to follow the second step mentioned in the top of the post and it can be done as below.
In the SECURITY_REGION role, we need make sure if the user is in the SECURITY_CUSTOMER role, then for the CustomerId attribute of the Customer dimension, we need to restrict all the members since the
security will be applied from the SECURITY_CUSTOMER role. Otherwise we need to allow all the members.
We can achieve it by applying the below mdx expression for allowed member set of the CustomerID attribute of the Customer dimension in the SECURITY_REGION role.
IIF(Count(NonEmpty
(
{[DIMCUSTOMER].[CUSTOMERID].[CUSTOMERID].Members}*
STRTOSET("[LOGIN].[PERSON NAME].&["+username()+"]"),
[Measures].
[SECURITY CUSTOMER Count]))>0 OR
(Count(NonEmpty
(
{[DIMCUSTOMER].[CUSTOMERID].[CUSTOMERID].Members}*
STRTOSET("[LOGIN].[PERSON NAME].&["+username()+"]"),
[Measures].
[SECURITY CUSTOMER Count]))=0 AND
COUNT(NonEmpty
(
{[DIMREGION].[REGIONID].[REGIONID].Members}*
STRTOSET("[LOGIN].[PERSON NAME].&["+username()+"]"),
[Measures].[SECURITY REGION Count]))=0) ,
{},{[DIMCUSTOMER].[CUSTOMERID].[CUSTOMERID].Members})
We have to do the same thing for the RegionId attribute in the SECURITY_CUSTOMER role.
Also make sure that you put the tick for the Enable Visual Total check box in all the security applied attributes.
These steps will allow you to provide security based on Multiple Roles for Different Dimensions.
