CODEWITHSUNDEEP
powerbidaxssasssas-tabularrow-level-security
Chicago1988
Chicago1988

Reputation: 684

Slow SSAS RLS implementation with DAX

I have published my .pbix, it works great, speed is fine. I consume from SSAS. Now, I have introduced some RLS security and for people accessing with limited permissions, it is awfully slow…

How can I troubleshoot this?

I have 3 roles.

Role1: (everyone has access):

RLS

Employee table: =[NT Username] = USERNAME()

Projects table:

=[Key_Project] IN
SELECTCOLUMNS(
    FILTER(
        'Engagement Role'
        ,'Engagement Role'[UserName] = USERNAME()
        )
    ,"Key_project"
    ,'Engagement Role'[Key_project])

Role2: (everyone has access):

Employee table:

=[TeamLeadEmployeeID] IN
SELECTCOLUMNS(
    FILTER(
        'Employee Current'
        ,'Employee Current'[NT UserName] = USERNAME()
    )
    ,"RLS_Ids"
    ,[Employee Number])

Projects table:

= VAR MyEmployeeNumber=
SELECTCOLUMNS(
FILTER(
'Employee Current'
,'Employee Current'[NT Username]=USERNAME()
)
,"EmployeeNumber"
,'Employee Current'[Employee Number]
)


VAR My_Employees =
SELECTCOLUMNS(
    FILTER(
'Employee Current'
,'Employee Current'[TeamLeadEmployeeID] = MyEmployeeNumber
) ,"EmployeeNumber"
    ,'Employee Current'[Employee Number]
)

RETURN
[Key_project] IN
SELECTCOLUMNS(
    FILTER(
        'Engagement Role'
        ,'Engagement Role'[Employee Number] IN My_Employees
    )
    ,"Key_project"
    ,'Engagement Role'[Key_project]
)

(the 3rd role doesnt really matter, it has no filters is Read all access).

Upvotes: 1

Views: 113

Answers (1)

Ryan B.
Ryan B.

Reputation: 3665

You don't want to set up your security in this way if you can avoid it. Take your first example -- security filter iterates over every row of your 'Projects' table, so it calculates that SELECTEDCOLUMNS for every row of the table. Depending on the size of these tables, that could be a lot of extra work and it totally sidesteps the advantages of your columnar model.

You want to filter on the table that actually has the value to match USERNAME() in it, and then let that filter propagate throughout your model along existing relationships.

So for Role1, you wouldn't build an RLS filter for the 'Projects' table, you'd only put one on 'Engagement Role', and all it would look like is [UserName] = USERNAME()

There would also need to be a relationship between 'Engagement Role' and 'Projects' on [Key_Projects]. The filter direction on the relationship should go from 'Engagement Role' to 'Projects.'

I think you'll find that this is quite a bit more performant.

Upvotes: 3

Related Questions