Dimension Security and Drillthrough with SSAS2005

We are facing a problem when applying simple dimension security: it disables drillthrough.

The business case is simple enough: there are two usergroups, one that is denied access to a specific dimension and one that is allowed to see its contents. (In theory we would like to completely hide that
dimension for the first group, but that is impossible – vote for this issue here.)

To implement this we have set the Allowed Member Set to {} for all attributes of the particular dimension. It works as expected.

But… the drillthrough stops working for the users that have no access to the dimension (the drillthrough does not contain elements from that dimension). There are no frivolous calculated measures, no cell security, …

According to MSDN “Drillthrough returns a security error if the user has non-trivial cell security access to the cube” (http://msdn.microsoft.com/en-us/library/ms345125.aspx ) but what is “non-trivial” ? And in our case we do not get any error, only an empty resultset.

We tried several seemingly equal solutions (e.g. setting Allowed Set to the All element and at the same time Disallow all children of the All element), but drillthrough just did not work.

Our current workaround: there is one element in the dimension that does not contain any sensitive data (the “Unknown” element for these cases where the source data is not present). Setting the Allowed Member Set to exactly this member (or better: all its attributes) makes drillthrough work again. If a user who has no access to the dimension drags it into the pivot table then only the “Unknown” element is shown, which luckily is an acceptable solution.

[Sidenote: why do we need to set security per attribute instead of just on the key attribute ? Makes no sense, or does it ?]

The above message was posted in the microsoft.public.sqlserver.olap newsgroup but alas did not get any replies.

1 Comment »

Comment by Thomas
2011-08-31 07:34:37

Hello Tom

As we are facing the same problem with 2008 R2, we are wondering if there is now a solution for this problem?

Kind regards

Name (required)
E-mail (required - never shown publicly)
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post