This example is based on SH schema tables.
A new table created for storing user details.The user details where stored in a database table as shown below,
The two users used for this example are UserA and UserB.
Create the Initialization block for populating the group session variable.
This will populate the group values (REGION, PRODUCT in the GROUP session variable based on the user).
Create another session variable to populate the data value for the group.
This will populate the data value for each group based on user.
Now create two groups REGION, PRODUCT in the RPD.
Now go to Group REGION -> Permissions
Go to filter tab ->Add the logical layer table for applying the filter.
Similarly add the filter over other table as well wherever the filter needs to be applied, In this the filter applied over Sales (fact) table as well.
Add filter over the Product group as well based on the table.
Now when a user with no data level security logs in
For UserA,
For UserB,
If UserB selects the following column
Since this did not have product or region table column then also the filter will be applied because we have added the filter over the Sales table for both REGION and PRODUCT group.
The physical query for the above criteria will have this additional filter for region and products.
In the above physical SQL the filters for Region and Product is applied. Like this we can add other table also in the group to enforce the filter to be applied over the tables.
A new table created for storing user details.The user details where stored in a database table as shown below,
The two users used for this example are UserA and UserB.
Create the Initialization block for populating the group session variable.
This will populate the group values (REGION, PRODUCT in the GROUP session variable based on the user).
Create another session variable to populate the data value for the group.
This will populate the data value for each group based on user.
Now create two groups REGION, PRODUCT in the RPD.
Now go to Group REGION -> Permissions
Go to filter tab ->Add the logical layer table for applying the filter.
Similarly add the filter over other table as well wherever the filter needs to be applied, In this the filter applied over Sales (fact) table as well.
Add filter over the Product group as well based on the table.
Now when a user with no data level security logs in
For UserA,
For UserB,
If UserB selects the following column
Since this did not have product or region table column then also the filter will be applied because we have added the filter over the Sales table for both REGION and PRODUCT group.
The physical query for the above criteria will have this additional filter for region and products.
In the above physical SQL the filters for Region and Product is applied. Like this we can add other table also in the group to enforce the filter to be applied over the tables.
Hi,
ReplyDeleteI have never used session variables in rpd. Can you please explain my doubts about the above post.
1. Where's the session variable declared here? you have just shown how to create a init block here but you have not shown how to create the session variables 'Product' and 'Region' which are used. Pls reply whether I am missing something here.
2. The SQL query used in the first init block is like "Select 'Group',USER GROUP from USER_GROUP where USER=:USER;
1. Is USER_GROUP the table used here?
2. Why did you select 'Group' along with the user group column.
3. Where is the system session variable getting initialized?
1.Yes, USER_GROUP is a database table here which may keep information about the user groups and other attributes.
Delete2'Group' is in quotes.So, the results coming from database will look like this:-
ROW1|| Group|| USER GROUP1
ROW2|| Group|| USER GROUP2
3.It gets initialized for every session ..like when a user logs in, a particular session variable will get initialized.
If u login, the session variable will fetch data for u.Thats why the name Session variable.