I have an SSAS that is consumed by some users using Powerbi and excel.
Its being used and works perfectly; but 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 Current 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 too):
Employee Current 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 that doesnt matter much, because it has no fitlers, its to provide Read ALL.