How to use the same security dimension table to allow users to access their own data and their manager’s data (row level security in framework manager)

Check list of all posts

Problem
Please see sample below for an organization structure


The requirement is user Employee111 can only see his data and his manager (manager11) data, but not his co-worker data.

To provide a good data model in framework manager, this structure is flatted into the table below ( please see old post How to flatten hierarchy and associations for detail)

Analysis

Given that there is a fact table as below


Employee 111 should see 100 sales.
Employee 111 should see 300 sales for his manager (department)

Case 1: It is easy to get Employee 111 see his own sales by applying data security with user ID level 4 based on active directory Employee111. It is translated into SQL statement like

[Security Dimension Table].[Level4] = ‘Employee111’

Employee 111 will get 100 sales


Case 2: Apply the similar data security, we can assign active directory Manager11 to Level3 . then It is translated into SQL statement like

[Security Dimension Table].[Level3] = ‘Manager11’

Employee 111 will get 300 sales for his manager


The issue here is how to assign this data security with two different cases.

Solution

The solution is to create two different security dimension query subjects with the same physical table, then assign security to these two different query subjects, and then create star scheme with these two query subjects

The detail implementation is listed below

Step1: Create two mode query subjects based on same security dimension table:

[Security Dimension Table level4]

[Security Dimension Table level3]

Step 2: Assign [Security Dimension Table level4]  for level4 users in active directory, while assign [Security Dimension Table level3]  for level3 user group in active directory


Step 3: Create scheme using two Security Dimension Tables with id join

Sales ------ [Security Dimension Table level4]  (Sales.id = [Security Dimension Table level4] .id)

Sales ------ [Security Dimension Table level3] (Sales.id = [Security Dimension Table level3] .id)

Step 4: Use [Security Dimension Table level4]  when generating report based on level4, and use [Security Dimension Table level3]  when generating report based on level3

The report should looks like follows

Note

Please note that data security only apply when dimension query subject with data security is used in report.  In other word, even you assign data security for a query subject, but the query subject is NOT used in report, the data security won’t apply at all. Please see old post for row level security for detail.

The idea above could become generic by applying the similar situation, such as association.