KPI Partners Blog

2 Options For Data Security in Tableau

Posted by KPI Partners News Team on Tue, Sep 02, 2014 @ 03:09 PM

by Kumar Krishnaswamy

A quick Google search for “Tableau Data Security” returns a link to a Tableau knowledge base article for User Filters and Row Level Security.  The knowledge base article clearly lays out the options available for implementing data security – defined as the ability to control what data a user sees.  The goal here is to not regurgitate that content, but to examine each of the two data security options laid out by Tableau and determine how they fit various use cases in the enterprise context.

Manual:  

This option is maintenance intensive and decentralized.   If you have a number of Tableau workbooks managed by individual users in various departments and they, in-turn, share workbooks between each other in a small group, without a centralized system to build and maintain either datasources or workbooks, this option becomes tenable.  

I should note that this is not an uncommon scenario for Tableau use - even in larger enterprises.

However, when security needs are more volatile – when new users get added frequently,  user security changes frequently, security is already maintained in some table in an ERP system and it is easier to re-use that setup etc., then we have a great need to choose the “Automatic” option defined by Tableau.

Automatic:

This option ought to be very familiar to many developers and architects of traditional relational BI tools such as OBIEE, Microstrategy, and Business Objects.  It involves users (or user group) mapped in a table to the regions (or any other dimension) that they have access.

A quick example to illustrate how this works and what the limitations are:

Kumar Krishnaswamy Tableau Data  Security 1 resized 600

Jane’s Sales = $50 + $35 = $85 (Region 3 only)

Mark’s Sales = $100 + $250 + $400 + $100 + $75 = $925 (Region 1 & 2 only)

John’s Sales = $1005 (Regions 1, 2 and 3)

In a traditional relational BI tool, the filter UserSecurityTable.USER = username() is applied during the query execution.  This works no different in Tableau,provided you use the “Live Connect” model.

The Tableau Extract

Most implementations of Tableau use the enhanced performance offered by Tableau extracts, especially when dealing with reasonable amounts of data. 

(What you define as reasonable amount of data depends on a number of factors but a quick google search for real-world experiences will reveal that extracts are being used for several million rows, perhaps even in the 10s of millions of rows)

Most relational BI Architects will immediately recognize that extracts are analogous to materialized views in the relational world.   

Now consider what happens when you try to materialize the view along with the security filters.  In our simple example above, we get 14 rows in our extract from an underlying fact table that contained only 7 rows.  In general, the number of rows in the materialized view (extract) explodes by a factor proportional to the number of users and the average number of regions that they have access to.

Even if you have a few million rows in your fact table and have a hundred users, materialization quickly becomes untenable.  You must fall back on the traditional “Live Connect” option.

Obvious, perhaps, but unstated in the Tableau documentation.

For extra credit – think about what this means when you want to support both users with security constraints and users without security constraints.

 


Kumar Krishnaswamy

Kumar Krishnaswamy is a General Manager at KPI Partners.   Kumar manages KPI's Offshore Technology Center in India and is responsible for establishing efficient processes and standards to ensuring a high quality delivery center for KPI's customers around the world. Kumar is recognized as one of the world's leading experts in business intelligence architecture and design.  Visit Kumar's blog at at KPIPartners.com.

Tags: Security, Data Discovery, Business Intelligence, Tableau, Kumar Krishnaswamy, Blog