Can you query a SSAS cube while it is processing?

I have always wondered if queries against a SSAS cube while it is processing would be forced to wait the entire time the cube is processing, or just for a few minutes?  Or would the query bomb out?  Surprisingly, I found little information on the internet about this.  Fortunately, with help from SSAS guru’s Chris Webb and Adam Jorgensen, they lead me to an old posting that explained it.  Here is my summation:

You can query a cube while it is processing.  The worst that could happen is near the end of processing there is a brief period of time where your query could be killed or it might be queued, and it would be queued only for a short time, even if the cube processing takes hours.  The reason is the way a cube is processed:

When an cube object is processed a new version of the object is created and this new version is where the processing actually takes place.

For example, the first time you process a dimension you will see in the dimension folder files with a name like 1.(All).astore where ’1′ stands for the version of the dimension.  When you process the dimension again you will see files with version ’2′, so the above file will change to 2.(All).astore.

When the processing is complete a “pending commit” lock is placed on the object that was being processed (and any dependent objects).  If there are any current queries executing against the object in question they will be allowed to continue, and any new queries will be queued up until the current version can be swapped out for the new version.  So the only time a cube query could be delayed is during this swapping, which could be anywhere from a few seconds to a few minutes, depending of course on the size of the object.

After the commit operation the old set of object files are deleted and Analysis Server will start to use the new files.

In this example if the dimension was called Reseller, the DDL definition of the Reseller dimension, which is kept in the Dim Reseller.1.dim.xml file, would be deleted and you would see a new file created called Dim Reseller.2.dim.xml.  If you looked inside this file, you would see <ObjectVersion>2</ObjectVersion>

There is an SSAS property called ForceCommitTimeout that specifies what happens to a query that is executing against the cube when the “pending commit” lock is placed, meaning these queries are blocking it from completing.  ForceCommitTimeout is a signed 32-bit integer property that defines the timeout, in milliseconds, before a pending commit should cancel other commands that preceded the current command, including queries in process.  The default value is 30 seconds (30000 milliseconds).

If the ForceCommitTimeout has expired and there are queries still executing they are cancelled (the receive a “Server: The operation has been cancelled”) and the old version of the object is swapped out for the new one and any queries that were queued up are allowed to commence.

This means that increasing the ForceCommitTimeout will give currently executing queries more time to complete. But it also means that queries that were executed soon after the “pending commit” lock was taken will be stalled for the timeout period before they even start to be executed. This will result in the perception of inconsistent performance as any queries executed during this window might take nearly twice as long to start producing results.

The “pair” to the ForceCommitTimeout setting is the CommitTimeout setting, where you could cause the commit of the process operation to timeout and roll back, allowing currently executing queries to continue to completion.  Seems unlikely you would want to use this as much as the ForceCommitTimeout as you are basically saying that queries are more important than the processing operation.  The default value for this property is zero (0), which indicates that the server will not time out commit operations.

This also leads to another question: If you process a cube and the processing bombs, will the users continue to be able to query the cube?  If you fully process a dimension, such as after making dimension structural changes, but you don’t process the cube that references that dimension (or the cube bombs when you try to process it), the cube will be left in an unprocessed state and it will be unavailable for querying.  Other than that particular case, the users will be able to query the cube.  So if you decide to do a full process on the cube and it errors out, you don’t have to worry that you just made the cube un-accessible.

More info:

Processing Analysis Services Objects

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, SSAS. Bookmark the permalink.

5 Responses to Can you query a SSAS cube while it is processing?

  1. Wes W. says:

    The best process, IMHO, is to have a production instance of SSAS and a “preprod” processing instance. Do all of your processing in “preprod” then sync into production upon successful processing. This approach means the production SSAS cube is always online and available.

    • James Serra says:

      That a great idea Wes, and one I recommend if your cube takes a long time to process, as processing is very resource intensive. Or if you can’t afford the cube being inaccessible for even a minute (although a sync does require a write lock on the target server). You have to balance the benefits of a “preprod” with the cost and effort to maintain another instance and the extra step needed for synching. And also keep in mind how often you would even need to process the cube during work hours. For info on how to sync, check out Push Cube Changes Using Synchronization

      My thoughts on writing this article was for those situations when you normally process the cube nightly during a maintenance window but occasionally upper management requests the cube to be processed during the day and they ask “Will that cause any problems?”. Hopefully, this article will help you to answer that question.

  2. Pingback: Improving cube processing time | James Serra's Blog

  3. Hui Shi says:

    hi james, when you say cube being process, which type of process are you referring to ?
    if it’s process updates, then you can access at any time. If it is full process, that is
    a different story…

  4. Greg Sharrow says:

    Is this the same behavior for a Tabular SSAS cube?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>