Masking Dimension attribute / Security in SSAS

4
votes

We have a cube where we implemented the dimension data level security based on ROLE. This security is working fine where we are restricting the user to see his records only. Now the Customer dimension has another Employee attribute. Based on the value of this field we want to restrict other dimension attributes. Like in the below example Manager_Id is the attribute that should be masked. We want to mask the attribute value of this field with "Employee” so that the restricted user only sees a masked attributes value.

Note: Both the attribute from the same dimension

User is allowed to see employee data

Name  Manager_ID
Jon    123456

If the User is not allowed to see then the attribute value needs to be masked with Employee

Eg:
Name Manage_ID 
Jon   xxxxx

Thanks Jay

1
ssasmdxssas-2012
Give an example of when Manager_ID would be unmasked. Please add more examples and MDX you tried till now. – SouravA
I am not going to unmasked for the particular role. But my client requirement to do like that. I tried with the unknownmember, it's masked all the columns in the dimension instead of single. – jay

1 Answers

0
votes

If I'm interpreting your question correctly you're trying to disable a value in a dimension based on the user querying the dimension/cube. Unless you've build your own version of excel where you would add those overrides you should use different roles for the different types of users that use your application.

While I don't know how and if you can mask a value, you can use attribute security to disable the role from viewing the contents of a certain attribute of a dimension.

To do this from SQL Server Management Studio you can open the properties of a role, go to the dimension data tab and select the cube and dimensions you want to filter.

Warning: you can't disable values in a dimension, then the user will still be able to query them, you'll need to scroll down to the cubes and edit the dimension there.

(it's a long list of first your dimensions and then your cubes which can be extended to show the cubedimensions, which are the only thing relevant to us right now)

When you have the correct dimension you can select an attribute Manager_ID in your case and disable all values a user shouldn't be able to see. You can do this by unchecking all restricted values or deselect all members and then click the ones that should be available. That all depends on your useCase.

The result of this will be that when opening the dimension from excel or powerBI the disabled values won't show.

I hope this helps and good luck.