A bit of background on leaf members and non-leaf members is necessary to know how the data is managed at cell level.
The cell value in a cube can be obtained in multiple ways; it can be directly retrieved from the fact table of the cube. The identification of a cell value and its members is leaf members that have no child members or hierarchy that reference a single record in a dimension table.
Further on this cell can be identified by using non-leaf members, members that have one or more child members. The cell value is derived (typically) from the aggregation of child members.
The following prerequisite is essential for our recipe to continue the recipe:
SQL Server 2012 Management Studio (SSMS).
The
AdventureWorks2012database. We can obtain the necessary database files and database product samples from SQL Server Database Product Samples landing page (http://msftdbprodsamples.codeplex.com/releases/view/55330).Ensure you install the databases to your specified 2012 version instance.
For this book I have created a new OLAP database using the
AdventureWorksDM.xmlafile.
The following steps are continued from the previous recipe, but I believe it is necessary to reiterate them from the beginning. Hence, this recipe's steps are listed as follows:
Start the SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services instance.
Expand the
Databasesfolder.Choose the AdventureWorksDM database (created within the Getting ready section as previously mentioned) and expand the
Rolesfolder.Right-click on the role (here I have selected the DBIA_Processor role) to choose Role Properties.
Click on Cell Data on the Select a page option to present a relevant permissions list.
Note
In some cases, if you have observed that there is no option available in the Cube drop-down list in the Cell Data option, ensure you check that the relevant cube is set with appropriate Access and Local Cube/Drillthrough options by choosing the Cubes option on the left-hand side on Select a page.
Refer to the following screenshot:
Now let us continue with the Cell Data options:
Click on Cell Data in the Select a page option to present a relevant permissions list.
Select the appropriate cube from the drop-down list; here I have selected the Adventure Works DW2012 cube.
Choose the Enable read permissions option and then click on the Edit MDX button. You will be presented with the MDX Builder screen. Then, choose the presented Metadata measure value to grant this permission.
Similarly, for the Enable read-contingent permissions option, follow the previous step.
Finally, click on the Enable read/write permissions option.
As a final check, either we can click on the Check button or the OK button, which will check whether valid syntax is parsed from the MDX expressions previously mentioned.
If there are any syntax errors, you can fix them by choosing the relevant Edit MDX button to correct.
This completes the steps to secure the data at the cell level using a defined role in the Analysis Services database.
There are a few guidelines and some contextual information that will help us understand how we can best secure the data in a cell.
Nevertheless, whether the database role has read, read-contingent, or read/write permissions to the cell data, we need to ensure that we are granting permissions to derived cells correctly. By default, a derived cell obtains the relevant data from the other cells. So, the appropriate database role has the required permissions to the derived cell but not to the cells from which the derived cell obtain its values.
Irrespective of the database role, whether the members have read or write permissions on some or all the cells within a cube, the members of the database role have no permissions to view any cube data. Once the denied permissions on certain dimensions are effective, the cell level security cannot expand the rights of the database role members to include cell members from that dimension.
The blank expression within the relevant box will have no effect in spite of clicking on Enable read/write permissions. How this is effective is discussed in the following recipe.