Archive for the ‘Tableau’ Category

I’ve been trawling the web trying to find a decent explanation on how one will go about reusing the security model defined in Microsoft SQL Server Analysis Services (SSAS) cubes when publishing reports to Tableau Server 7. There are bits and pieces eluding to some of the concepts described below but nothing comprehensive. After some proper R&D I can now share the end-to-end functionality with you. Happy reading.

SSAS Security

One way to define security on a SSAS cube is to add a Role to the SSAS database. The Role Properties dialog, portrayed below, allows one to add Active Directory (AD) users or groups on the Membership page. One can then restrict access to certain Cubes and Dimension Data.


Users accessing the cube using Excel, SQL Server Reporting Services, Tableau Desktop Professional and some other reporting applications will then only be allowed to access the data they are allowed to see. All good.

Now the snag comes in when you introduce Tableau Server. The classic double-hop (passing the users login credentials from their machine to Tableau Server and then onto the SSAS server) comes into play as Tableau Server and SSAS are unlikely to be installed on the same server. I thought good old Kerberos was going to be required but as you’ll see a little later, it isn’t.

Create Tableau SSAS Connection

A user will be provided with the Analysis Services Connection dialog depicted below when creating a new SSAS connection in Tableau Desktop Professional 7.


After you have specified the Server you will need to choose between Windows NT Integrated security or specifying a username and password. This is generally where the first mistake is made. If you select Windows NT Integrated Security, the credentials will not be used by Tableau Server when a user accesses a report. Instead the Tableau Server’s Run As User account will be used to access the cube and all the security roles defined for the SSAS database will be bypassed. Users will therefore all have the same access, which in my case was not good enough.

So, go ahead and specify an AD user in the Username text box along with the associated Password and go about creating your report.

Publish Tableau SSAS Connection

Once your report has been created it’s time to publish your Data Source to Tableau Server. To do this, right-click on the data source and select Publish to Server… In the Authentication drop down, select Prompt user and click on the Publish button.

An example of the Publish Data Source dialog is depicted below.


Publish Workbook

Next up it’s time to publish your workbook to Tableau Server. This is done by selecting the Server menu item in Tableau Desktop Professional and choosing Publish Workbook… One will then be presented with the Publish Workbook dialog (example below).


In the bottom left you will see an Authentication button. You will be presented with a second dialog after clicking on this button. Next, select Prompt user from the Authentication drop down and click OK. Finally click on Publish and you’re all done.


It’s important to know that if you selected the Windows NT Integrated security option when you defined the SSAS connection, the Authentication button will not be there!

Accessing the View through Tableau Server

Now for some testing. Login to your Tableau Server and browse to the view that has just been published. After have you clicked to open it you will be presented with a login screen (example below).


Type in your AD credentials and the view will be displayed. You have the option of caching your credentials until you physically log out which saves you from having to re-enter the details for each view that is accessed.

Admin User Access

In my scenario I was accessing a Speed Fines cube. I had defined an Admin user that could access all vehicle makes and then another user that could only see statistics for Audis and BMWs. Below is the view after entering the Administrator’s login credentials.


Tableau User Access

When entering the second user’s credentials and I access the same view, only the two vehicle makes are displayed. Like.


Administration: Clearing & Saving Passwords

Finally there are some useful settings in the Tableau Server Administration Maintenance section to make accessing the reports that little bit easier.


In the snippet above, an Administrator has the opportunity for clearing all saved passwords and there is also a checkbox that will allow users to save data source passwords across multiple visits and browsers. Not a good idea though if multiple users share the same workstation.


Read Full Post »