Using Tabular Model Scripting Language with SQL Server Analysis Services (SSAS)

By:   |   Comments (1)   |   Related: > Analysis Services Development


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.


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.

New SQL Server Analysis Services Tabular Database

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.

Script out the SQL Server Analysis Services database with TSML

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.

XMLA format for lower level SQL Server Analysis Services databases
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta 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

Comments For This Article

Wednesday, June 28, 2017 - 6:13:43 AM - Waheed Back To Top (58477)

Hi Siddharth,

I am creating a table in SSAS Tabular Model 2016 by using TMSL via SSMS for my development cube but when i go back and check table in my VS 2015 I don't find it.

Can you help me why it is happening even i refresh my model in VS?


Kind regards,





get free sql tips
agree to terms