Cell Security and the Formula Engine Cache

I’ve been looking at a security implementation this week for a customer and investigating what impact it’s having on their cube’s performance. A number of interesting points have come up which are all worthy of a blog entry, but I thought I’d start with what I noticed about cell security. I’d heard from Mosha that cell security was just about the worst feature to use on a cube from a performance point of view because it stops the AS engine from knowing whether a cell is empty or not – and I can see this on my customer’s cube because cold cache queries take a lot longer to run for users who have cell security compared to users who don’t. But I also found another bad side-effect: it seems to stop the AS engine caching the results of calculations.

Here’s the steps I took to repro this in Adventure Works (I used the Simple version but I’m sure it works just the same on the full version):

• Comment out the entire MDX Script apart from the Calculate statement
• Add the following calculated measure to the Script:
  CREATE MEMBER CURRENTCUBE.MEASURES.[Test] AS
  [Measures].[Internet Sales Amount]+1;
• Add a new role to the database and give it ‘Read’ permission on the Adventure Works cube
• Create a new Windows user (without administrator permissions on the cube) and add it to the new role
• Start a Profiler trace, making sure you include the ‘Get Data From Cache’ event.
• Connect to the cube using SQL Management Studio as this user (I used the ‘Run As’ option on the right-click menu) and run the following query:
  SELECT {[Measures].[Internet Sales Amount], [Measures].[Test]} ON 0,
  [Date].[Calendar Year].MEMBERS ON 1
  FROM [ADVENTURE WORKS]
• In Profiler you’ll notice everything’s as you’d expect when you’re running a query on a cold cache: the disk is being hit, data is being read from partitions.
• Run the query again, ie on a warm cache
• In Profiler you’ll see that this time the data used to answer the query is read from cache. There are four ‘Get Data From Cache’ events: two reading data from the measure group cache, which is raw data from the cube; and one each from the flat cache and the calculation cache, which are formula engine caches containing the results of calculations. For more information on these types of cache, see chapter 28 of "Microsoft SQL Server Analysis Services 2005".
• Now, go back to your role and on the Cell Data tab check the Enable Read Permissions checkbox and enter the following expression:
  IIF(1=1, TRUE, FALSE)
  Incidentally, there’s a bug to watch out for here: sometimes you need to click the Edit MDX button and then OK on the resulting dialog to make BIDS aware that the cell security expression has actually been edited. Also although this is a pretty trivial expression, I found that I could not repro the behaviour if I just used the expression:
  TRUE
  for cell security. Clearly AS is able to work out that this expression always returns true, even if if can’t do the same for the first one!
• Next, clear the cache and watching Profiler rerun the query twice.
• Notice that on the second run, you now only see three ‘Get Data From Cache’ events and they are all for the measure group cache. So AS has been able to cache the raw data but not the results of the calculation.

If you imagine a scenario where there are hundreds of users, many connecting through roles with cell security, very complex MDX calculations and queries that take 5-15 seconds to run on a cold cache then you can imagine the kind of impact that cell security can have on performance. Queries that should run instantaneously on a warm cache are consistently taking almost as long to run as they did on a cold cache because the calculations have to be re-evaluated every time. So the moral of this tale is: don’t use cell security unless you absolutely have to.