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)
AnalysisGiven 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.
SolutionThe 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
NotePlease 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.