This post shows how to process SSAS Tabular tables and partitions with TMSL. TMSL stands for Tabular Model Scripting Language, and it is used for working with SSAS Tabular databases programmatically starting with compatibility level 1200 (SQL Server 2016). Multidimensional models and previous versions of SQL Server all use Analysis Services Scripting Language (ASSL for XMLA).
A common use for TMSL is automating the processing of tabular models using the SQL Agent, SSIS, PowerShell, or some other application. One typical processing sequence is a series of Process Data commands followed by a Process Recalc at the end. Here’s what the TMSL code looks like in this scenario:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
{ "sequence": { "operations": [ { "refresh": { "type": "dataOnly", "objects": [ { "database": "DatabaseName", "table": "Table1Name", "partition": "Table1Partition" }, { "database": "DatabaseName", "table": "Table2Name", "partition": "Table2Partition" }, { "database": "DatabaseName", "table": "Table3Name" }, { "database": "DatabaseName", "table": "Table4Name" } ] } }, { "refresh": { "type": "calculate", "objects": [ { "database": "DatabaseName" } ] } } ] } } |
Parallel Processing
A major improvement beginning with SQL Server 2016 is parallel processing. In previous SQL Server versions, all commands were processed sequentially. Parallel processing uses more resources, but can result in significantly shorter processing windows. In the TMSL code above, the Process Data commands will be processed in parallel, followed by the Process Recalc.
According to Microsoft, “The default behavior is to use as much parallelism as possible.” You can limit the number of threads by assigning a value to maxParallelism in the sequence object. You can find more information at the Microsoft Doc “Sequence command (TMSL)“.
Just one note: using TMSL is good for small static scripts. Generating dynamic TMSL and/or large scripts is very bad for performance, using TOM from PowerShell or other languages is much better (and simpler).
That’s great to know. Thanks, Marco!