A Manager of a Country, [email protected] 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 [email protected] 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 [email protected] 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
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).
[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:
[email protected] 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 [email protected]
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.
|
| 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. | Data Security Apply on User Level. |
| Security Table Size | Contains a List of Unique Security Keys. | A large table contains a list of all users with a unique member list by user. |
| 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
Check out this related content:
Academy
Documentation
