Row-Level Security Implementation Using OR Operator Across Multiple Dimensions

Overview

In Sisense, the Data Access Security, allows Admins to restrict the data access for Users and Groups, in row granularity.
In many cases, customers restrict the data to multiple dimensions.
For Example:

Restriction on Country
Restriction on User

When applying data security across two different dimensions, the security rules are enforced using an 'AND' operator between the dimensions.

In many cases, customers are looking for the ability to use the Data Security feature with an 'OR' operator between two or more dimensions, a functionality that is not supported natively.

Example Use Case

A Manager of a Country, testsecurity@sisense.com in our example, needs access to information related to his country, including details on both sales and manufacturing activities within that Country.

User Permissions

Country Restriction - CHINA
Security Apply on Manufacture Country = 'CHINA' and Sales Country = 'CHINA' with 'OR' condition Between the two dimensions

User Output example on fact_transactions using OR condition

User testsecurity@sisense.com access should be for transactions: [1,5,8,13,16,20]

User output example on fact_transactions using AND condition (current Sisense Configuration)

While Applying Data Security over the 2 dimensions for User testsecurity@sisense.com as the following:

dim_manufacture_country.Country = 'CHINA'
dim_sales_country = 'CHINA'

the Restriction will allow the user the access for records: [1,20]

Data Model Tables and relations
the list of tables which created for the use case

dim_employee - list of all employees
dim_sales_country - list of all sales country
dim_manufacture_country - list of all manufacture country
fact_transactions - transaction data
fact_orders - orders data

Table Relations

Basic Solution

In general, it is recommended to apply data security to a single dimension rather than using multiple tables.

Implementation Steps

Step 1 - Create a surrogate key
In each of the fact tables (or tables that require to have the restriction), Create a surrogate key as the additional column, in the following format:

Note - Make sure that the Values in the Surrogate key are not Null

[manufacture_country] + '_' + [sales_country]

Table Output

Step 2 - Create a Unique security key table
The Security key table is a combination of all possible keys from all fact tables; see the example:

select distinct fo.security_key
from [fact_orders] fo
union 
select distinct ft.[security_key]
from [fact_transactions] ft

Use Union instead of Union ALL to remove the duplication of the security_key to keep it as the primary key

Step 3 - Create the relationship
Create the relationships between the fact table/s and the security table on the security_key column

Step 4 - Assign the Data Security Member by user/group

In the Data model page, Click on the Options
Click on Data Security
Select security_key column from the security_key table
Select the User / Group
Select the relative Members for the Country
Assign all the related Keys

Step 5 - Create a dashboard for the validation

create a pivot table and select the relative columns
check the results - should return all the records of China for manufacture and sales country

Advance Solution

The advanced solution is more scalable, easier to understand and debug, and can improve the dashboard load time (in case the amount of members in the data security is high).

Step 1 - Create a surrogate key
In each of the fact tables (or tables that require to have the restriction), Create a surrogate key as an additional column, in the following format:

Note - Make sure that the Values in the Surrogate key are not Null

[manufacture_country] + '_' + [sales_country]

Table Output

Note - best practice is to create the column directly in the source data base

Step 2 - Create a security key table
The Security key table is the combination of all possible keys from all fact tables, see the example:

select distinct fo.security_key
from [fact_orders] fo
union 
select distinct ft.[security_key]
from [fact_transactions] ft

Use Union instead of Union ALL to remove the duplication of the security_key in order to keep it as the primary key

Step 3 - Create a Security Table and Store the Members on the User Level

implementation prerequisites

On the Customer level (Source Data Base), it is to maintain the table according to the security rules for each user
The Security keys will be assigned to each user according to his permissions
The Members per user will be Unique
the table will be added to the data model and the security will apply to that table

Implementation Example

Create the Users_scope table Permissions, for example:
testsecurity@sisense.com allows one to see CHINA country
User: User to apply the security on
Scope: The Dimension/s
Value: The Security info that the security_keys are assigned to

Example Table
Create a table: Users_security_keys and Assign the Distinct Security_keys related to CHINA from the Security_key table for each user in Users_scope table.

Table output for testsecurity@sisense.com

Again, The Security_key value per user is unique and can't repeat in Users_security_keys table.
Create the relation in the data model from Users_security_keys table to the fact tables based on the security_key column.
The Relation is a Many to Many by design but is one to Many on a User Level

Model Relation
Create the Data Security on Users_security_keys table where User = User
Please find the example below
Create a dashboard for the validation

Solutions Compare Between the Methods

Topic - Main Points	Basic Solution	Advanced Solution
Dashboard Update while Applying the Row Level Security	Dynamic - Changes apply on the Dashboard at the time the Members Updated	Limited - Changes apply after the member updates in user security table. Elasticubes - Changes will be updated after the Build Live - Changes will be updated while the Member updates in the table
Member Assignment in Data Security	Users can have a list of Members	Users have Single Member
Performance - Amount of members	Limited - Performance issues raised in a high amount of Members (recommended less than 100 Members)	Advanced - Single member per user which outputs the list of all members from the Users_security_table
Assignment on User/Group	Data Security Apply on User Or Groups. Provide Flexibility of Managing the Same Access for Users on a Group Level	Data Security Apply on User Level. Each User will have its own permissions
Security Table Size	Contains a List of Unique Security Keys. Relation from Security table to Facts is on One to Many	A large table contains a list of all users with a unique member list by user. Relation from Security table to Facts is Many to Many By Design, But One to Many on the User Level
Members' Maintenance across multiple Data Models	Changes in Members for a single user / Group should update for each data model	Changes in Members for Single User are updated in the table, no changes are required in Sisense Level for Existing Users
Members Tracking (Changes between Permissions)	Harder to Maintain, require to Get the Amount of Members before Applying the Changes	Handled on the Customer Side, Easier to Have it as a Change Log in Tracking Security table in the Customer Data Base

Comparison summary
Both Method has Pros and Cons related and selecting the right method is per the use case.
Few Examples:

For Quick Changes in the Members that should affect the dashboard immediately, while the Amount of Members is Small, Use the Basic Solution
For Large Deployment, where changes shouldn't update on the fly, Similar data security for a user among multiple cubes, use the Advanced Solution

Advanced Options and Points

Make Sure that the Security key is not Null, or does not have any Null values in one of the compositions of the Surrogate key
Keep the Identical structure of the Surrogate key in all fact tables
Identical meaning, if starting the Seucrity_key with Manufature_country + '_' + sales_country, this structure should be created in all fact tables.
In case need additional Scopes, the new Scope should be added as part of the Security key, keeping the Identical Structure.
Surrogate_key using '_' is an example and several alternatives can created, keeping a similar structure
Best Practice is to Generate the Surrogate key and all the Logic within the Source Data Base.

Best Regards