Chris Webb's BI Blog

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

What happens when you do a Process Update on a dimension?

with 9 comments

Over the last few days I’ve been involved in an interesting thread on the SSAS forum regarding what happens when you do a Process Update on a dimension. It’s a topic that is not widely understood, and indeed I’ve not known all the details until today, but it’s nonetheless very important: one of the commonest performance-related problems I see in my consultancy work is partitions that have aggregations designed for them, but where those aggregations aren’t in a processed state because a Process Update has been run on one or more dimensions. Anyway, just now Akshai Mirchandani from the dev team posted a really good overview of what actually happens when you run a Process Update on that thread, so I thought I’d copy here to ensure it gets a wider audience:

Here is a quick summary of what happens when you do ProcessUpdate:

1. After the dimension has been updated, the server analyzes the changes that occurred to the dimension. In 2005, this analysis was pretty simple and would often incorrectly detect that major changes had occurred that required clearing of indexes and aggregations. In 2008, this code was improved such that it more often would realize that nothing significant has occurred. It’s a fairly small (but useful) optimization — I guess nobody thought it was worth documenting!

2. Based on this analysis, the server will decide whether or not indexes and aggregations need to be cleared. If no (e.g. because records were only added and not deleted/updated), then the partitions won’t be affected.

3. If indexes/aggregations need to be cleared, then the server will check if ProcessAffectedObjects was enabled — if yes, then instead of clearing the indexes/aggregations it will rebuild the indexes/aggregations.

4. The act of clearing the indexes/aggregations also shows up as "partition processing operations" in Profiler — that’s one of the things that has been confusing some of you.

5. When aggregations are cleared, only the flexible aggregations need to be cleared because we’re guaranteed by the rigid relationships that the members cannot have moved and therefore the rollups cannot have changed. However, indexes can still have changed and therefore you may still see the partition processing jobs kick off to clear the indexes.

6. ProcessIndexes and ProcessClearIndexes take care of building both bitmap indexes (aka map) and aggregations — the context is that both aggregations and bitmap indexes are generically considered "indexes".

Really the main takeaway here is that if you ProcessUpdate a dimension, you should strongly consider either doing ProcessAffectedObjects or an explicit ProcessIndexes on the affected partitions so that bitmap indexes and flexible aggregations get rebuilt. The advantage of explicitly doing ProcessIndexes is that you can bring your cube online earlier and have the indexes/aggregations get processed more lazily in the background — a number of customers prefer to do that because their processing windows are too small to wait for the indexes to get processed.

Also related to this topic, I thought I’d also highlight a great post by Darren Gosbell where he shows how to find out if your aggregations are processed or not:

Written by Chris Webb

May 12, 2010 at 10:11 pm

Posted in Analysis Services

9 Responses

Subscribe to comments with RSS.

  1. Hi I have deployed PPS2007 in a test lab. All went well and without issues. From the Administration Console I configured everything without issues. However, when I went to Excel and tried to use the Report Wizard to create a report, I am asked to select the required model which I do without issues. Then I click Next, where I am asked to select the dimension and Hierarchy which I select OK. Then when I click the memeber selection field I get an error as follows: "Error getting the database context for the model site[2035d974-eeea-4143-9231-7d76089fb68f]can someone help please?regards


    May 13, 2010 at 8:31 am

  2. Hi Hassan,This isn\’t really the right place for this question. Try posting on


    May 13, 2010 at 11:19 am

  3. Hi, Chris. I can not understand what will happen if ProcessUpdate mode selected when a fact table contains missed dimension members.

    Dmitriy Tataurov

    May 12, 2014 at 3:08 pm

  4. Would setting the processing mode to lazyaggregations for measure groups related to a dimension that gets the processupdate command be helpful?

    Chris Ross

    September 3, 2014 at 2:25 am

    • It would mean that there’s one less thing to have to do explicitly, but in general I don’t like the idea of lazy aggregations – I like to be in control of what’s happening and I don’t like the idea of unpredictable performance.

      Chris Webb

      September 3, 2014 at 8:20 am

  5. Can you run Process Cube Default to rebuild any of the aggregations or indexes that were dropped as a result of the dimension update process?


    November 12, 2014 at 10:41 pm

Leave a Reply

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

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 3,714 other followers

%d bloggers like this: