Securing the Cube - Expert Cube Development with SSAS Multidimensional Models - page 273

Let's try again. In order to return the correct set of members, we need to make
Analysis Services ignore the presence of the parent/child hierarchy, at least initially.
To do this, we need to filter on the key attribute and not the parent/child hierarchy.
If we try this set:
NonEmpty (
[Employee].[Employee] .[Employee].Members,
(
[Measures].[Employee Count],
[Users].[User].[SQLBOOK\Marco]
)
)
We're getting closer to what we want: it returns only the three Employees that Marco
should have access to, just from the wrong hierarchy. Of course, Analysis Services
won't allow us to apply dimension security to the key attribute when there is a
parent/child hierarchy.
Notice that we used the set expression Employee.Employee.
Employee.Members and not Employee.Employee.Members in
the first parameter of the Nonempty function. This is very important
because Employee.Employee.Members returns a set containing
the All Member and Employee.Employee.Employee.Members
does not. We do not want the All Member to appear in the set: if
it does, the technique will not work for the same reason that the
previous set expression did not.
All we need now is a way to take the members this set expression returns and find
the equivalent members on the parent/child hierarchy. Luckily, we can do this easily
with the LinkMember function.
The LinkMember function takes a member and finds the member on another
hierarchy that has the same key value. This expression:
LinkMember (
Employee.Employee.[Amy E. Alberts],
Employee.Employees
)
Takes the member Amy E.Alberts from the Employee hierarchy and returns the
same member but on the parent/child hierarchy. What we need is to iterate over the
set of members we've got from the key attribute (the Employee hierarchy) and use
LinkMember to convert each of them into the equivalent members on the parent/child
hierarchy (the Employees hierarchy), and for this we can use the Generate function.