How do you rate this blog

Sunday, July 3, 2016

Dynamic Security in SSAS in Detail

We all have applications which have security implemented at the application access level and at data level. Now if we have cube over this for reporting we would like the data level security to propagate to this layer and subsequently into the cube.
So how do we propagate this?

Lets do this by considering a simple example of a sales data model as shown in the image below.




Given below are the dimensions-
1) Product - Stores all the products


2) Date - Stores all the dates valid in this case I am using only months


3) Employee - Stores the list of all users who can access the application.





Fact table -
1) Sales - Stores all the sales data.

 

Bridge Table -
1) Employee to Product Mapping - Stores the list of product an employee can see.



For employee in the login make sure you add the proper username which should be in <domain name>\<username> format. If you do not know open command prompt and enter the command whoami to find it out.

Now lets build the cube over this. All the tables mentioned above should be present in the data source view as shown in the image below.



Lets build the cube with 2 measure group.

1) Fact Sales - Contains the sales data
2) Emp Prod Bridge - Contains the count of mapping between users and products which will be used in the dynamic security.

You need to have the below dimensions -
1) Product
2) Employee
3) Date

Once it is done, click on the cube and then click on the dimension usage tab. The usage should be as shown in the image below. If it is not there add the dimension and make sure the connection between dimension and fact is present.

Once this is done process the cube. Then open the cube in SSMS and execute the below MDX

SELECT {} on 0,
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
[MST EMP].[ATTR LOGIN].&[<username which you login>],
[Measures].[EMP PROD BRIDGE Count]
)
) on 1
from [Dynamic Security]

You can see only the products which you have mapped to user who has logged in is being displayed.

Now this is through MDX but how do we enforce this on the cube? For this follow the below Steps -

1) Make sure the users who access do not have administrator rights over the SSAS else this wont be effective since it will take the higher level privilege.
2) Click on Roles as shown in the image below
 

3) Right Click on roles to add a new Role. Give the role some name and allow only Read Permission.
4) Go to the Membership and add the users or usergroup to this place. The best practise is to use usergroup since the permission to this cube can be handled while creating the user in the AD by admins. This will prevent frequent visits to the SSAS to provide access.

5) Go to Datasources and provide read permission.
6) Go to Cubes and provide Read and if needed drillthrough feature.


7) Now go to dimension data, you can see the list of all the dimensions present in the cube.

 
Click on Product dimension, go to the Advanced tab and add the code below
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]"),
[Measures].[EMP PROD BRIDGE Count]
)
)

StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]") is the code which will filter out the dimension data for the user who is accessing the cube.
If you want to allow the user to access over application or powerbi \ excel pivot \ power pivot use UserName(). If you are using sharepoint over this cube you need to use CustomData().

The permission will look like the image below -

 


Once you are done with the above step click on ok.

Now you are ready with the permission model. You can check this by clicking on the cube and then on the browse button. Once this is done you will see the user icon as shown in the image below.





Click on the user icon and then click on the user. Provide the username and click ok.





 















You can then browse the cube where in you can see only those records to which the user has access to.

The cube created above with the permission model can be used via Power BI or Power Pivot or Excel Pivot by the end users for Ad Hoc reporting as well.

15 comments:

  1. Good code can you share code/ script?

    ReplyDelete
  2. Hi Abid, As you see the MDX is the only code you need to write, rest is design and configuration

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Dear Bharath R S,
    thanks for reply, i am still fail to implement dynamic security.
    can you share your project code?

    Regards,
    mianabid1978@gmail.com
    Abid Ali

    ReplyDelete
  5. Hi Abid, Sorry for the late reply, I do not have the project code with me. let me know how which part is failing.

    ReplyDelete
  6. Great blog!
    What if we have 2 domains? that means we need 2 attributes for each domain? or is there a function that can take just the UserName regardless of the domain?
    the current solution always expect: domainName\UserName
    thanks!

    ReplyDelete
    Replies
    1. If your domain controller has both the domains linked inside it then it should not be a problem. It can check the domains inside it and allow access to the cubes.

      Delete
  7. I have implemented the design above and can successfully run the MDX, but it gets and error when added to the Advanced tab for the Dimension security.

    NonEmpty (
    [SALES ORG DIM].[Sales Rep Number].Members,
    (
    StrToMember ("[User Dim].[User Account].[" + UserName () + "]"),
    [Measures].[BRIDGE SALES ORG Count]
    )
    )

    An error occurred in the MDX script for dimension attributes permission's allowed member set. Query (5,1) The '[BRIDGE SALES ORG Count]' was not found in the cube when the string, '[Measures].[BRIDGE SALES ORG Count]' was parsed.

    ReplyDelete
    Replies
    1. Hi,
      sorry for the delayed response. The mdx should work if it worked on the management studio. There is nothing different you have to do except remove the from clause in the MDx and whatever is on axis 1 needs to be placed in the advanced tab as shown in the blog.

      Delete
    2. Quote: "An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[User]' was not found in the cube when the string, [User].[UserName].[UserName].&[], was parsed."

      Because i had so many dimensions i didn't see the cube dimensions, at first. So i implemented the security at databasedimension level and not at cube dimensionlevel. The combobox "dimension" stores both database dimensions and cube dimensions. This is very confusing. "

      Source: http://bifuture.blogspot.com/2011/09/ssas-setup-dynamic-security-in-analysis.html

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This will work, you need to apply the logic to all the dimensions where you want the filtering mechanism. It will be lot of effort but that is how it was designed.

      Delete
    2. This comment has been removed by the author.

      Delete
  9. in the current example how would you recomment to enforce the access at MST_DATE dimension? my experiments show, that if i dont select product and only select data the mdx will show me all product sales for that date

    ReplyDelete