Member based security & aggregations

Performance question:

We are using member based security, which restricts at the lowest level of our hierarchies. When a user connects to AS we are using the CustomData attribute of the connection string, which passes in a user ID which is resolved to a keyset. If we are quering a few levels higher than the lowest level, and an aggregation has been created at the level being queried, does the engine "crawl" the hierarchy to determine if it can use the aggregation (provided the user has access to all lowest level members), or will abandon the aggregation and sum them up since it is already touching the lowest level?

If anyone has any insight, it would be much appreciated.

Thank you in advance,

John Hennesey

Depends on how you set the Visual Totals attribute on the dimension attribute permission. If it is set to False, which is the default, your totals will reflect all child values whether or not the child is available per the security setting. This helps to keep performance high in the cube and in most situations does not cause problems so long as end-users are aware of why their totals do not match the values for the members visible to them.

If you set the property to true, totals will reflect just those members available to the end user.

B.

|||

And to continue on from Bryan, visual totals is known to reduce performance. I don't know if it actually checks if a given user has access to all the children of a given member and then uses the higher level member, or if it will always add up the applicable members of the allowed set. I think it might be intersecting the allowed set and adding up the results, but I don't know for sure, this is just a guess.

Either way you are better to set your security at the highest level that you can. If you want a person to see all the cities in a given state, given them access to the state member rather than to an explicit list of cities.

|||

Bryan / Darren -

Thank you both for your quick responses - certainly not what I was expecting (or hoping for); it is a bummer because our security is set at the lowest level, but I completely understand why it works this way. Oh well.

Thanks

John Hennesey