Using MDX Expressions to Set Cell Data Permissions
The Multidimensional Expressions (MDX) expression that sets permissions to cell data can either specify which cells can be accessed or which cells cannot be accessed. By allowing access to cells for specific members in some dimensions and denying access to cells for specific members in other dimensions, cell security provides great flexibility. In fact, you can create an MDX expression that allows or denies access to any possible combination of cells.
When you create the MDX expression, remember the following:
The MDX expression does not need to include every dimension in the cube. If you omit a dimension in the MDX expression, access to dimension members is not affected.
To restrict access by only one dimension, a relatively simple MDX expression usually suffices.
The complexity of the MDX expression depends largely on the number of dimensions it includes.
To allow or deny access to cells for a specific member or measure, use the MDX functions, CurrentMember and Name, in combination.
If you specify a member name that is not unique within the dimension, use the UniqueName function instead of the Name function.
To allow or deny access to the cells for a member and its descendants, include the Ancestor function in the expression.
To allow or deny access to cells based on criteria in multiple dimensions, create an expression for each dimension, and then combine them with AND or OR into one expression.
Reviewing Examples of MDX Expressions that Set Permissions
The following topics illustrate how various MDX expressions can be used to control access to cell data:
Example 1—Permitting Access to All Members
Uses the default expression to return all members.
Example 2—Permitting Access to a Single Member
Uses the CurrentMember function to return only a single measure.
Example 3—Denying Access to a Single Member
Uses the CurrentMember function to prevent access to a single measure.
Example 4—Limiting Access to a Member and its Descendants
Uses the Ancestor and CurrentMember functions to return only a single member and its descendants.
Example 5—Giving Access to a Specific Measure Within a Dimension
Uses And to combine two expressions that contain the Ancestor and CurrentMember functions into a single expression that includes only a specific measure from a dimension.
Example 6—Excluding Selected Measures from a Dimension
Uses And to combine two expressions that contain the Ancestor and CurrentMember functions into a single expression that excludes selected measures from a dimension.
Example 7—Making Exceptions to Denied Members
Uses And to combine two expressions that contain the Ancestor and CurrentMember functions into a single expression that excludes a selected measure from every dimension except one particular dimension.