All about Business Intelligence

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.