By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
I was recently faced with a challenge when working for a client. We needed a simple way to provide row-level permissions to hundreds of Tableau Server users for 4 million+ rows of data. The data took place over 300+ sites. Some sites might only have a handful of users that needed access to view while others might have 40.
The classic row-level permissions approach which duplicates each row of data for each user and then filters on the active Tableau Server username wasn’t feasible. Our row count went from 4 million to over 70 million and we knew it would only continue to grow.
Each of those 300 sites was broken into four regions. There were three levels of access that needed to be reflected in our permission model:
● Site-Level (General Manager)
● Regional (Regional Manager)
● Global (Vice Presidents and Executives)
We ended up managing the permissions using two separate systems.
For global users (which didn’t change frequently) we utilized a Tableau Server group.
For the rest of the users, we list-aggregated their usernames and used a CONTAINS function in a data source filter of the Tableau workbook to restrict site access to only granted users.
The data source filter looked like this:
Let’s dive into the steps we took to get there.
Providing Global Access
Providing global access was the easiest part of this solution.
You can navigate to Tableau Server or Tableau Online and create a group. (Note: you will need to be a Server Administrator to have access to do this).
Once the group is created, you can enter the users and select who should be a part of it.
That is where the first piece of the data source filter comes from. The ISMEMBEROF function searches for the Tableau Server Group you input and returns TRUE or FALSE depending on whether the active user is a part of that group.
If we only had the top line of the data source filter condition (above) and only Kirk was in the “Global Permissions” group, he could see everything and I could see nothing.
Regional and Site Level Access
For the next couple sections of this post, this is what the base permissions table looks like:
Each row represents a single user. Their access level is listed to the right. As mentioned previously, global users were added manually to the Tableau Server group.
For later reference, note that I am permissioned to site 101. Kirk is listed as a global user.
These next few steps I am going to demonstrate in Tableau Prep. I’m not advocating that it’s the best place for this (it’s probably not) but since this is a post about providing a permissions model for Tableau reporting, I assume there’s a decent chance you have access to Tableau Prep. If you have access to a more sophisticated tool like Alteryx or the ability to do the next several steps in SQL, I would recommend utilizing either of those approaches.
I’m going to start by pulling the permissions table into Tableau Prep and create two workstreams, one for site-level access and one for regional-level access.
My temporary end goal is to get each flow to have one row of data for each unique combination of email address and site that a user should have access to.
That is fairly straightforward for the site workflow. All I need to do is remove every column but the [Email] and [Access] column.
While still in the site flow, I’m going to rename [Access] to [Site ID].
Our temporary goal has been established for the site arm of this flow.
The regional arm of this flow will be trickier. First, I will want to track down a table that gives me a complete list of sites by region.
Next, I can join that table (Site List) to the region arm of this workflow where Access (from the Permissions table) = Region (from the Site List table).
Now, like the site arm of the flow, we can remove every column except [Email] and [Site ID].
Now, for both arms of the workflow, we have a single row for each unique combination of Email and Site. We can union the two flows together.
Now, I am going to do a calculation to add an asterisk to the beginning and end of each email. This is important because we’ll be using a CONTAINS function to determine if a username (email) has access to a site.
Imagine we have a user, annie.smith@onenumber.biz who just has access to site 405. However, there is a fannie.smith@onenumber.biz who has global access. Since annie.smith is contained within fannie.smith Annie will be given global access!
The ideal final state would be to get the data into a format where there is one row of data for each site with a list of permissioned usernames in an adjacent column. Unfortunately, Tableau Prep isn’t well equipped to handle transposing the data. In my actual project, this is where we turned to SQL and this LISTAGG function.
Below is a simplified version of our SQL Statement:
The desired output of this step looks like this:
At this point, we can join the permissions table we created to the table which contains our transactional data on [Store ID] = [Store ID].
It’s important to ensure your row count didn’t change with this join. If the row count increased, it means you probably have a site listed multiple times in the Permissions table. If you are performing an inner join and it decreases, that means you are probably missing a site in the Permissions Table.
Now, it’s time to add a data source filter based on the [Permissioned_Usernames] field.
The result is that I will only be able to see data for Store 101 because that is the only store I’ve been granted access to through this permission model and data source filter.
However, Kirk has global access and can see all stores.
Need help with your own data source or permissions questions? Sign up for a Tableau Office Hour or SQL Office Hour and we’ll work on it together!