Implementing decentralized row-level access in Tableau Reader

For ten consecutive years, Tableau remains the leading business intelligence and analytics in Gartner Magic Quadrant for organizations. It is for many reasons: Tableau connects to a wide array of sources, has an easy-to-use interface, and has great built-in visualizations.

Although great in many respects, not all companies can’t run it to its fullest due to financial limitations or lack of internal support, leaving developers to create smart solutions to deliver great products with what we’d have in hand.

This article tells my real-world experience developing row-level access in Tableau Reader without the need for a user directory. Readers of this article are expected to have some previous experience building Tableau dashboards. Download links to a sample dashboard and its data source will be included at the end.

Context and scope

This is a case of a company with an overloaded analytics team delivering individual spreadsheet reports for 50+ salespeople. The workload increased when the sales team doubled in size due to a wide corporate merge. Thus the team needed help developing a solution for their increasing information demand.

The final goal was to reduce the monthly workload of the BI team and to incentivize information-based decision-making among salespeople.

The staff request their dashboards build using Tableau thus the final product has to be delivered using this platform. Also, the project must follow the next constraints:

1. The staff has corporate laptops as well as tablets, but the internet is not always available.
2. Due to high licensing costs, the client can’t afford to pay for Tableau Server user licenses.
3. Each salesperson’s performance is measured by their assigned set of geographical territories. and per corporate policies, each salesperson can only access their relevant assigned information.
4. Per company policy external access to internal user identity apps is not enabled for this project.
5. Development and service delivery can’t use Tableau Server API due to licensing agreements. Development and service are limited to using Tableau Desktop.

Problem exploration

Before addressing project constraints, let’s start by exploring the problem from the base data model:

Starting data model

This model contains sales in units for 1100 cities, 2000 distinct product SKUs, and up to 10 years of historical data.

• DIM_TIME: Represents recorded sale’s time period (month/year). Contains over 120 rows.
• DIM_PRODUCT: Contains the information on the sold product. Contains over 2000 distinct product SKUs as well as their associated attributes.
• DIM_GEOGRAPHY: Represents geography from the country to the city level. For this case, it has information for 1100 cities.
• DIM_SALESPERSON: Describes the responsible salesperson for generating the sale.
• FACT_SALES: Each row of table FACT_SALES records the total number of units sold for a specific time period (month/year), a product SKU, geography, and assigned to a salesperson.

As it is, once set up in Tableau all users would have access to all available information within the dashboard as shown below.

Sample base dashboard

Solution development

By the client’s request, all dashboards must be built in Tableau. Users can interact with visualizations online by connecting to a Tableau Server instance on a web browser or a Tablet. Although this requires both a stable internet connection and one license per user.

For constraint one, internet connection is not always available thus server deployment is not viable. And for the second constraint, the client can’t afford to pay for each license.

Tableau provides an offline reader app available for laptops and free of charge. It allows users to interact with dashboards, and export dashboard components as images or as spreadsheets files. We’ll deploy on Tableau Reader and they’ll download the latest monthly release when the internet is available, then constraints one and two are met.

Constraint three requires us to meet the corporate information access policy in which each user can only access their individual information. However, constraint four limits access to internal user identity directories then it’s nearly impossible to manage information access by users.

Let’s consider two options. First, it’s possible to generate individual user dashboards. This might work but, we’d have to refresh and deliver one dashboard per user each month. Yet it is also possible that the number of users increases over time.

On the other hand, Tableau Server is capable of running programmatic/automatic extract refresh, this scales greatly when you have to update many dashboards.

But for constraint five, we can’t use any of Tableau Server API capabilities, in this case, we’d have to refresh and maintain each extract, and also we’d have to deal with maintaining visual consistency among each one. Then this option is neither time nor cost-effective.

For the second option let’s consider how we could put in place an authentication mechanism within Tableau such that we wouldn’t have to generate many individual dashboards but rather one.

Coming up with a custom authentication mechanism

In its most basic data terms authentication is a set of user names, encrypted passwords, and a set of permissions available for each user.

In most cases, an app would connect with an identity system to first authenticate its user and second if the user was properly authenticated allow access to information and services according to available permissions.

From the data model definition, Tableau has the information associated with each user in table DIM_SALESPERSON. Let’s use the field SALESPERSON_NAME as a proxy for username and finally, we require a place to store a password associated with each user.

With this idea in mind let’s transform DIM_SALESPERSON, and add a new field SALESPERSON_PASSWORD. Although there’s no access to a centralized identity directory for this project each user would have an exclusive key to access the dashboard.

The key idea to managing user access is to assign an application key to each user instead of a connection to a central identity service. Keys are not managed by final users but only for administrators. If a user loses their key they can ask the administrators for it.

DIM_SALESPERSON schema is defined as follows:

Sample implementation

I recommend filling SALESPERSON_PASSWORD using a random word generator with at least ten (10) alphanumeric characters and assigning one to each user.

Now let’s review the new data model:

Modified data model

Finally, we have to implement a mechanism for users to authenticate themselves such as a user/password prompt.

Tableau provides parameters as a way for users to input text data and it’s available to capture it and use it with calculated fields. Let’s use this functionality to apply conditionals to what the final user is available to see on their dashboard.

Step by step implementation

Once the new extract is uploaded to Tableau, we’d have to code our custom authentication mechanism.

• Add a text box parameter and for the moment leave it as blanks. For this sample, I’ll call it PARAM_USER_INPUT_PASSWORD.

Create new parameter

• Create a new calculated field, for this sample, I’ll call it AUTH_KEY, its implementation is defined as follows:

Create new calculated field

• Drag the new calculated field to your filter’s shelf and set it to AUTH and apply it to all visualizations.

Setting-up user auth filter

Now let’s test it, include your password input and paste one of the user keys into the blank parameter, and hit enter.

Locked dashboard

At this moment you should only have available the relevant information for the selected user.

Authenticated user dashboard

Conclusions

So far I’ve introduced a practice to implement row-level data access with minor changes using only Tableau components.

This practice can be further realized at the visualization level, it’s possible to show/hide specific charts depending on a user basis. It’s entirely possible to have a single dashboard to serve a wide array of audiences.

Finally, dealing with technical and organizational constraints should not affect our capacity to deliver great products. Let’s use restrictions to challenge our abilities and allow us to grow, teach us to think outside the box, and use the most of what we have in hand.

Recommended reading material

If you are starting with Tableau I encourage you to play and experiment to your fullest capacity and dive deeper into LOD.

If you are an advanced user I recommend you to tinker with your data model and try to find ways to optimize your dashboard performance Tableau has a great performance tuning guide.

You can download sample files here!