SSAS extremely slow after RLS implementation

ovonemty 51 Reputation points
2023-01-31T10:11:57.6+00:00

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.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,237 questions
0 comments No comments
{count} votes