Row Level Security in Tableau (Simple and Org Hierarchy Based)

Row Level Security requires setup both in Tableau workbooks and in database.


To be secure and perform well, it is recommended  to use a Live Connection to the database. Extracts may be too large if we join a security table that is one-to-many to the data table.


Lets consider the Excel Sheet that comes with Tableau-> Sample - Superstore Sales (Excel).


Consider Data is "Orders" worksheet .

Now lets say we want user to see the data pertaining to his own region.

For example User-A will see data for Nunavut
User-B will see data for User-B

Create a worksheet that has mapping for Region and respective User. (If we have RDBMS then we will have similar mapping table. )

Now open the workbook -> Pull the tables (or worksheets in this case); make a join betwenk Order and User Mapping.

Open the Worksheet we we want to create our report. Create a calculated column as shown in below screen-

Place the calculated column in the Filter and Select True.

Now when User-A logs in then he will see only data for Region Nunavut; and User-B will for Atlantic.

--=========================================================---
We can have following tables also as security tables - 

Users Table
Roles Table
Users-Roles-Mapping Table

And then the Data Table (or Fact table will have the Roles instead on individual users) and that Role column can be joined to Users-Roles-Mapping Table.

--===================Row Level Security Based on Organization Hierarchy========


(Refer to workbook here-> 
https://public.tableau.com/profile/vikas.arora.bidw#!/vizhome/RegionalSalesProfit-RowLevelSecurityonHierarchy/Dashboard1

)

A more practical and real life scenario would be implementation of Row-Level Security based on Organizational Hierarchy where a boss can see all the data (records) created by himself or by his sub-ordinates.

Lets consider a scenario->

Les add a excel worksheet having following data (which is having employee details and their org path-

This will be kind of Employee table maintained by may in HR database. Notice the Org Path Desc the column that we will use for implementing security based on org hierarchy  The column login ID is the one which LDAP Id.


Now lest see the a column Emp ID that has been added in Orders worksheet, this indicates the row created by the employee id.

As per the data in Empl ID- following is the mapping ->
Atlantic-> All records by Emp ID 1010 (Karen)
Northwest Territories-> All records by Emp ID 1010 (Karen)
Nunavut-> All records by Emp ID 1006  (varsha)
Ontario-> All records by Emp ID 1008  (monika)
Prarie ->  All records by Emp ID 1008 (monika)

Quebec ->All records by Emp ID 1003 (vaurora)

West->  All records by Emp ID 1003 (vaurora)

Yukon->All records by Emp ID 1009  (david)

Lets see the workbook->

Create a Calculated Field -> RowBasedSecurityFilter

Place the field in Filter Shelf and select True.

So now the for different Regions appear based on the org hierarchy.

ODI 12c Standalone Agent Configuration

This involves three  steps:

1) Creating  a Domain for Standalone Agent

2) Creating Physical Agent in ODI Studio

3) Starting the Agent

1) Creating a Domain for Standalone Agent

Start FM Configuration Wizard:

On Unix machine, navigate to

/u01/Middleware/ODI12c/oracle_common/bin

Execute ./config.sh

If you do not yet have a WLS domain, then the first one created will be called base_domain by default. You can rename it to something such as odi_agent or mydomain99, just make sure that it remains in $FMW_HOME/user_projects/domains/. If you already have a domain named base_domain, then you cannot "create" another one, you would have to rename it to base_domain2 or something else, or you could Update an existing domain.

Click Next

Select Oracle Data Integrator - Standalone Agent. It is standalone in the sense that it does not use WLS.

Select as shown, Click Next.

Select Manual Configuration. Click Next

Enter the parameters as shown.

(Change hostname to the server where you installed the Master and Work Repository.)

(Mention passwords for schemas PROD_STB and PROD_ODI_REPO; these schemas are in the database where you installed the Master and Work Repository)

Click Next.

Click Next.

Click Next.

Leave these to default.

The is the password of ODI user that we use to connect from ODI Studio.

Note down the PORT and Password, we will need these while starting Agent.

Click Next.

Mention password as odi_agent

Click Next

Click Next.

Click Next.

Click Finish.

2) Create a Physical Agent in ODI Studio

Open ODI Studio

Enter Wallet Password.

Enter ODI Username and Password

On Topology Manager, Right Click Agent and Select New Agent

Enter details as we mentioned during domain configuration

Physical Agent OracleDIAgent1 appears under Agent tree.

Save.

3) Start the Agent

On Unix machine, Navigate to

/u01/Middleware/ODI12c/user_projects/domains/base_domain/bin

Execute ./agent -NAME=OracleDIAgent1 -PORT=20910

Agent is started.

Reference: 
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_12c/odi12c_agent/odi12c_agent.html#section1s1

Planning your Tableau Deployment

Planning your Tableau  deployment->

1) How will you license your installation?
2) How will users authenticate to Tableau Server?
3) How will Tableau Server access data sources?
4) What hardware will you need?

5) High Availability Architecture/Failover Architecture