Denying access to an entire level with dimension security

Reposted from Chris Webb's blog with the author's permission.

Most of the time when you’re using dimension security with SSAS, you’re slicing a hierarchy vertically: for example, on a Geography dimension you only want members of a given role to see just one Country on your Country hierarchy. Occasionally, though, you may want to slice a hierarchy vertically: on a Time hierarchy, you may want to allow members of a role to drill down from Year to Month, but not see Date level data. This is also possible; a customer recently asked me how to do it so I thought I’d write up the explanation since it’s very easy to do.

Let’s take the Calendar hierarchy on the Date dimension in Adventure Works as an example. When you browse it in the cube browser in BIDS, you’ll see a hierarchy that goes down from Year at the top to Date at the bottom:

Now, let’s create a role that stops users from drilling down beyond Month. Create a new role and go to the Dimension Data tab, and select the Date dimension on the Adventure Works cube (note not the Date dimension further up the list – that’s the Database dimension, we want the Cube dimension, the instance of the Date dimension inside the cube):

Then select the Date attribute hierarchy (the Date attribute hierarchy is used as the bottom level of the Calendar user hierarchy, so by securing it you’re also securing all user hierarchies that it appears in) and select the Deselect All Members radio button.

You can then deploy the project with this new role, and go to the Cube Browser tab, click the Change User button and select the new role in the Security Context dialog to test it:

You will then be able to see that you can no longer drill down below the Month level in either the metadata pane or the pivot table:

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .