Custom Rules in Cell Security

Custom Rules in Cell Security

In a cube role, by defining a custom rule for cell security, you can specify which cube cells can be accessed by end users in the role.

Each custom rule contains an expression written in Multidimensional Expressions (MDX) that allows or denies access to specific cells or groups of cells. The MDX expression resolves to either True or False for each cell (atomic and nonatomic) in the cube. (If the MDX expression resolves to a numeric value, any nonzero value is evaluated True, and zero is evaluated False.) If the expression resolves to True for a cell, access is allowed. If it resolves to False, access is denied.

Custom rules are defined in the Cube Cell Security dialog box.

A custom rule provides two alternative approaches for each dimension in the cube. You can either specify the members whose cells can be accessed, or you can specify the members whose cells cannot be accessed.

If you can specify the member whose cells can be accessed, cells of other members cannot be accessed. You can use the equality operator (=) in the MDX expression to identify the cells that can be accessed. Example 1 illustrates this concept.

If you specify the members whose cells cannot be accessed, the cells of other members can be accessed. You can use the inequality operator (<>) in the MDX expression to identify the cells that cannot be accessed. Example 2 illustrates this concept.

In the MDX expression, it is not necessary to include every dimension in the cube. Omitted dimensions do not place restrictions on cell access; that is, cells for all their members can be accessed unless denied by way of a dimension in the MDX expression. If you want 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.

By allowing access to cells for specific members in some dimensions and denying access to cells for specific members in other dimensions, you can exercise great flexibility in defining cell security. In fact, you can allow or deny access to any possible combination of cells. Following are examples of functions to use in specific scenarios.

To allow or deny access to cells for a specific member or measure, you can use the MDX functions CurrentMember and Name in combination. Examples 1 and 2 illustrate this concept. 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. Example 3 illustrates this concept. To allow or deny access to cells based on criteria in multiple dimensions, create an expression for each dimension and combine them with AND or OR into one expression. Examples 4, 5, and 6 illustrate this concept.

Example Custom Rules in Cell Security

The examples are for a cube defined as follows:

• Measures:
  • Cost
    
    
  • Revenue
    
    
  • Tax
• Time dimension with levels:
  • Year, limited to members 1997 and 1998
    
    
  • Month
• Geography dimension with levels:
  • Continent, limited to members Asia, Europe, and N. America
    
    
  • Country, limited to members Japan, Korea, France, Germany, Canada, and USA
    
    
  • City

Before cell security is implemented, a dataset returned from this cube looks like the following.

Each example defines alternative cell security for this cube. Each example includes an expression in Multidimensional Expressions (MDX) in a custom rule for a read permission. The result on the preceding dataset is shown for each example.

The examples assume that the client application translates the formatted value #N/A, which indicates that access to a cell is denied, to a null value.

The examples are applied to read permissions so that the effect of the MDX expression on the dataset can be visually demonstrated. However, the examples are also applicable to read/write permissions. That is, if an example's expression was used in a read/write permission, it would allow and deny access to the same cells as in the read permission. The examples would also be applicable to read contingent permissions if none of the displayed cells were derived from other cells.

Example 1

The following MDX expression allows access to cells for the measure Cost, but denies access to cells for all other measures.

Measures.CurrentMember.Name = "Cost"

In a read permission, this expression causes the example dataset to be modified as follows.