Securing the Cube - Expert Cube Development with SSAS Multidimensional Models - page 282

Cell security
Cell security has by far the biggest negative impact on query performance, and
for this reason alone, you should use it when there is no other option. Remember
also that there are two different types of cell security, Read and Read Contingent;
Read Contingent cell security has more impact on query performance than Read.
The reason why cell security is so bad is because it prevents Analysis Services from
using many important query optimization techniques. For example, in many cases it
appears to completely prevent the use of the Formula Engine cache, so the results of
calculations are not cached from query to query, even when users are querying cells
they do have access to.
Dimension security
Dimension security has less of an impact on performance than cell security, although
it can still lead to noticeably slower queries.
As we've already noted, enabling the Enable Visual Totals option can prevent the
use of aggregations at higher levels of granularity, which means Analysis Services
may need to do more aggregation at query time. Setting the AggregationUsage
property of the attribute that we're securing to Full , and setting it for any attributes
at a higher granularity to None might, as a result, be a good idea to make sure we
don't build any aggregations that the role cannot use.
Dimension security does not stop Analysis Services from caching the result of MDX
calculations, but it does limit its ability to share cached calculated values between
users. Only users that have exactly the same set of permissions and are members
of exactly the same roles can share cached calculated values. So, for example, if
user A ran a query that included a calculated measure, in most cases the second
time user A ran the same query the calculated measure's values would be retrieved
from the Formula Engine cache. However, if user B connected to the cube and ran
the same query, that query would only be able to retrieve values from the Formula
Engine cache if user B had exactly the same security rights as user A. On cubes with
very complex calculations, the ability to share values in the Formula Engine cache
between users can make a big difference to the overall performance of the cube.