By: Ray Barley | Comments (12) | Related: > Analysis Services Security
Problem
I have seen the tip Dynamically Control Data Filtering in SQL Server Reporting Services Reports which showed how to leverage a SQL Server database table to filter sales data based on the user running the report. I have a similar requirement to implement dynamic filtering except my data is in a SQL Server Analysis Services (SSAS) cube and the users query the data with a variety of different tools. How can I implement dynamic security in my case?
Solution
SSAS has a robust security model that provides for dynamic filtering via a role-based security model. You create roles and assign Windows users, Windows groups, or both to be members of a role, then specify various security settings for each role.
As an example I have created an SSAS cube based on the SQL Server database used in the tip mentioned above in the problem statement. The cube structure is as follows:
Our task is to dynamically filter the list of offices based on the user accessing the cube. If the user were querying the cube from Excel, when the user drags the Office dimension onto the rows or columns of a pivot table, we want the list of offices to include only those offices that the user is allowed to view. We will setup role-based security in the cube to accomplish this as follows:
-
CEO role can view all offices
-
ManagerRegion1 role can view the Baltimore, Philadelphia and Wilmington offices
-
BaltimoreSales role can view only the Baltimore office
Setting Up Role-Based Security in the Cube
To begin, open SQL Server Management Studio (SSMS), navigate to the Object Explorer, and connect to your Analysis Services server. Locate your cube in the Object Explorer as shown below:
Right click the Roles node, then select New Role to launch the Create a Role dialog as shown below:
I have named this role BaltimoreSales; it will be used to grant access to the Baltimore office sales data to the appropriate people. Click Membership in the Select a page list of the Create Role dialog to specify the members of this role:
The above dialog has an Add button in the lower right corner (not shown above); click that button to locate the Windows users and/or Windows groups to add to this role. In the example above I have added a single user to the role.
Click Cubes in the Select a page list of the Create Role dialog to grant the BaltimoreSales role access to the cube:
Click Dimension Data in the Select a page list of the Create Role dialog to specify which members of the Office dimension this role is allowed to view:
In the above dialog we are able to specify the offices that this role can view based on the following options:
-
Select all members and manually deselect the members to deny
-
Deselect all members and manually select the members to allow
I chose the second option because if new members are added they will initially be denied; with the first option any new members added will be allowed by default. Choose the appropriate option based on your circumstances.
This completes the setup for the BaltimoreSales role. Repeat the steps above to setup other roles as required.
Testing Role-Based Security in the Cube
To test our role-based security we'll use the Business Intelligence Development Studio (BIDS) that comes with SQL Server. BIDS has a cube browser built-in to the SSAS project type. The cube browser provides an option to specify the role(s) you want to use for testing. The user account you use will need to be a member of each role that you want to test. While not the best choice from a security standpoint, the account used to develop and test is often a member of the local administrators group which will work.
The cube browser is shown below; I have highlighted the toolbar option that allows you to specify the credentials to use to browse the cube:
Click on the highlighted toolbar option to specify the credentials to use as shown below:
The default is to use the current user's credentials. You can specify a different user or one or more roles. Select the BaltimoreSales role then browse the cube. The cube browser shows the following based on our role selection:
Note that the only office shown is Baltimore and there is an information message stating that you are browsing the cube using the BaltimoreSales role. If I change the credentials to use the ManagerRegion1 role, the cube browser shows:
In this case we see data for three offices. Finally if I change the credentials to use the CEO role I see sales data for all offices:
You may have noticed that although the list of offices shown was what we expected in each case, the Grand Total reflected the total sales from all offices; it wasn't restricted to just the offices that the role is allowed to view. This is the default behavior. You can change this by returning to the Dimension Data dialog where we selected the offices that the role can view. Click on the Advanced tab and click the Enable Visual Total checkbox.
The above solution will work fine for a relatively small number of users where the authorization rules are pretty static. Ideally you should create Windows groups, assign users to those groups, then add the Windows group(s) to the cube roles. In a future tip I will explore the other available options for implementing dimension security which are using MDX expressions and creating stored procedures using .NET code.
Next Steps
- Download the sample code and experiment with it. The archive includes a backup of the SQL Server database used to build the cube, the SSAS project, and a backup of the cube itself. You can use SSMS to edit the roles and/or add your own roles.
- Dimension security in an SSAS cube is much more robust than what we get with a SQL Server relational database. This is good to know because your users will come up will all sorts of authorization schemes.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
