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

Bear in mind that enabling the Enable Visual Totals option on Dimension security
on a parent/child hierarchy can have a significant impact on query performance.
Parent/child hierarchies can have no aggregations within the hierarchy itself; the
only member that can benefit from aggregations is the All Member. Denying access
to some members in the hierarchy will invalidate any aggregations that have been
built at the All Level, and this means that every single query at that level (and
this includes queries that don't mention the Employee dimension at all) or higher
will have to aggregate values up through the parent/child hierarchy. We already
discouraged the reader from using parent/child hierarchies. The performance impact
of security on parent/child hierarchies is yet another reason not to use them.

Dynamic cell security
We can implement Dynamic security for cell security in much the same way as
we have seen with Dimension security. In this section, we'll use the same bridge
table we used in the previous sections to model the relationship between users and
resellers they have access to; our requirements will be as follows:

• Members of the role will have complete access to all measures, except for
Gross Profit.
• For Gross Profit, users will only have access to values for it for resellers they
have been granted access to base on the data in our bridge table.

• We also need to hide the All Member of the Reseller dimension to ensure that

users cannot derive values for cells they don't have access to.

We can use this expression to control Read access in the Cell Data tab:

NOT (
Measures.CurrentMember IS Measures.[Gross Profit]
AND (
StrToMember ("[Users].[User].[" + UserName() + "]"),
Measures.[Bridge Reseller Users Count]) = 0
OR
Reseller.Reseller.CurrentMember IS Reseller.Reseller.[All]
)
)

The usage of NOT is very common in cell security expressions: it's usually easier to
define the cells that users are not allowed access to. The expression may be read as
follows: "if the user is not querying Gross Profit, then show everything. Otherwise,
if the reseller they are querying is not present in the bridge table for that user, or the
reseller is the All Member, then deny access to the cell".