Server: The operation has been cancelled

Question

This may be a silly question.. If the server is fully processing a partition, is it expected that all pivot table queries that are running will die with the error "the operation has been cancelled"?

One of our users is getting this error at the exact time that the partition is being reprocessed.

I guess I should just tell them to just live with it?

 

 

 

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 
  <Process>
    <Object>
      <DatabaseID>OlapWarehouse</DatabaseID>
      <CubeID>GL Budget and Activity</CubeID>
      <MeasureGroupID>GL Summary Activity for Corporate G and A</MeasureGroupID>
      <PartitionID>GL Summary Activity for Corporate G and A - 2007</PartitionID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>

</Batch>

Answer 1

Certainly for a single query you should not expect this. The server processes the new information to one side and then once it has completed it switches over from using the old data to using the new data.

I did a demo of pro-active caching once where the cube was constantly updating itself ever few seconds and I was able to drill around in Excel and you could see the figures changing.

Is it Excel pivot tables that you are talking about? if so, which version of Excel?

Have you tried running a profile trace? It may give more insights on the "conversation" that is going on between the pivottable and the SSAS server.

 

Answer 2

Somebody helped me resolve this problem.  Just thought I'd put the resolution here in case anybody does a search.

 

 I suspect what you are seeing is by design behavior where a pending commit lock will cancel long running queries blocking it from completing.  The default value for this is 30 seconds.  Look at the ForceCommitTimeout server property in the SQL Server 2005 Analysis Services (SSAS) Server Properties whitepaper.  I would suggest you set that value high enough that your typical long running queries can complete in the time specified.

 

Answer 3

Hi Darren, David,

 

I've also found that SSAS applies this parameter not only when querying while processing, but when building xmla script with CaptureLog feature while processing on same parent object (different partitions of same cube).

We had lots of cancelled operation when capturing log, but problems went away since setting this parameter (ForceCommitTimeout) to zero.

 

Thank you.