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: