I stumbled across an authorization problem regarding showing values in a company hierarchy and would appreciate if someone could help me out. The situation is as following:
I have an organization hierarchy with the following structure:
1) Market (statistical market data for comparing group performance with the overall market)
2) Group A
3) Group B
- We just have one key figure, let it be order intake (OI).
- In an Excel based report we have columns for all groups as well as for all subsidiaries, showing the order intake for each group/subsidiary for a certain period.
- I have created roles for the different groups/subsidiaries based on the organization hierarchy
Now the problem:
- Users which are only authorized to see data for a subsidiary should not see any data for the group
- But as the group value is calculated by the sum of both subsidiaries, these users always see the value of their subsidiary as group value too.
How users should be authorized:
- If I am a group user: See all data for my group as well as for each subsidiary in my group + see market data
- If I am a subsidiary user: See all data for my subsidiary, but show 0 or #NA for the group + see market data
- How can I achieve that a user who is only authorized to see a leaf of a hierarchy (= subsidiary) is not allowed to see any value for the parent sum node?
I hope you understood the requirements and provide me with a solution. Thanks in advance for any help.
- Edited by Mothman3000 Wednesday, April 18, 2012 12:19 PM
In SSAS, there is Dimension data security and Cell data security.
Based on the description, you want to restrict users based on the dimension attribute member, so typically you can consider implementing Dimension security because it covers most of your requirement.
In addition, you will need to "Enable visual totals" so that the restricted users will not see the Subtotal/grandtotal for parent in case the users do not have access to. For more information, refer to http://msdn.microsoft.com/en-us/library/ms175366(v=sql.105).aspx
Cell Data Security
HOWEVER, if users are restricted to the "subsidiary user" role, they will see their subsidiary data, and the parent level (group) will not be displayed because they do not have access to it. In most clients like Excel or SSMS 2008 R2, the users can only see "subsidiary" so that they can select only what they are allowed to see. The group will not be displayed as 0 or #NA. If you really want to show them as 0 or #NA, you can consider implementing the cell Data security. For more info, please refer to http://msdn.microsoft.com/en-us/library/ms174590(v=sql.105).aspx