Tableau Q&A – Row-Level Security

Data

Tableau Q&A – Row-Level Security

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.

More About the Author

Care Quality Analytics We are so excited to be launching our new product “Care Quality Analytics” and thought what better way to do this than with a blog and ...
PYD118 – Catching Up with Journalist Alberto Cairo In this episode of Podcast Your Data, analytics consultants Emily Miley and Jack Hulbert catch up with podcaster, professor, author and ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072