A dimension is the key factor within Analysis Services database that consists of a list of attributes that will be used to analyze data. For instance, an attribute of customers may be represented by their sales volume or geographical area.
Whenever we build an Analysis Services solution the AS dimension objects are built from the dimension tables. The dimensional architecture keeps the Analysis Services database intact to provide Business Intelligence features.
In this recipe we will overview the dimension access privileges. As an administrator your responsibility is to grant relevant read or write permissions to some or all the members of any dimension in the cubes to which the database role has access permissions.
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 follow the following steps:
Open the SSMS and choose the Connect to Analysis Services… instance.
Expand SQL Server 2012 instance and the Databases folder.
Choose the appropriate database
AdventureWorksDM(that was created within the preceding Getting ready section) and expand the Roles folder.Note
As the
AdventureWorksDMdatabase is created with pre-existing Data Sources, Data Source Views, and Dimensions, we will have an opportunity to view the relevant Dimensions on the following screens:In conjunction with the Creating security roles (Intermediate) recipe we have already created three roles on
AdventureWorksDMdatabase, named asDBIA_Reader,DBIA_Processor, andDBIA_User.
As per the their naming convention the
DBIA_Readerrole has read access on the database that will automatically impersonate Read Definition privilege for the users that are associated within this role.Similarly, the
DBIA_Processorhas been granted a Process Database privilege that will automatically impersonate Process privileges for the users that are associated within this role.Expand the Roles folder and right-click on the DBIA_User role to choose Properties.
Click on the Dimensions option in the Select a Page pane (left-hand side).
Now locate the dimension in the Select Dimension Set and ensure that the All database dimensions option is selected from the drop-down list.
See the preceding screenshot that displays the relevant privileges for the role DBIA_User.
As there were permissions set for the role DBIA_user role under the Set the database permissions for this role option all the corresponding Read Definition and Process checkboxes are blank.
Now, on this screen, click on the Read Definition checkbox against the Dim Product and Dim Product1 dimension names.
Further choose the Process checkbox for the Dim Geography dimension name.
Click on OK to apply the corresponding changes in this recipe.
Tip
In addition to specifying the read (Read Definition) or read/write (Process) dimension access privileges to a database role, we can define specific attribute hierarchies and members within the dimension to which role members are allowed access by choosing the Dimension Data option under the Select a page option on the left-hand pane.
In this recipe and the previous one (Creating security roles) we have performed the necessary steps to create security roles and granted required privileges for these roles.
To re-iterate what follows from the preceding steps, we have already created three roles on AdventureWorksDM database, named as DBIA_Reader, DBIA_Processor, and DBIA_User.
Note
As per the their naming convention the DBIA_Reader role has read access on the database that will automatically impersonate the Read Definition privilege for the users that are associated within this role.
Similarly, the DBIA_Processor has been granted with Process Database privilege that will automatically impersonate Process privileges for the users that are associated within this role.
According to Microsoft documentation,
Since the SQL Server 2005 Analysis Services (SSAS) design concept, by default a database role in Analysis Services database has read permissions on all dimension members in each cube to which the database role has access permissions.
SSAS relies on Windows operating system authentication methods to authenticate users.
This means by default only authenticated users who have rights can establish the connection to the Analysis Services instance. The access privileges are determined by the rights that are assigned on instance level and database level. This process will work directly from the Windows operating system or through the membership in the Windows role.
The Server Administrator role is a single fixed-server role that grants relevant member permission to perform any task within the Analysis Services instance
Each database role has a customized set of permission to let the users access data or perform tasks within a database, dimension, or cube
Multiple privileges such as read and/or process can be granted to each role within the AS database. A database role can specify whether the associated members have permission to view or update members in specified database dimensions.
Similarly, on a cube level that is based on a single-database dimension will have multiple cube dimensions defined. The permissions that are specified for the database dimensions apply to all the cube dimensions unless these permissions are overridden for one or more of the cube dimensions.
By default, members with at least read access to an attribute member have read access to all cube cells related to the attribute member. You can limit cell access to specific cells. We will overview this topic under the Securing data at the cell level (Intermediate) recipe.
As we have cooked through a few recipes from the basics to construct the administer OLAP security management, the recipes associated in this security essentials will cruise through dimension to cell level within an OLAP database.
By default the best practices (and Microsoft) recommend to use Windows authentication based security. In some occasions this may not be possible by design or due to some architectural restrictions where the integrated security feature is used. Irrespective of either authentication methods it is highly recommended to use account credentials to access the data on database.
An authentication and access privilege brings up an impersonation topic that works seamlessly from the database level down to the cell level. In this book the recipes will highlight the role-based security from dimension to cell level and database to cube level. Also, covered are the features surrounding how to restrict access to dimensions and define default members (full and partial).
The following recipes will go through the security aspects on dimension level, how we can specify whether its members have permissions to view or update dimension members in a specific database dimension. For instance, a database role is not granted permissions to view or update a particular dimension. In this case some or all of the dimension's members of the database have no permission to view the dimensions of any of its members.
Similarly to the cells level a database role can specify whether its members have read, read contingent, or process permission on some or all of the cells within a cube. On the contrary, if a database role is granted permission to view members of a dimension, cell-level security can be used to limit these cell members from the dimension that the database role members can view.
In managing role-based security the two recipes Managing role-based security – dimension to cell level (Advanced) and Managing role based security – database to cube level (Advanced) are inter-related. In order to accomplish these tasks we will perform the steps as a two-fold process. Initially we will work upon database to cube level based security that will enable us to perform dimension to cell level steps.