Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Cell Security and the Formula Engine Cache

with 4 comments

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.

Written by Chris Webb

February 19, 2008 at 4:46 pm

Posted in MDX

4 Responses

Subscribe to comments with RSS.

  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:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b07cd88-e8b0-45f6-8af6-f65bed30f976/update-cube-statement-using-weightedallocaton-gives-type-mismatch-error

    Thanks,
    Martin

    Martin A.

    September 19, 2013 at 1:25 pm

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

      Chris Webb

      September 19, 2013 at 1:38 pm

      • OK Chris, thanks a bunch for taking the time. My hope now is with MS Support :-)

        Martin

        Martin A.

        September 19, 2013 at 1:42 pm

  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.

    Martin

    Martin A.

    September 19, 2013 at 8:47 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,299 other followers

%d bloggers like this: