In the Cube Role manager for the sales cube select the All users role and click the ellipsis button in the Restricted dimensions column.

Select the time dimension and then select custom from the Rule drop down list. Then click the ellipsis button to open the Custom Dimension security dialog box. In the common tab select the check box for defining a default member. Note that an MDX expression has to be specified.

Click Ok three times in the screens that follow. The members function retrieves a set of all the members of the Calendar year level, while the Tail function forms a set that consists of only the last member from the level. The Item function extracts the first and the last member of that set. The resultant values are the last member of the Calendar Year level. Click the test role button to test the role.

Cell level security further restricts users from viewing certain cell values. Most often dimension level security is sufficient to ensure that the values of the cells in the restricted dimension are not visible. Yet when the cells need to be secured without removing members in a dimension then, cell level security is useful.

Right click the cube for which you want to apply cell level security and click manage roles. Expand all the levels and click Test role. Browse the data. Note that all the cells contain values. Close the Browser window and Click on the Cells column ellipsis button. The default Cell security policy value is unrestricted read. Select Advanced from the drop down list.

Select custom in the rule column and click on the ellipsis button in the custom settings column. Type a description in the description box. The cell security needs an MDX expression that can be calculated for each cell of a grid so that the value evaluated returns a false and nothing will be displayed to the end user.

Check the syntax by clicking the Check button if required. If the syntax is valid Analysis services says so. Close the message box and proceed.

The Read Contingent and Read/write permission for the role are automatically set to Fully restricted. Close the Edit cube dialog box and browse the data. The restricted cells will have #N/A against their value.

Cell level security is extremely important in write enabled cubes. Different groups of people may modify different cell values causing loss of integrity of data. To enable members to write to a cube the cube must be write enabled.

Analysis services gives a lot of importance to security. The cube role manager provides the administrator with flexibility for applying security. He can set permission at various levels beginning with the server itself and going down to the cell in a cube. Fine tuning of security can be done using MDX expressions.

In the next lesson we shall learn how to create and implement data mining models

First Page: Tutorial 66: MSAS - Applying security to a Dimension