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:
    [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
  • 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:
    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.

4 thoughts on “Cell Security and the Formula Engine Cache

  1. Hi Chris,
    In a writeback enabled cube, I am currently trying to understand an issue that seem to have to do with the cell security specified for the role in “read/write” MDX. So I went here to your blog to look for clues to my problem since I know you have written some excellent articles that could be of help. As of yet I have not found a solution to the problem, but I will investigate further.

    Anyway, if you would have one minute to spare I would be extremely thankful if you could read the forum thread I’ve started on the link below, describing the issue. I’m currently stuck on this issue and it prevents me from using weighted allocation in the writeback cube.

    Here is the link:


    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      I see on the forum you’ve opened a support ticket – it does sound like a bug, to be honest, and your best bet is to get this checked out by MS.

  2. I just want to mention that I solved the issue, or at least I found a workaround. (There are still things about the issue that I don’t understand). Details are in the forum thread.


Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.