Dimension Security and Drillthrough - SSAS2005
TomVdP
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: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410
)
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 that 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 ?]