In OLAP, within the Analysis Services database the cube plays an important role to provide Business Intelligence to the end users. A cube is represented as a multi dimensional structure that contains data used for analytical purposes. In this regard, the main pillars for the cube are dimensions and measures. Dimensions highlight structure and measures provide aggregated values for calculation.
In a logical design a cube allows the end users or client application to retrieve values of measures. These are contained in cells in the cube, cells represent summarized values that are referred as measures too.
Talking about cube permissions within SSAS, a database role has no permissions to view any cubes in the database. In order to obtain the necessary database that database role can be granted relevant permissions to a cube that will be transformed to cell level too.
In this recipe we will see how to grant access and permissions from database to cube level within an Analysis Services database.
Note
The prerequisite for this recipe can be obtained from the Getting ready section from the Designing and creating security roles in an OLAP database recipe.
Once the above prerequisites are completed follow these steps:
Open the SSMS and choose the Connect to Analysis Services… instance.
Expand the SQL Server 2012 instance and the Databases folder.
Choose the AdventureWorksDM database and expand the Roles folder.
Select the DBIA_Reader role and right-click to show Properties.
Click on Cubes in the Select a page pane, where the deployed cube will be displayed to grant Access and Local Cube/Drillthrough Access privileges.
Here we will choose both the Read option at the Access dropdown and the Drillthrough option at the Local Cube/Drillthrough Access column (see the following screenshot).
Do not grant access to the Process cube; thus, leave the check box blank.
Click on OK to apply the corresponding changes at this point.
The first steps in managing role-based security access to cube level is essential that will in turn enable the process to define cell based access on the Analysis Services database.
The preceding set of steps will grant necessary access privileges to the database roles. However, it is essential to highlight the associated action within the process; the Drillthrough option at the Local Cube/Drillthrough Access column is classified as an action. This can be termed as an end user-initiated operation upon a selected cube or portion of a cube. As per the settings or code actions, an operation can start another application with the selected item as a parameter or retrieve information about the selected item, which in turn deviates the security policies.
As per the Microsoft documentation the actions are differentiated as three types:
Drillthrough actions: These return a set of rows that represents the underlying data of the selected cells of the cube, where the action occurs
Reporting actions: These return a report from Reporting Services that is associated with the selected section of the cube, where the action occurs
Standard actions: These return the action element (URL, HTML, DataSet, RowSet, and other elements) that is associated with the selected section of the cube, where the action occurs
The Drillthrough action is also used within a mining model, which is termed as when a user browses through the model, they can retrieve detailed information about the cases that were used to create the model. In relevance to this, necessary permissions and the structure must have already been processed.
By default the database role, which grants read or write permissions to a cube and all the associated role members, will have relevant access to all cube cells unless permissions are specifically restricted on specific cells.
In order to accomplish such cell-based privileges, or if you want to grant write permission on specific cells in the cube to database role members, you can refer to the following recipe.