Forum Discussion
Row Level Security single role
- 6 years ago
So your issue is that in both of your LOOKUPVALUE expressions that multiple values could be returned and this function will throw and error in this case.
You could work around this as follows by adding a second lookup condition for the key_sales or cust_code respectively:
IF ( [user_logged_condition] = "View All", 1 = 1, IF ( [user_logged_condition] = "Items", Sales_Fact_View[key] = LOOKUPVALUE ( items_access[key_sales] , items_access[email], [user_logged] . items_access[key_sales], Sales_Fact_View[key] ), IF ( [user_logged_condition] = "Customers", Sales_Fact_View[customer] = LOOKUPVALUE ( customers_access[cust_code], customers_access[email], [user_logged], customers_access[cust_code], Sales_Fact_View[customer] ), 1 = 0 ) ) )But from a performance perpective I think a better approach would be to have 3 roles then setup 3 security groups and put one group in each role then add users to one of those groups. That way your role expressions can be simple (and therefore faster), they can be put on the smaller dim tables (and therefore will be faster) and you don't need to edit the roles, you just put users in the different security groups.
So your issue is that in both of your LOOKUPVALUE expressions that multiple values could be returned and this function will throw and error in this case.
You could work around this as follows by adding a second lookup condition for the key_sales or cust_code respectively:
IF (
[user_logged_condition] = "View All",
1 = 1,
IF (
[user_logged_condition] = "Items",
Sales_Fact_View[key]
= LOOKUPVALUE ( items_access[key_sales]
, items_access[email], [user_logged]
. items_access[key_sales], Sales_Fact_View[key]
),
IF (
[user_logged_condition] = "Customers",
Sales_Fact_View[customer]
= LOOKUPVALUE (
customers_access[cust_code],
customers_access[email], [user_logged],
customers_access[cust_code], Sales_Fact_View[customer]
),
1 = 0
)
)
)
But from a performance perpective I think a better approach would be to have 3 roles then setup 3 security groups and put one group in each role then add users to one of those groups. That way your role expressions can be simple (and therefore faster), they can be put on the smaller dim tables (and therefore will be faster) and you don't need to edit the roles, you just put users in the different security groups.