Forum Discussion

pgoum's avatar
pgoum
Frequent Visitor
6 years ago
Solved

Row Level Security single role

Hey all!   I have a report concerning sales and salesmen. There exist 3 roles let's say depending on which every user can see different rows from the sales table. If a user has flag=0 or null then ...
  • d_gosbell's avatar
    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.