| title | Tabular Model Scripting Language (TMSL) Reference | Microsoft Docs | ||
|---|---|---|---|
| ms.custom |
|
||
| ms.date | 03/06/2017 | ||
| ms.prod | sql-server-2016 | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | reference | ||
| ms.assetid | c700d7f8-7e01-4052-a9ad-8200dd4009f2 | ||
| caps.latest.revision | 20 | ||
| author | Minewiskan | ||
| ms.author | owend | ||
| manager | erikre |
Tabular Model Scripting Language (TMSL) is the command and object model definition syntax for tabular databases at compatibility level 1200, created for the SQL Server 2016 version of Analysis Services. TMSL communicates to Analysis Services through the XMLA protocol, where the XMLA.Execute method accepts both JSON-based statement scripts in TMSL as well as the traditional XML-based scripts in Analysis Services Scripting Language (ASSL for XMLA).
Key elements of TMSL include the following:
-
Tabular metadata based on Tabular model semantics. A Tabular model is composed of tables, columns, and relationships. Equivalent object definitions in TMSL are now, not surprisingly, tables, columns, relationships, and so forth.
A new metadata engine supports these definitions. It's available only for models at compatibility level 1200.
-
Object definitions are structured as JSON instead of XML.
With the exception of how the payload is formatted (in either JSON or XML), both TMSL and ASSL are functionally equivalent in how they provide commands and metadata to XMLA methods used for server communication and data transfer.
The easiest way to explore TMSL scripting is using the CREATE, ALTER, DELETE, or Process commands in SQL Server Management Studio on a model you already know. Assuming you're using an existing model, remember to upgrade it to compatibility level 1200 first. See Upgrade Analysis Services for help with this step.
-
Find the command you want to use: Commands in Tabular Model Scripting Language (TMSL)
-
Check the object definition reference for objects used in the command: Object Definitions in Tabular Model Scripting Language (TMSL)
-
Choose a method for submitting TMSL script:
-
XMLA window in Management Studio
-
invoke-ascmd via AMO PowerShell (Invoke-ASCmd cmdlet)
-
Analysis Services Execute DDL Task in SSIS.
-
The following screenshot shows an abbreviated version of the schema, collapsed to show the major objects.
Analysis Services has several scripting languages: ASSL and TMSL. Only Tabular models created at the 1200 compatibility level are described in TMS in JSON format.
Analysis Services Scripting Language (ASSL for XMLA) was the first scripting language, and is still the only scripting language for Multidimensional models and Tabular models at lower compatibility levels (1100 or 1103). In ASSL, tabular models at 110x are described in multidimensional terms, such as cube (for a model) and measuregroup (for a table).
Note
Using SQL Server Data Tools (SSDT), you can upgrade an earlier version Tabular model to use TMSL by switching up its CompatibilityLevel to 1200. Remember that upgrade is irreversible. Prior to converting, back up your model in case you need the original version later.
The following table is the scripting language matrix for Analysis Services data models across different versions, at specific compatibility levels.
| Version and model type | Multidimensional | Tabular 110x | Tabular 1200 |
| SQL Server 2012 | ASSL | ASSL1 | Not Applicable |
| SQL Server 2014 | ASSL | ASSL1 | Not Applicable |
| SQL Server 2016 | ASSL | ASSL1 | TMSL |
1 ASSL is not ideal for Tabular models, but designing and implementing a more semantically correct scripting language required deep changes across the spectrum of the Analysis Services component architecture. Changes of this magnitude can only be made in major version releases, and only when all components impacted by this change can be updated in tandem.
Compatibility Level for Tabular models in Analysis Services
Analysis Services Scripting Language (ASSL for XMLA)
Determine the Server Mode of an Analysis Services Instance