Hi Experts ,
I am working on SQL 2012, SSAS Tabular Model .
I have a concern in Performance issue , after implementing Dynamic Security .
Scenario:
Tabular model contain UserSecurty Table ,to provide access to employee with Limited Product(to implement dynamic security)
UserSecurtyTable Format :
EmployeeID , EmployeeADUserName, ProductID
In The Model Tab I have created a A Role With Read Access , Name of Role is "ProductReadAccess" .
In Role Against Product Dimension I am using below Query:
=Product[ProductKey]=LOOKUPVALUE(UserSecurity[ProductKey],UserSecurity[ADUserName],USERNAME(),UserSecurity[ProductKey],Product[ProductKey])
and FALSE() against UserSecurity Table in Tabular Model .
NOTE : My UserSecurity table contain approx 70,000 rows
My Dynamic Security is working Fine , I see only limited product in Excel report , assign to me in Usersecurity table (Not in terms of performance)
-> Just because I felt that performance was coming as a concern I also create partition in UserSecurity table on EmployeeID , Number of Partiton in UserSecurity Table=Number of Distinct EmployeeID present in it .
But Nothing seems working as expected .
Still I am struggling when I see report pulled from this cube in Excel , Even a refresh of Excel report take more than 1 minute and changing a filter take lots and lots of time .
Please assist.
