Edit

Share via

Performing Batch Operations (XMLA)

  • Article

You can use the Batch command in XML for Analysis (XMLA) to run multiple XMLA commands using a single XMLA Execute method. You can run multiple commands contained in the Batch command either as a single transaction or in individual transactions for each command, in serial or in parallel. You can also specify out-of-line bindings and other properties in the Batch command for processing multiple Microsoft SQL Server SQL Server Analysis Services objects.

Running Transactional and Nontransactional Batch Commands

The Batch command executes commands in one of two ways:

Transactional
If the Transaction attribute of the Batch command is set to true, the Batch command run commands all of the commands contained by the Batch command in a single transaction-a transactional batch.

If any command fails in a transactional batch, SQL Server Analysis Services rolls back any command in the Batch command that ran before the command that failed and the Batch command immediately ends. Any commands in the Batch command that have not yet run are not executed. After the Batch command ends, the Batch command reports any errors that occurred for the failed command.

Nontransactional
If the Transaction attribute is set to false, the Batch command runs each command contained by the Batch command in a separate transaction-a nontransactional batch. If any command fails in a nontransactional batch, the Batch command continues to run commands after the command which failed. After the Batch command tries to run all the commands that the Batch command contains, the Batch command reports any errors that occurred.

All results returned by commands contained in a Batch command are returned in the same order in which the commands are contained in the Batch command. The results returned by a Batch command vary based on whether the Batch command is transactional or nontransactional.

Note

If a Batch command contains a command that does not return output, such as the Lock command, and that command successfully runs, the Batch command returns an empty root element within the results element. The empty root element ensures that each command contained in a Batch command can be matched with the appropriate root element for that command's results.

Returning Results from Transactional Batch Results

Results from commands run within a transactional batch are not returned until the entire Batch command is completed. The results are not returned after each command runs because any command that fails within a transactional batch would cause the entire Batch command and all containing commands to be rolled back. If all commands start and run successfully, the return element of the ExecuteResponse element returned by the Execute method for the Batch command contains one results element, which in turn contains one root element for each successfully run command contained in the Batch command. If any command in the Batch command cannot be started or fails to complete, the Execute method returns a SOAP fault for the Batch command that contains the error of the command that failed.

Returning Results from Nontransactional Batch Results

Results from commands run within a nontransactional batch are returned in the order in which the commands are contained within the Batch command and as they are returned by each command. If no command contained in the Batch command can be successfully started, the Execute method returns a SOAP fault that contains an error for the Batch command. If at least one command is successfully started, the return element of the ExecuteResponse element returned by the Execute method for the Batch command contains one results element, which in turn contains one root element for each command contained in the Batch command. If one or more commands in a nontransactional batch cannot be started or fails to complete, the root element for that failed command contains an error element describing the error.

Note

As long as at least one command in a nontransactional batch can be started, the nontransactional batch is considered to have successfully run, even if every command contained in the nontransactional batch returns an error in the results of the Batch command.

Using Serial and Parallel Execution

You can use the Batch command to run included commands in serial or in parallel. When the commands are run in serial, the next command included in the Batch command cannot start until the currently running command in the Batch command is completed. When the commands are run in parallel, multiple commands can be executed simultaneously by the Batch command.

To run commands in parallel, you add the commands to be run in parallel to the Parallel property of the Batch command. Currently, SQL Server Analysis Services can run only contiguous, sequential Process commands in parallel. Any other XMLA command, such as Create or Alter, included in the Parallel property is run serially.

SQL Server Analysis Services tries to run all Process commands included in the Parallel property in parallel, but cannot guarantee that all included Process commands can be run in parallel. The instance analyzes each Process command and, if the instance determines that the command cannot be run in parallel, the Process command is run in serial.

Note

To run commands in parallel, the Transaction attribute of the Batch command must be set to true because SQL Server Analysis Services supports only one active transaction per connection and nontransactional batches run each command in a separate transaction. If you include the Parallel property in a nontransactional batch, an error occurs.

Limiting Parallel Execution

An SQL Server Analysis Services instance tries to run as many Process commands in parallel as possible, up to the limits of the computer on which the instance runs. You can limit the number of concurrently executing Process commands by setting the maxParallel attribute of the Parallel property to a value indicating the maximum number of Process commands that can be run in parallel.

For example, a Parallel property contains the following commands in the sequence listed:

  1. Create

  2. Process

  3. Alter

  4. Process

  5. Process

  6. Process

  7. Delete

  8. Process

  9. Process

The maxParallel attribute of this Parallel property is set to 2. Therefore, the instance runs the previous lists of commands as described in the following list:

  • Command 1 runs serially because command 1 is a Create command and only Process commands can be run in parallel.

  • Command 2 runs serially after command 1 is completed.

  • Command 3 runs serially after command 2 is completed.

  • Commands 4 and 5 run in parallel after command 3 is completed. Although command 6 is also a Process command, command 6 cannot run in parallel with commands 4 and 5 because the maxParallel property is set to 2.

  • Command 6 runs serially after both commands 4 and 5 are completed.

  • Command 7 runs serially after command 6 is completed.

  • Commands 8 and 9 run in parallel after command 7 is completed.

Using the Batch Command to Process Objects

The Batch command contains several optional properties and attributes specifically included to support processing multiple SQL Server Analysis Services projects:

  • The ProcessAffectedObjects attribute of the Batch command indicates whether the instance should also process any object that requires reprocessing as a result of a Process command included in the Batch command processing a specified object.

  • The Bindings property contains a collection of out-of-line bindings used by all of the Process commands in the Batch command.

  • The DataSource property contains an out-of-line binding for a data source used by all of the Process commands in the Batch command.

  • The DataSourceView property contains an out-of-line binding for a data source view used by all of the Process commands in the Batch command.

  • The ErrorConfiguration property specifies the way in which the Batch command handles errors encountered by all Process commands contained in the Batch command.

    Important

    A Process command cannot include the Bindings, DataSource, DataSourceView, or ErrorConfiguration properties, if the Process command is contained in a Batch command. If you must specify these properties for a Process command, provide the necessary information in the corresponding properties of the Batch command that contains the Process command.

See Also

Batch Element (XMLA)
Process Element (XMLA)
Processing a multidimensional model (Analysis Services)
Developing with XMLA in Analysis Services