An Approach to Multi-Tenant Customer Data Isolation Using SQL Server and Tableau 8.1

While helping a client provide a 360-degree view of their offerings across business units, we came across a need for physical data isolation between the customers. This would be one of the key differentiators for our client. Physical data isolation would not only strengthen a customer’s belief in true data segregation but also enable easy extraction of isolated data on demand.

Considering the cost limitations, physical data isolation at the hardware/server level was not an optimal solution. We evaluated the following two options that were cost effective:

–   Option A: Create physical isolation of customer databases in the data warehouse.

–   Option B: Create logical isolation (views) of customers by database schemas in a consolidated data warehouse.

We used a simple framework to guide the client and help them make an informed decision on the right solution. The chart below compares how the two approaches scored based on a set of key evaluation factors:

Option B: Isolation by Consolidated Data Warehouse

Based on the chart above, Option B scored higher and seemed to be a better approach overall. In addition to the key factors considered, a consolidated data warehouse also provides multilayer security controls that are more reliable when used on a single, coherent system that does not need to be adapted or reintegrated for each new customer. Consolidated data also allows for more effective population risk assessments and more efficient response for regulatory mandates.

How to Implement Option B

       I.   SQL Server 2012

a.  Consolidate all customer data into a single data warehouse database.

b.  Create a database schema for each customer:

CREATE SCHEMA CustomerOne

c.  Create fact and dimension customer isolated views that will filter data warehouse data for a particular customer.

i.  Fact views can be created using a customer key filter against the fact tables (vFactOrder filtered for CustomerOne).

ii.  Create a bridge table/view that contains the unique dimension key from the fact table (FactOrderUniqueProducts). An indexed view is the preferred approach. If the performance of the view is poor, then create a bridge table.

iii.  Create isolated dimension views by joining the dimension table with the newly created bridge table/view (vDimProduct is created by joining DimProduct and FactOrderUniqueProducts on ProductKey).

d.  Create an application user for each customer. The user can either be an Active Directory account (preferred) or an SQL user. Set the default schema of this user to corresponding customer schema:

IF NOT EXISTS (SELECT *     FROM sys.database_principals    WHERE name = CustomerOneAppUser) BEGIN CREATE USER CustomerOneAppUser FROM LOGIN CustomerOneAppUser WITH DEFAULT_SCHEMA = CustomerOne; END GO

e.  Grant select permission for this user on its corresponding customer schema:

GRANT SELECT ON SCHEMA::CustomerOne TO CustomerOneAppUser GO

II.  Tableau 8.1 (Desktop and Server)

a.  In Tableau desktop, create a shared data connection using custom SQL similar to the one indicated below:

SELECT field1, field2 FROM vFactOrder f INNER JOIN vDimProduct p ON f.ProductKey = p.ProductKey

b.  In Tableau server, create a site for each customer.

c.  Publish the Tableau data connection from desktop to server using customer specific username.

Notes:

–   The Tableau view referred in the sample select statement is without any database schema prefix, as the user credentials used for connecting to the data source will automatically identify the schema to be used. For example, if the username is CustomerOneAppUser, then the result of the select statement will be from the CustomerOne schema.

–   Each customer Tableau site will need to connect to the data using its own application username.

–   On-demand data extraction for a customer can be achieved by extracting these isolated views using SQL Server Integration Services (SSIS).

–   Additional layers of security can be added using Tableau data source filters/Tableau user filters.

Summary

This approach uses native database security to isolate customer data so the data isolation is effective, regardless of the front-end tool. For example, these isolated views can also be used in custom .net applications and SQL Server Reporting Services (SSRS) reports. This approach also enables easy extraction of the isolated data on demand.

• SQL Server
• Tableau 8.1
• Multi-Tenant Customer Data Isolation