Data Visualization with Tableau (35 Blogs) Become a Certified Professional
AWS Global Infrastructure

BI and Visualization

Topics Covered
  • Pentaho BI (9 Blogs)
  • Microsoft BI (5 Blogs)
  • Data Visualization with Tableau (33 Blogs)
  • BI and Data Visualization with Qlikview (1 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

Row Level Security in Tableau: Implementation and Usage

Published on Oct 10,2019 182 Views

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Row Level Security has been in all the rage since its inception into the Business Intelligence world. But how does Row Level Security in Tableau work?

This article gives you the most “standard” model of Row Level Security possible in Tableau. It will work for both Live Connections and Multi-Table Extracts. The topics covered are as follows;

Overview of the Tutorial: Row Level Security in Tableau

Now, before we begin, this article consists of the following elements;

  • The Entitlements View, which is a denormalized view, having rows for all possible Usernames and their matching entitlements

  • Data View. Now, this could be a single denormalized View or multiple Tables that are JOINED together, modelled as a single View. There is no reason not to reduce it down to a single Table when taking Extracts.

  • JOIN Clauses between the Entitlements View and the Data View on every field which makes up part of an Entitlement

These views are in the same database or Schema.

RLS High Level Process Flow

At a higher level of it all, a basic breakdown of the process can be explained the following way;

  1. Determine the Username

  2. Get your Data Entitlements for said User, just that User

  3. Filter your Data by those Entitlements

Now let’s get down to understanding what these complex term actually mean, shall we?

Standard Row Level Security in Tableau

RLS - Standard Overview

What is an Entitlement?

Now, the word Entitlement has been used many times so far. So, obviously it must be something worth defining. So, an entitlement is basically a single unique combination of Attributes that the Data View will be filtered on. These Attributes must exist in Column(s) of the Data View.

For instance, in a geographical hierarchy of Region, Sub-Region, and Country, each unique combination would be an Entitlement.

Representing Entitlements

The two main methods for representing Entitlements in a relational database are the following;

Deepest Granularity Entitlements which means you have a row for every entitlement.

Sparse Entitlements means you’ve got a row for every level in the hierarchy of Entitlements including the Deepest Granularity.

While the Deepest Granularity method explicitly lists down every possible entitlement combination, it doesn’t, however, have any entries for the levels of the hierarchy.

Sparse Entitlements instead allows using NULL to represent an Everything at this level in the hierarchy condition. This not only includes the most granular definitions but also steps at each level in the hierarchy that can see anything below them.

So, which method should you choose?

We don’t have a way of performance testing in all situations, and every RDBMS system differs in what is optimal.

Hierarchies and do you actually need them?

You may have noticed that this instance is solely stratified (the 3 attributes square measure coupled to at least one another); but, the construct of process entitlements don’t need that every one of the attributes is in an exceedingly strict hierarchy. As long as you’ve got the JOINs for every Column / Attribute, it ought to work properly.

You can get a performance profit if you employ the Deepest coarseness methodology and everything is hierarchical: you simply got to do one be a part of, on the deepest level of the hierarchy (since the upper levels of the hierarchy square measure implicit by the deepest level). This solely works if all of the attributes at all-time low-level square measure distinct.

If your connection is on a singular ID, then you’d be clear simply to affix thereon deepest level attribute, however if there’s an opportunity for duplication within the real values (for example, a Central sub-region beneath each AMER and EMEA), then you’ll be got to be a part of on all the columns to attain the impact of a definite key price.

Tying Users to Entitlements

So far, we’ve solely checked out the Entitlements themselves. currently we’ll explore the way to tie the Usernames to the Entitlements. In general, most systems link Usernames to a job, that may be a specific set of Entitlements. this permits for simply dynamic or removing a user from the Role, whereas still maintaining a record of the Role and its entitlements. It is often helpful to check the performance of a Regional Director, though it wasn’t a similar person over time.

In this structure, you’d have a Roles table, that defines absolutely the set of Roles.

Role Table

role_id

role_name

RD-APAC

APAC Regional Director

SRD-APAC-SEA

Sub-Regional Director, South East Asia

CD-APAC-JPN-JP

Country Director, Japan

CD-APAC-SK-KR

Country Director, South Korea

CD-APAC-SEA-TH

Country Director, Thailand

And finally, a mapping table that lists all of the title IDs for every Role ID. Notice {this is|this is often|this is often} a many-to-many table: it can be terribly long, however, remains skinny, and in each column, you’ll have several duplicated entries.

Their area unit 2 variations, looking on whether or not you are doing the Deepest graininess technique or the Spare Entitlements technique.

Deepest Granularity Mapping Table, Roles-to-Entitlements

role_id

entitlement_id

RD-APAC

APAC-JPN-JP

RD-APAC

APAC-SEA-KH

RD-APAC

APAC-SEA-ID

RD-APAC

APAC-SEA-MY

RD-APAC

APAC-SEA-MM

RD-APAC

APAC-SEA-PH

RD-APAC

APAC-SEA-TH

RD-APAC

APAC-SEA-VN

RD-APAC

APAC-SK-KR

SRD-APAC-SEA

APAC-SEA-KH

SRD-APAC-SEA

APAC-SEA-ID

SRD-APAC-SEA

APAC-SEA-MY

SRD-APAC-SEA

APAC-SEA-MM

SRD-APAC-SEA

APAC-SEA-PH

SRD-APAC-SEA

APAC-SEA-TH

SRD-APAC-SEA

APAC-SEA-VN

CD-APAC-JPN-JP

APAC-JPN-JP

CD-APAC-SK-KR

APAC-SK-KR

CD-APAC-SEA-TH

APAC-SEA-TH

Sparse Entitlements Mapping Table

role_id

entitlement_id

RD-APAC

APAC

SRD-APAC-SEA

APAC-SEA

CD-APAC-JPN-JP

APAC-JPN-JP

CD-APAC-SK-KR

APAC-SK-KR

CD-APAC-SEA-TH

APAC-SEA-TH

You’ll notice how much smaller this Entitlements Mapping table is than the one above (and this is simply 4 people with a limited subset of the APAC region itself). It’s also very easy to understand where someone lives in the hierarchy with this type of an entitlement mapping structure. (This example just happens to be 1 – 1 with roles and entitlements, but that is not always the case).

Finally, you map Users to Roles using another many-to-many mapping table. The username here needs to match either the Username or Full Name in the Tableau Server, as those are the only two values available via the User Functions in Tableau Desktop (yes, you can do string calculations on those values in Tableau if you need to massage the values available from this table).

role_id

username

RD-APAC

regional_director@company.com

SRD-APAC-SEA

sub_regional_director@company.com

CD-APAC-JPN-JP

japan_manager@company.com

CD-APAC-SK-KR

south_korea_manager@company.com

CD-APAC-SEA-TH

thailand_manager@company.com

Alternatively, you’ll be able to simply assign Entitlements on to User victimization the Tableau Username/Full Name in an exceedingly Users-to-Entitlements Many-to-Many Mapping table. You’ll get to manage a lot of directly therein table however it removes an additional set of JOINs to manage.

Entitlements View

For optimum performance (and it brings the only set of tables if conveyance into AN Extract), you’ll need to hitch all of those tables along into one Entitlement read. Yes, this can lead to a “blown up” version of the Entitlements, however, it shouldn’t lead to the info table itself rebuke.

The JOINs (in our theoretical example) would be:

SELECT * FROM users_to_roles_map Ur INNER be part of roles_to_entitlements_map re ON Ur.role_id = re.role_id INNER be part of entitlements_view e ON re.entitlement_id = e.entitlement_id

Now you’ll have a read (again, store within the information but is most optimal) that has usernames and every one the columns of claim data.

Filtering the Data by the Entitlements

The “Standard Method” of Row Level Security in Tableau uses a JOIN between the Data View and the Entitlements view. We recommend this because the same technique can be used with Live Connections and Multi-Table Extracts (2018.3+).

JOINs for Deepest Granularity Method

You can represent the Deepest Granularity form of this in SQL like the following (although VizQL will produce whatever complex queries it needs, this is the basic form):

SELECT * FROM data d INNER JOIN entitlements e ON d.attribute_a = e.attribute_a AND d.attribute_b = e.attribute_b AND … WHERE e.username = USERNAME()

For our example, the JOIN condition will be

ON d.region_id = e.region_id AND d.sub_region_id = e.sub_region_id AND d.country_id = d.country_id

You do this in Tableau in the Data Connection dialog. Just set up an INNER JOIN and add the fields from each table on each side.

JOINs for Sparse Entitlements Method

The SQL for the Sparse Entitlements method is more complex. If you are using Custom SQL (only possible for Live Connections), it would look like:

SELECT * FROM data d INNER JOIN entitlements e ON (e.region_id = d.region_id OR ISNULL(e.region_id) AND (e.sub_region_id = d.sub_region_id OR ISNULL(e.sub_region_id) AND (e.country_id = d.country_id OR ISNULL(e.country_id)

You can’t do that level of quality in Tableau’s be part of the dialog, however, you’ll get a close to identical result by doing the subsequent (Major hat tip to Dan Cory at Tableau World Health Organization right away represented this method once conferred with this Row Level Security pattern.)

In the Tableau Desktop knowledge association pane, you’ll solely do straightforward equality be part of definitions (=, <>, <, >, <=, >=) and not write additional complicated be part of clauses. However, you’ll outline be part of Calculations, that permits for the subsequent pattern;

ROW LEVEL SECURITY IN TABLEAU creating a join calculation

In the calculation editor that pops up, just put the integer value 1.

ROW LEVEL SECURITY IN TABLEAU - join calc of one

Now do the same for the other table. This creates what is called a Cross JOIN (yes, theoretically this is a “data blow up”, but luckily in both a Live Connection and Multi-Table Extract scenario, it shouldn’t be processed that way, due to the filters that follow).

Now we go into a Sheet to define the Row Level Security filters we want to apply.

The first filter is the Username limit, which will reduce down the total rows of entitlements:

[Username]=USERNAME()

Now you have the choice to do separate calculations for each of the levels in the hierarchy, or you can combine them all together. Those calculated views look like:

[region_id] = [region_id (Entitlements View)] OR ISNULL([region_id (Entitlements View)]

or a combined one for all levels:

([region_id] = [region_id (Entitlements View)] OR ISNULL([region_id (Entitlements View)])

AND

([sub_region_id] = [sub_region_id (Entitlements View)] OR ISNULL([sub_region_id (Entitlements View)])

AND

([country_id] = [country_id (Entitlements View)] OR ISNULL([country_id (Entitlements View)])

Place these calculated fields all as Data Source Filters and they will now give you the correct results based on the hierarchy you have defined. The magic is in the ISNULL calculations — this creates the effect of the NULL in the particular entitlement column to be matched up to ALL items in the other column.

“All Access” or “Deepest Granularity”: An intermediate solution

Hat’s off to Tableau’s Fearghal Gunning for coming up with this technique!

Why the Standard Implementation doesn’t cause Data to “Blow-Up”?: Row Level Security in Tableau

For those used to Tableau Data Extracts prior to 2018.3, you’re probably looking at this and saying, “that’s totally crazy, the end result will be enormous”. If in fact, you were to materialize the JOINs and bring it all in as one table (which is what a Single Table Extract does), that is exactly what would happen.

Thankfully, most databases can be optimized so that they never actually answer these types of queries by actually bringing all the rows together before filtering down. Your initial JOINs and security calculations in Tableau are just telling the VizQL engine about the relationships and filters to be sent. VizQL will write an optimized query, which the RDBMS system will then find an optimal Query Plan for.

Things like Primary and Foreign Key Relationships and Indexes will be taken into account (hopefully!) to process the resulting query in something like the following order:

  1. Determine the Entitlement Rows, filtered on the username

  2. JOIN that filtered set of Entitlement Rows to the Fact Table, using available Indexes and Key Relationships

  3. Max Number of Rows = Rows in Data Table (If a user is entitled to everything)

“Blowing Up” upon implementation happens when database processes the query in this order:

  1. JOIN all of the Entitlement Rows to the Fact Table

  2. Max Possible Size = Number of Entitled Users * Rows in Fact Table  (Every user * every row)

  3. Filters down to user’s rows

When Row Level Security is Built Into Your Database in Tableau

  • In Oracle, if you have VPD set up for the database users, you can use Initial SQL in Tableau to take advantage of the existing security filtering

  • In versions of SQL Server post-2016, you can set up Row Level Security in the database, based on database user. Tableau’s Impersonate User functionality will set the user correctly for you, and SQL Server 2016 will do the filtering. See Russell’s exploration here.

With that, I think we’ve reached the end of this article on Row Level Security in Tableau. For any queries, drop a comment down below. Hope you visit here again.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.