none
SSAS Tabular Model Performance RRS feed

  • Question

  • 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.



    • Edited by Rihan8585 Friday, April 10, 2015 9:02 AM
    Friday, April 10, 2015 9:00 AM

Answers