How do I limit SSAS hierarchy levels to users?

Question

I am relatively new to ssas and am having trouble with something.

The scenario:

• A cube with a company hierarchy (region, sub-region, country, company)
• Dimension security is applied by filtering the company dimension by linking username to a list of allowable companies.
• Enable Visual Total is switched ON so that you can only see totals at each level of the hierarchy for those companies for which you have access.

The problem:

It has been requested that if a user can only see companies for one country (for example) then they should not be able to see the higher levels in the hierarchy (as the totals will be the same). i.e., if you can only see UK companies you should only see the country and company levels of the hierarchy and not the sub-region (Europe) and region (EMEA) levels.

Does anyone have any ideas on how this can be accomplished, or even if it can be done? We can manage a solution to work in the reporting layer, but the requirement is this should be handled in the cube to allow for future ad-hoc reporting/alternative reporting solutions.

Ideas/things I have tried:

• Trying to see if setting default member has any effect on the levels of the hierarchy you can see (it doesn't)

• Implemented multiple perspectives that are identical apart for the company hierarchy they use; each perspective uses a hierarchy that has starts at a lower and lower level.

  • this works up to a point, but i can't see how to restrict a user to only one perspective

• HideMemberIf - as far as I can see this is used to create a ragged hierarchy and hides lower members not the top levels of the hierarchy.

So, in conclusion, hmmm.

Answer 1

You can do this by removing the Role's permissions on viewing members in the associated hierarchies.

To do this:

• open the Role Designer
• choose the Dimension Data tab
• select the appropriate Dimension (make sure it's the cube dimension, not the database dimension)
• for each Attribute Hierarchy you want to hide:
  • choose the appropriate Attribute Hierarchy from the drop-down
  • select "Deselect all members"

Then ensure the perspective they're using doesn't attempt to display the hierarchies; any attempt to do so will result in a client error, because no doubt your cube has various interconnected queries referencing those members.

Also, any calculations that reference these members will throw wobblers; permissions are evaluated before calculations, so you should either remove those calculations, or resort to the sub-optimal solution of setting the cube's ScriptErrorHandlingMode property to IgnoreAll while in production.

Little side note: Perspectives aren't used for security, but for presentation. So if you don't want your users seeing things you've blocked off in a perspective, bear in mind that they can view them by other means, e.g. by using MDX, or by using client features that ignore perspectives.

Little other side note: some folk suggest that security to this degree is a client-side issue. I disagree.