Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2016-07-13   |   Comments (1)   |   Related Tips: > 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.

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.


Last Updated: 2016-07-13


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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,

W

 

 

 


Learn more about SQL Server tools