416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Dundas Dashboard and SSAS Roles

Hide navigation
RSS
Modified on Mon, 10 Jun 2013 04:13 PM

Overview

SQL Server Analysis Services relies on Windows authentication and roles to determine user permissions. Please make yourself familiar with the User Access Security Architecture article on MSDN.

The rest of this article will demonstrate how to setup SSAS roles to work with Dundas Dashboard.

Setting up SSAS roles

You can take advantage of SSAS roles to restrict access to data in your databases. Using Microsoft SQL Server Management Studio let's create two roles in Adventure Works DW sample database:

Creating roles in AdventureWorks.

Creating roles in AdventureWorks.

For each role select Read Definitions permission from General page:

Select Read Definitions.

Select Read Definitions.



For each role grant Read access to Adventure Works cube:
Granting Read permission.

Granting Read permission.



Select Dimension Data page and Adventure Works DW.Customer dimension from Dimension drop down. For Role1 allow access only to the United States member:

Limiting Role1 to the USA.

Limiting Role1 to the USA.

Role2 should allow access to all members with the exception of the United States:
Limiting Role2 to everything else.

Limiting Role2 to everything else.



We will now setup role members. For each role go to Membership page and add a domain user you will use in Dundas Dashboard Data Connector. I am adding my own domain account in this case:

Adding a role member.

Adding a role member.



Passing roles from Dundas Dashboard

Login to Dundas Dashboard and go to Administration section. Create two new Security Groups naming them Role1 and Role2. These group names have to match the names of SSAS roles exactly. Dundas Dashboard will pass the names of all groups a user is member of to SSAS with the exception of Everyone group. See Adding a local Dashboard user for more information.

Create two new viewer user accounts User1 and User2. Add User1 account to Role1 and User2 to Role2 group. See Creating a Security Group for details.

Switch to Design Explorer and create a new Data Connector. Select 'Microsoft SQL Server Analysis Services 2005 or 2008' from provider drop down list. Check Connect As and provide the necessary information about the same domain user you've setup in SSAS roles:

Connect as a domain user.

Connect as a domain user.



Note: Notice that the user configured in Connect As is the same one we added to SSAS roles.

Enter your SSAS server, database and impersonation settings as described here.

Click Test Connection and Save.

Create a new virtual cube from Adventure Works cube. Edit the virtual cube. Go to Reports tab and a new report. Drag Cutomer dimesion and Internet Sales Amount measure to the grid. Because we are logged in as administrator the report shows all members available in Customer.Country attribute:

Creating a virtual cube.

Creating a virtual cube.

Click OK and check in the virtual cube. Create a new analytical dashboard and add the virtual cube to it. Check in the dashboard. We are now ready to view our data with two users we created. Login with each user and view the analytical dashboard. Here is the data as seen by the first and the second users:

User1 - Role1 on SSAS.

User1 - Role1 on SSAS.


User2 - Role2 on SSAS.

User2 - Role2 on SSAS.


As we expected, the first user can see only data related to United States customer while the second user can see the data for the rest of the countries.

Important Notes

Role and security group names

When choosing names for SSAS roles and security groups, note that certain characters are not permitted in the name. Dundas Dashboard will not pass any security groups that have a name containing such characters to SSAS.

The following characters are not permitted:

. , ; ' ` : / \ * | ? " % $ ! + = ( ) [ ] { } < >

Note that the underscore (_) character is allowed.

Related topics

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.