Database Reference
In-Depth Information
Dimension security and parent/child
hierarchies
Parent/child hierarchies handle dimension security in a different and much more
complex way compared to standard dimensions. When a dimension contains a
parent/child hierarchy:
We cannot set up security directly on the key attribute - it will not appear on
the drop-down list of hierarchies on the Dimension Data security tab in the
Role Editor.
Granting access to a member on a parent/child hierarchy will grant access to
all of that member's descendants on the hierarchy.
Granting access to a member on a parent/child hierarchy will grant access
to all of its ancestors, so that the user can reach the selected member in the
hierarchy. Enabling the Enable Visual Totals option will ensure that these
ancestor members only display values derived from the accessible members.
Let's take an example of a dimension with a parent/child hierarchy, the Employee
dimension. What we want to do is implement Dynamic dimension security on this
dimension in a similar way to how we implemented it on regular dimensions in the
previous section. We want to create a bridge table to model the relationship between
Users and the Employees whose data they need to have access to.
As we've already seen, setting up security is as easy or as hard as finding the right
set expression to enter in the Allowed Member Set pane. Unfortunately, in the case
of parent/child hierarchies, finding the right set expression can be pretty hard.
Let's start with the requirements: we want to link Users and Employees using
this table:
UserName
Employee
SQLBOOK\Alberto
Syed E. Abbas
SQLBOOK\Alberto
Garrett R. Vargas
SQLBOOK\Alberto
Amy E. Alberts
SQLBOOK\Chris
Stephen Y. Jiang
SQLBOOK\Marco
Amy E. Alberts
SQLBOOK\Marco
Lynn N. Tsoflias
SQLBOOK\Marco
Jillian. Carson
Search WWH ::




Custom Search