Our business intelligence consultants are constantly engaged in tackling challenge after challenge. The individual capabilities of each are impressive enough, but what they accomplish together is what makes them such an effective team. We’ve decided to share some of the solutions they’ve created together in a new Tableau Q&A blog series.

Challenge

A question regarding Tableau row-level security:

“A client has a team of 200 salespeople. Each salesperson, when they log into Tableau, must only be permitted to see their own data. The salespersons are split into groups of about 30 with a dedicated sales manager who will have permissions to see their data. In addition, 10 people in the organization will have unrestricted access to all sales rep data.

Row-level security, as I understand it, would have a table in SQL that holds a row of data for each salesperson/manager combination as well as a line of data for each salesperson/company-wide viewer. This table would then be linked to sales order lines on each salesperson, creating a data set that tableau can connect to and apply a USERNAME()= filter.

In this client’s case, this would cause a huge amount of duplicate rows. They are concerned this will impact performance. Does anyone have any other suggestions as to how I could implement security in this kind of environment?”

Team Discussion: How We Got to the Solution

1. So, maybe you join in some form of code, say “Security Group 1,” to your particular row and then create a calculated field that maps the username to a security group. Lastly, a calculated field comparing the two OR username() = .
2. This typically depends on the type of connection – extract or live? If live, some databases have alternative options such as SQL Server’s impersonation. With live connections in general, you would want to have your fact table with separate security tables that have a record for every user and the dimension members to which they have access. Then you’ll join the security and fact tables with a filter restriction on the user so you’re only bringing back his rows. With extracts, you can use user filters otherwise you will need to extract the join detailed above which would blow up your data set as you described.
3. One thing to consider though is that the maintenance of the sales team now lays in the Tableau workbooks calculated field, so if there are going to be a bunch of different workbooks requiring this security this may become an issue.RE: “You can now apply filters directly to data sources in v8, so there may be a way to abstract that to the data server and keep the workbook level edits to a minimum.

Solution

I ended up creating a fixed row size lookup table that used multiple columns for each level of management within the company hierarchy. I then used a case statement and username() in Tableau to enforce the security rules.