locked
Why does an mdx query run slower for a person in a user defined role than it runs for a person in the server administrator role? RRS feed

  • Question

  • I've created a user defined role in SSAS 2005 (called AllData). This role has "basic" dimensional security ("Select all members" for all 3 dimensions in the cube). If I execute an MDX query in the query analyzer on the cube the query results return in 20 seconds.

    If I run the same mdx query in the query analyzer for a person in the server administrator role then the query results return in 5 seconds.

    Why does this happen? Are there any good articles to describe why this is happening?

    I assume the difference in run times can be attributed to the checking of access to data, but so far I cannot find any descriptions of how dimensional security is implemented.

    Has anyone else run into this issue?

    Wednesday, February 24, 2010 7:15 PM

Answers

  • This seems to be a cell security issue. The AllData role had "basic" dimensional security on all dimensions, but also had cell data security "Enable read permissions" with the following mdx Measures.CurrentMember is [Measure].[Clients %]. After removing this mdx and unchecking the "Enable read permissions" check box the query ran as fast as the server administrator role.

    Is there some good documentation about how to use and/or how to optimize the use of the cell security features?
    Friday, February 26, 2010 7:57 PM