Sequence command (TMSL)

APPLIES TO:yesSQL Server Analysis Services (starting with 2016)yesAzure Analysis Services

Use the Sequence command to run a consecutive set of operations in batch mode on an instance of Analysis Services. The entire command and all of its component parts must complete in order for the transaction to succeed.

The following commands can be run sequentially, except for the Refresh command which runs in parallel to process multiple objects concurrently.

Request

maxParallelism is an optional property that determines whether Refresh operations run sequentially or in parallel.

The default behavior is to use as much parallelism as possible. By embedding Refresh within Sequence, you can control the exact number of threads used during processing, including limiting the operation to just one thread.

Note

The integer assigned to maxParallelism determines the maximum number of threads used during processing. Valid values are any positive integer. Setting the value to 1 equals not parallel (uses one thread).

Only Refresh runs in parallel. If you modify maxParallelism to use a fixed number of threads, be sure to review the properties on the Refresh command (TMSL) to understand the potential impact. It's possible to set properties in a way that undermines parallelism even when you've made multiple threads available. The following sequence of refresh types will give you the best degree of parallelism:

  • First, specify Refresh for all objects using ClearValues

  • Next, specify Refresh for all objects using DataOnly

  • Last specify Refresh for all objects using Full, Calculate, Automatic or Add

    Any variation on this will break parallelism.

{   
  "sequence":    
    {   
      "maxParallelism": 3,   
      "operations": [   
        {   
          "mergepartitions": {   
            "sources": [   
              {   
                "database": "salesdatabase",   
                "table": "Sales",   
                "partition": "partition1"   
              },   
              {   
                "database": "salesdatabase",   
                "table": "Sales",   
                "partition": "partition2"   
              }   
            ]   
          }   
        },   
        {   
          "refresh": {   
            "type": "calculate",   
            "object": {   
             "database": "salesdatabase"   
            }   
          }   
        }   
      ]   
    }      
}   

Response

Returns an empty result when the command succeeds. Otherwise, an XMLA exception is returned.

Usage (endpoints)

This command element is used in a statement of the Execute Method (XMLA) call over an XMLA endpoint, exposed in the following ways:

  • As an XMLA window in SQL Server Management Studio (SSMS)

  • As an input file to the invoke-ascmd PowerShell cmdlet

  • As an input to an SSIS task or SQL Server Agent job

    You cannot generate a ready-made script for this command from SSMS. Instead, you can start with an example or write your own.

    The [MS-SSAS-T]: SQL Server Analysis Services Tabular (SQL Server Technical Protocol) document includes section 3.1.5.2.2 that describes the structure of JSON tabular metadata commands and objects. Currently, that document covers commands and capabilities not yet implemented in TMSL script. Refer to the topic (Tabular Model Scripting Language (TMSL) Reference) for clarification on what is supported.

See Also

Tabular Model Scripting Language (TMSL) Reference