Please note that individual users should see the total data for all nodes; in other words, the data for each node should be the same for all different cases. The security is to check whether it allows a user to see it.
The challenge: we can not use the traditional RLS filter concept to put it into the hierarchy, as we will filter data out. The other option is to add a huge bridge table between hierarchy and facts and get data not filtered out. However, the problem with this idea is that the combination for the actual situation is too big, which could result in billions of records. Therefore, we need to think about how to mask the hierarchy to what node allows users to see. While all other nodes should be hidden or suppressed, the data join should not be impacted.
Use the script below to get data setup,
Then the hierarchy will be looks as follows
The report looks like the below.
Now the security problem to how to make data level security work. use SQL script below to enhance the data, the reason we use more columns is to have the best performance and easy to compare simply
then we can create another table to assign users to
all nodes, or any nodes, such as
user 1 999999
user 2 1,2
user 3 1,2,4,5
user 4 1,2,5
Create parameter map as about; then we can mask data in the business layer as
L1:
case when
999999 in (#$DataLevelSecurity{$account.personalInfo.userName}#)
or [Data Layer].[HS].[L1_SK] in (#$DataLevelSecurity{$account.personalInfo.userName}#)
then [Data Layer].[HS].[L1] else '' end
L1_Caption:
case when
(999999 in (#$DataLevelSecurity{$account.personalInfo.userName}#) and [Data Layer].[HS].[L1] <> '' )
or ( [Data Layer].[HS].[L1_SK] in (#$DataLevelSecurity{$account.personalInfo.userName}#) and [Data Layer].[HS].[L1] <> '' )
then [Data Layer].[HS].[L1] else '-' end
Now for user 1 is the same as above, as User 1 can see everything.
User 2
FM model:
User 3
FM model:
Report:
User 4
FM model:
Report:
No comments:
Post a Comment