By: Siddharth Mehta | Comments (1) | Related: > Analysis Services Development
Problem
Analysis Services Scripting Language (ASSL) is the scripting language for SQL Server Analysis Services (SSAS) tabular as well as multi-dimensional models. It has been the primary scripting language since the inception of SSAS. Tabular Model Scripting Language (TMSL) is the new scripting language introduced in SQL Server 2016. What is this new scripting language? How can I use it? What are the capabilities of this scripting language ? We will address all these questions in this tip.
Solution
What is TMSL?
TSML is a JSON based scripting language that was introduced with SSAS 2016 for Tabular models only having a compatibility level of 1200. TSML and ASSL are functionally equivalent, with the only difference of the command syntax and format for object definitions. ASSL uses XML based syntax while TSML used JSON based syntax. Both these scripting languages use XMLA protocol to execute the scripts. TMSL can be used for a variety of database operations like DML tasks, data refresh as well as other administrative tasks.
How to use TMSL for lower version of SSAS Tabular?
One can consider upgrading lower version of SSAS Tabular to SSAS 2016, by upgrading the compatibility level property of the model using the latest version of SSDT. Once the upgraded model is re-deployed, TMSL can be used with the model. If the compatibility level of the model is lower than 1200, TMSL cannot be used with the model.
How to use TMSL?
One of the easiest methods of using TMSL is by using SQL Server Management Studio (SSMS). We assume that you have latest version of SSMS as well as SSAS Tabular 2016 installed on your development machine. Follow the below steps to exercise the use of TMSL commands.
1: Open SSMS and log on to your SSAS Tabular 2016 instance. As the TMSL scripts using XMLA protocol, open a new XMLA query window. We will try to create a new SSAS Tabular database using the CreateOrReplace TMSL command. Copy a sample script from this command reference and paste it into your XMLA query. Execute this script and a new database will get created. If you check the properties of this database, it should be as shown below.
2: Right click the database and select the menu option Script -> Script Database As, and you should be able to find the command options specific to TSML. These options are only available for tabular database objects having a compatibility level of 1200 i.e. SQL Server 2016.
3: In case you compare it with any SSAS Tabular database of a lower compatibility level, you will notice that the scripting options are different. For example, you will not find the "CreateOrReplace" scripting option for databases with a lower compatibility level. Also the script generated for the lower version of tabular databases would be of XMLA format and not JSON as in the case of a tabular database having a compatibility level of 1200.
Next Steps
- Refer to the TMSL command reference and try out different commands to learn about the syntax as well as the structure of TMSL commands.
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
