locked
SSAS 2012 Tabular, Read database permission and Row filtering problem RRS feed

  • Question

  • I have Tabular Data Model with a lot of tables (around 100), multiple attributes (columns) and relationships.

    I noticed that some SSRS reports based on that Model are performing much slower if the report’s user has “Read” permission on Tabular SSAS database. If the same user is given “Full control (Administrator)” permission then the report’s execution time is much better (roughly 5-10 times faster).

    I suspect that such behaviour is caused by Row filters. It looks like those filters are doing some checks in the background even they are not needed in my case. In SSAS server properties “Row Filters” tab lists all Tabular db tables with empty DAX filter expressions. If user is given “Full control” permission then that tab is disabled completely as db Administrator has full access to all records by default.

    What can be the cause of that problem?

    If my guess is correct (it is caused by row filtering overheads) is it possible to disable row filtering for “Read” users?


    • Edited by Auck07 Monday, November 4, 2013 5:15 AM
    Monday, November 4, 2013 4:48 AM

Answers

  • Hi Auck07,

    These row filter expressions are evaluated the first time a user queries that table or any related table. For example, a row filter on the Product Subcategory table will be evaluated and cached the first time any downstream table (such as Product or Internet Sales) is queried. However, queries on the Product Category table will not be affected by the row-level security on Product Subcategory, because cross-filtering operates in only one direction over relationships.

    If User1 and User2 are both in the same set of roles, then a complex row filter expression will be evaluated and cached when User1 queries a related table. When User2 queries that same table, the row filter expression will not be evaluated again, as long as it doesn’t reference a function such as USERNAME which differs between users.

    If we need to optimize such queries to reduce the expense of the user’s first query of the day, please refer to the "Limitations of DAX Query Plans" section regarding capturing a query plan or row filter expressions:
    Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services: http://technet.microsoft.com/en-us/library/dn393915.aspx

    Hope this helps.

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Wednesday, November 13, 2013 5:16 AM
    Wednesday, November 6, 2013 8:07 AM