Consortium Data By Masking Sensitive Values

[Community_Admin]

Question

Some organizations provide Sisense to their clients as part of their business offering. Some of these organizations would like also to provide clients with the consortium data derived from Sisense - a single ElastiCube where clients submit their confidential sales and other data to the vendor, and then the vendor allows them to benchmark their sales, share (concentration), and rank amongst their peers. Each client may see the details of their own data, but cannot see the names of competitors or their specific products. Consortium data allows clients to better understand concentration and distance from their competitors.

The vendor must provide the following security measures on consortium data:

• Users should be able to see their company's own data relative to aggregated data, but should not be able to see details of competitors at company or product level, and should not be able to filter competitor company or products.
• Users must be able to use Product Name/Category as a dimension but only display a company's own products/categories.
• Clients should be able to see the name of their company but not the names of their competitors.
• Users must be able to filter by dimensions and use a variety of dimensions.

Below you can find an approach for enabling security on consortium data. This approach suggests masking sensitive values in order to hide them from unwanted users.

Answer

1. Duplicate the data in the table by using the UNION ALL clause, to create a copy of the data with masked values.
2. Add a flag that indicates whether the record is masked or not.

Here is the SQL code you can use: 

SELECT D.Date, D.CompanyCode, D.CompanyName, D.Country, D.ProductType, D.[Product Name], D.DistributionChannel, D.Sales, D.Assets, 0 isMasked

FROM Data D

UNION all

SELECT D.Date, D.CompanyCode, '###########' CompanyName, D.Country, D.ProductType, '#######' [Product Name], D.DistributionChannel, D.Sales, D.Assets, 1 isMasked

FROM Data D​

3. Create or import a table that connects user and company, for example:

4. Create a table that contains the rules of masking for each user:

Here is the SQL code you can use: 

SELECT U.User, U.Company, 0 isMasked

FROM UserToOrg U

UNION all

SELECT DISTINCT O.User, D.CompanyCode, 1 isMasked

FROM [Data] D

 JOIN [UserToOrg] O ON D.CompanyCode!=O.Company

The logic is to take the list of users and their company and allow them to see it unmasked (isMasked 0). This list is added to a join of each user with every other company (join on non equal), which they will see masked.

5. Connect the tables on Company code and isMasked:

6. Create security settings for users/group:

The result:

Note:

This solution requires company codes to be displayed.  If the existing company codes might compromise the identity of the competitors, consider doing one of the following:

• Instead of masking all as the same value (#####), give each a unique masked value (for example - ####1,###2). You can do so more easily by creating an excel file with the relevant fields.
• Use company codes that don't relate directly to company names.