CODEWITHSUNDEEP
ssasxmla
Suresh
Suresh

Reputation: 156

Running SSAS XMLA queries in parallel

I have two xmla queries (to process SSAS Cubes) that have been embedded in two separate SQL Server scheduled jobs. These jobs get triggered by some code at random time and there may be a time when both of the jobs get triggered at the same time. In this case one job (actually XMLA query associated with that scheduled job) get precedence and other keeps waiting till the prior finished. I would like to execute both xmla queries simultaneously. Is that possible. The structure of both XMLA is as below:

  <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>SSAS Database ID here</DatabaseID>
        <CubeID>Cube ID here</CubeID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

Looking for the possible suggestions/answers.

Upvotes: 1

Views: 2296

Answers (1)

FrankPl
FrankPl

Reputation: 13315

If both processing instructions access a common object (partition or dimension), then one must wait for the other to finish. Analysis Services - being optimized for OLAP - uses a very coarse granular locking model: Each write access to an object like a partition or a dimension places an write lock on this object which is only released after the job finishes - be that successful or with an error.

In case both processing jobs only access different objects, there would be no conflict, and they could run in parallel (e. g. job 1 processing dimensions A and B and measure group X, and job 2 processing dimension C and D and measure groups Y and Z).

This coarse granularity of locking is one of the aspects on being fast, as there is no need to check for locks on each data row you access - as is the case for most relational databases , which causes some overhead.

Upvotes: 1

Related Questions