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.