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

Note the highlighted SCOPE statement, which is the key to making this technique
work: we are scoping on a dynamically evaluated member expression.
Having created this calculated member, we can then reference it in a much-simplified
cell security expression as follows:
NOT (
Measures.CurrentMember IS Measures.[Gross Profit]
AND HideGrossProfit
)
Now that the security logic is handled in MDX Script the query performance
improvement is significant: instead of being re-evaluated for every cell, the
expression determining which cells a user has access to be evaluated only once when
the MDX Script is executed. Another benefit of moving the logic to the MDX Script is
that it is much easier to define complex expressions and perform debugging there.
One last problem to solve with this technique is that the evaluation of the SCOPE
statement requires that each user of the cube, regardless of whether they are a
member of the dynamic role, be present in the Users dimension. If they aren't, the
cube will raise an error because we'll be attempting to use the SCOPE statement
on a member that doesn't exist. This can be worked around by altering our SCOPE
statement to use a named set, and then in the named set definition return an empty
set if the STRTOMEMBER function returns an error as follows:
CREATE MEMBER CurrentCube.Measures.HideGrossProfit AS False, Visible =
0;
CREATE SET CurrentCube.UserSet AS
IIF(
IsError(StrToMember ("[Users].[User].[" + UserName() + "]"))
, {}
, {StrToMember ("[Users].[User].[" + UserName() + "]")});
SCOPE (Measures.HideGrossProfit);
SCOPE (UserSet);
THIS = (Measures.[Bridge Reseller Users Count] = 0);
END SCOPE;
SCOPE (Reseller.Reseller.[All]);
THIS = True;
END SCOPE;
END SCOPE;