Cubes – an important ingredient in OLAP to provide Business Intelligence to the end users. A cube is composed of cells that are organized by the measure groups and dimensions. On its own, a cell represents the unique logical intersection in a cube.
A cube is represented as a multidimensional structure that contains data used for analytical purpose. In this regard, the main pillars for cube are dimensions and measures. Dimensions highlight the structure and measures provide aggregated values for calculations purposes.
In a logical design, a cube allows end users or a client application to retrieve values of measures. These are contained in cells in the cube. Cells represent summarized values, which are also referred to as measures.
As we talked through previous recipes, in SSAS once a database role is created and granted with read or process permissions to a cube, then role members will have access to view all the cell data. In order to restrict such an action and access to specific cells, we must specifically restrict cell access. Within these cells, certain calculations can be derived using measures, which represent columns that contain data (numeric) that can be aggregated. By default, a measure is mapped to a column, in a fact table.
It is also essential to talk about derived cells access, in addition to a single cell in a cube. For these derived cells, the data is obtained from other cells. For instance, there are three measures (cost, sales, and profit) associated to cells named quarterly, which is used to derive profit on sales for a product. In this case, a database role has permissions on profit and sales measures but not on cost, so it may be possible for a member of that database role to infer the values of cells to which it does not have permissions.
In this recipe we will see how to grant access and permissions from the dimension to cell level within an Analysis Services database.
Note
The prerequisite for this recipe can be obtained from the Getting ready section in the Designing and creating security roles in an OLAP database recipe.
Once the prerequisites are completed, perform the following steps:
Open the SSMS and choose the Connect to Analysis Services… instance.
Expand the SQL Server 2012 instance and the
Databasesfolder.Choose the AdventureWorksDM database (which was created within the Getting ready section mentioned) and expand the
Rolesfolder.Now, select the DBIA_Reader role and right-click to show properties.
Choose the Cell Data option in the Select a page pane, which presents three different types of permissions for the cell data.
Let us select the Enable read-contingent permissions checkbox for the DBIA_Reader role:
Additionally, to grant permissions specifically to certain members enter the relevant MDX expression in the Allow read of cell content contingent on cell security box (see the preceding screenshot).
Note
Also, you can click on the Edit MDX button to build the multidimensional expressions (MDX), which will enable you to build relevant MDX expressions (see the following screenshot). This is useful for users who have experience in using the MDX programming language.
Click on OK to apply the corresponding changes for the preceding set of steps.
As we talked through previous recipes in SSAS, once a database role is created and granted with read or process permissions to a cube, then role members will have access to view all the cell data.
In order to restrict such an action and access to specific cells, we must specifically restrict cell access. Within these cells, certain calculations can be derived using measures, which represent columns that contain data (numeric) that can be aggregated. By default a measure is mapped to a column in a fact table.
The previous steps define the simple process of granting access within a cube for cell data; in additional to these actions a certain level of access can be granted to specific members of a database role.
The flexibility of security provision, by allowing access to cells for specific members in some dimensions and denying access to cells for specific members, is possible as per the previous steps. By default all the members associated in that database role will have relevant privileges on all the cube cells within that cube, if no members are entered. The option within the Allow reading of cell content contingent on cell security box inherits such actions of denials. For certain members you can create an MDX expression that allows or denies access to any possible combination of cells.
In certain cases, the permissions that are granted on specific cells cannot exceed the permissions associated to a database role on the cube. In this case the permissions that were set on cube level will be used. For instance, a database role has read/write permissions on a cell, but that same role has only read permission on the cube; in that case, the cell data permission will be set to read only.
Although a database role may have access to cell data, that role will not have access to dimension data unless the role has been given read or read/write permissions to the dimension data separately. Access to cube data can restrict access to dimension attributes to which the database role has access, but cannot extend access to dimension attributes to which the database role does not have access.
Consider you have to grant elevated privileges in addition to read permissions to a role, unless the role has been given read or read/write permissions to the dimension data separately. In such cases, access to cube data can restrict access to dimension attributes to which the database role has access.