Why not to use the external assembly approach for dynamic security

Reposted from Chris Webb's blog with the author's permission.

If you’re familiar with dynamic security in SSAS, you probably know there are two basic ways of implementing it: the measure group approach, where security credentials are stored in a measure group inside the cube and the MDX set expression in your role queries this measure group to work out which members each user has access to; and the external assembly approach where the role uses a custom function MDX function from an external assembly to get the same information. Both approaches are covered in some detail in “Expert Cube Development” (still available in all good bookstores, BTW) but I’ve always preferred to use the measure group approach because a) I’m a rubbish .NET developer, and much more at ease modelling data into a measure group, and b) I’ve heard lots of complaints about opening a connection being really slow when you’re using the external assembly approach.

Anyway, to cut a long story short, I had the pleasure of meeting Gerhard Brückl (you may know him from the SSAS MSDN Forum) at SQLBits the other month and he had an interesting question for me: he’d been using the external assembly approach and found that once a user had connected to the cube once their security credentials were cached and the assembly wasn’t called again, even if the user disconnects and reconnects, until the cube’s cache was cleared. While this is good thing from a performance point of view, it has one big drawback as Gerhard pointed out: if the user’s security rights subsequently change the cube will not pick this up immediately. And of course this means that a user may have access to data they really shouldn’t have access to – which is a potentially big problem.

This initiated an interesting discussion with Akshai Mirchandani and various other SSAS obsessives about whether this could be worked around. The short answer is that it can’t be, at least not easily. You can execute an XMLA ClearCache on the whole cube but this might affect query performance quite significantly; you can reduce the impact by clearing the cache of just one measure group in your cube (you could even create a dummy measure group for this purpose) since this will clear the scope cache where the permissions are stored, without affecting the SE caches of the other measure groups. But you’re still left with the problem of knowing when to clear the cache: you’d need to write some code somewhere that checks to see if permissions have changed and runs a ClearCache if they have. As a result of all this, I’d say that if you’re using dynamic security, your permissions change regularly and you need the cube to reflect these changes as soon as possible, you should avoid using the external assembly approach altogether and use the measure group approach instead.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .