Have you ever wondering what are those functions under the USER section in Tableau’s calculated field? User Functions can be used to create user filters or row/column-level security that affect visualizations published to Tableau Server or Tableau Online, so that only certain people can see certain views of your visualization.

One example of row-level security (RLS) is that you may want to restrict data access so that the sales representatives can only see their own sales figures whereas the managers may be able to see all figures. For column-level security, you may want to restrict access so that some people can see a measure (such as profit margin), whereas others may not.

There are several methods for implementing user functions, ranging from very simple (but may not be scalable) to very complex. I will provide a brief overview of the 3 main methods, but only provide instruction for the most basic method using user functions. The 3 methods introduced in this post include:
1. “Create User Filter” option Tableau
2. Using User Functions only
3. Using User Functions & Entitlement Table

Required Knowledge: Basic knowledge of Tableau Server and Tableau
Difficulty: Easy
Requirement: applies to data sources with live connections and extract data sources whose tables are stored as multiple tables

What User Functions are available?

There are 6 user functions in Tableau: FULLNAME, ISFULLNAME, ISMEMBEROF, ISUSERNAME, USERDOMAIN, and USERNAME. I won’t go through the definition of them since you can click on them in Tableau to know what each of these is for. The function names are also self-explanatory. The functions start with IS… returns boolean results (True or False) whereas the other functions return strings. The function that we mainly use for this exercise is ISMEMBEROF and USERNAME()

Method 1: Use “Create User Filter” option in Tableau

Pros:  simple
Cons: not scalable, not robust (takes time to change, need to go back to Tableau Desktop to make changes), limited options, need to repeat per workbook

This method is not recommended (not even by Tableau) because it is very manual and hardcoded. It is also difficult to see who gets access to what. Furthermore, any changes to entitlement will require you to go back to Tableau Desktop to change. Finally, it’s also implemented per-workbook.

This option is available on the Server Menu > Create User Filter. There will be a drop-down of your dimensions so you can map manually which user group or user can see which value of the dimension (For example, user salesrep1 can see data of North Region only). Tableau then creates a set for you to drag to the filter.

Method 2: Use Tableau’s User Functions only

Pros: simple
Cons: need to go back to Tableau Desktop to make changes

Although this method is also not very robust, it is the focus of my post. Most of the articles and instructions on RLS focus on the more complex method 3. As a result, they can be hard to understand, so I hope this post can give you some foundation exercises before tackling the more complex approach. It’s also good for prototyping with extract.

Instructions

Step 1: Decide which user group can see what information
Step 2: Create a calculated field based on this
Step 3: Test the calculated field
Step 4: Add this calculation into the Data Source Filter
Step 5: Publish the Data Source or Publish the Workbook & Data Source separately

Step 1: Decide which user group can see what information

Step 1 is quite self-explanatory.  Let’s say we want everyone in the management team to be able to see overall sales, whereas the team in charge of each region can only see what’s in their region. If you don’t already have the required user groups in Tableau Server, you will need to create them first. For the purpose of this exercise, I created the following user groups in Tableau. A user can be assigned to multiple groups and they will have access to all permissions for each group.

Step 2: Create a calculated field

This is where we use the user functions. We can either use USERNAME() or ISMEMBEROF. If you use USERNAME(), you need to specify a specific username. This is not recommended because if the person’s role changes, we will have to go back to Tableau Desktop to change the calculation. It is better to use ISMEMBEROF. When a person’s role changes, we can just update the group on Tableau Server, without having to go back to Desktop.

IF ISMEMBEROF(‘Management’) THEN [Region] = [Region]
ELSEIF ISMEMBEROF(‘Sales – Central’) THEN [Region] = ‘Central’
ELSEIF ISMEMBEROF(‘Sales – East’) THEN [Region] = ‘East’
ELSEIF ISMEMBEROF(‘Sales – West’) THEN [Region] = ‘West’
ELSEIF ISMEMBEROF(‘Sales – South’) THEN [Region] = ‘South’
END

Step 3: Test the calculated field

Now, let’s test the calculated field by dragging it to the filter shelf, and filter to True only. You can test if this works by impersonation. You can either impersonate as a user, or as a user group.

Step 4: Add this calculation into the Data Source Filter

Once we made sure that our calculation works like it’s supposed to, we can get rid of the filter in the filter shelf and add it to the data source filter. Technically, we can just publish the workbook after Step 3, however, it would mean we have to repeat the process for every workbook we create. By applying the calculation to the Data Source Filter, we can use this data source for multiple workbooks. Another benefit of using a Data Source filter is that it would improve performance because it reduces the amount of data in the data source. Nevertheless, this data source would be an extract, not a live connection, although you can schedule extract refresh on Tableau Server.

Adding data source filter is very similar to adding filter on the filter shelf. Click Data Source tab > Click Add (see screenshot below)

How to add data source filter

How to add data source filter

Step 5: Publish the Data Source or Publish the Workbook & Data Source separately

Once we applied the filter, we can publish the data source or publish the workbook to Tableau Server.

How to publish the data source only

This is what it looks like after we published the data source to Tableau Server. Although I connected to a SQL database in Tableau, the published data source on Tableau Server is an extract.

How to publish the workbook & data source separately

As usual, click Server > Publish Workbook. In the dialogue box, under the section “Data Source” instead of Embeded Workbook, choose “Publish separately”.

Method 3: Use both Tableau’s User Functions & entitlement table in the database

Pros: scalable and robust
Cons: complex, may require complex SQL queries for joining, may have performance issues

Technically, this is more of an ‘approach’ rather than ‘method’, as there are many variations to implement this. It’s an extension of method 2. Instead of typing in the username or user group, there is an entitlement table specifying who can see which row. We then join it using Tableau, and adding some data source filters to reduce the rows. In short, the underlying data contains the security information you want to use for filtering. Entitlement table is sometimes also called ‘security’ table or ‘look-up’ table.

There are 2 data models for the entitlement table: the full mapping to the deepest level of granularity model, and the Sparse entitlements model. To learn more, check out the below links. They both provide detailed instructions on how to implement this approach for each model.

How to set up your Database for Row Level Security in Tableau

Tableau’s Whitepaper: Best Practices for Row Level Security with Entitlements Tables

In my next post, I will write about Column-level-security. Stay tuned!

 

 

Amy Tran
Author: Amy Tran

Amy hails from Vietnam and completed a bachelor’s degree in Economics at the Australian National University. While working a marketing role in Japan, she became interested in data analysis and began self-learning Tableau. She applied to join The Data School to gain knowledge and experience working with data in order to pursue a career in data analytics. In her own time, Amy is an avid blogger and also enjoys working on craft projects.