Recently a trainee from one of my MSBI trainings contacted me about how to implement user based security in SSAS. This is a common requirement where companies want all users to be able to access and use the cube but not all parts of it. A simple example would be to split metrics for sales data by region so that the manager of one region cannot see data for other managers in order to find out where he stands relatively.
The best way to manage a large number of users is to create a mapping table between all users and any permission they might have. E.g. in the below screenshot I am mapping a training co-ordinator with a client so that each manager can see the survey results only for his clients. The basic ER diagram shows the fact table answers which contain 15 Million rows, each row maps to an answer for 1 out of 17 questions asked to the student who attended the course. The student is mapped to a company and the company in turn is managed by a manager. The users table contains the Windows username of the allowed users and the Coordinator table contains the mapping between the user and the accounts or clients that he manages.
The below screenshots show examples of the data in the users and coordinator tables
Once the mapping table is related to the Companyid column in the student table we get to see how the bridge table connects the users to the dimension Students and in turn to the Fact table answers.
If all the dimensions and facts are properly related to each other then we can now start establishing the permissions by creating a role for allowed users.
Grant the role permission to read the cube first.
Then under Dimension data select the dimension to which the bridge table is connected (this could require selecting multiple tables such as region, SBU, Branch, etc.).
Under the dimension drop down select the attribute on which the mapping takes place , in this case I am filtering rows based on Company id which sis a column in the student dimension.
If there is already a permission defined then you will find the attributes security defined in brackets else it will be blank.
Next select the Advanced tab and enter the MDX expression to limit the rows to attribute keys that this user has permission for.
nonempty ([Student].[Company Id].[company id] ,
([Measures].[Coordinator Count],
STRTOMEMBER(“[Users].[Username].&[“+username()+”]”)))
I am getting the user name of the current user and converting the string into a member. This member is then based to the set to find out what company ids this user is mapped to which result in nonempty results. If non empty is not specified the user has access across the board. Instead of managing individual user level permission here we usually recommend the AD create roles for groups of users and the group are mapped within the cube. This ensures that permissions are granted and revoked cleanly across the enterprise.
Be sure to check the box for visual totals if you don’t want managers to reverse engineer the splits by deducting from grant total.
And you’re all set. Knowing MDX is very important to implement security in SSAS and as such the role of granting and removing permissions lie with the developer and not the DBA as is common with the relational database.
Please Consider Subscribing