12/4/2009 - SetFocus BI Master's Program Tip: Testing SSAS Roles in Excel

In the SetFocus Business Intelligence Master's Program, we cover the use of security roles in SSAS databases.

SQL Server Analysis Services 2005/2008 both allow you to define roles for when your Windows users (or groups of users) access OLAP data.

As an example, suppose in the AdventureWorks OLAP database, we wanted to associate users with a role that only allows visibility to data for the countries of Australia and the United Kingdom, for the product category of Bikes. You would create a new role in BIDS (or in SSMS), call the role something like TestBikeRoleForAustraliaAndUK, and make the following selections in the Dimension Data area for the Customer and Product Dimensions (next 2 screen shots):

   

   

You can test the role by going to the cube browser in BIDS - the cube browser provides the ability to view the contents of the cube as if you were a user associated with the new role of TestBikeRoleForAustraliaAndUK. But suppose you wanted to test the role in Excel? After all, many users prefer to use Excel to create Pivot Tables and Pivot Charts from OLAP data. But how can we open the cube in Excel and test the role, without needing to actually sign on as a user associated with that role?

It's actually quite easy. In Excel, you can create a connection to an Analysis Services database. You can open the connection properties in Excel, and set the Roles property in the connection string to the role you wish to test (see screen shot below).

   

After making the connection, you can access the PivotTable field list in Excel, and select the entire Customer Geography Hierarchy and Product Category Hierarchy (see screen shot below). However, since you're accessing the OLAP cube based on the Role in the connection string, you'll only see data for Bikes for Australia and the U.K. (screen show below that).

   

   

So as you can see, Excel provides us with the ability to test a role by placing the role in the Connection String property. Pretty slick!

Comments

[Sam Kane]

Here are this and some other articles on Analysis Services Security Testing:

http://ssas-wiki.com/w/Articles#Testing_Security