Tabular Model Scripting Language (T-SQL)

The Tabular Model Scripting Language (T-SQL) is a powerful, JSON-based scripting language used to define and manage tabular models in SQL Server Analysis Services (SSAS) and Azure Analysis Services (AAS). It allows for automation, version control, and programmatic manipulation of your tabular data models.

What is T-SQL for Analysis Services?

T-SQL for Analysis Services is an object model that represents the metadata of a tabular model. You can interact with this model by sending JSON-formatted scripts to the Analysis Services engine. This language enables operations such as:

Creating, modifying, and deleting tables, columns, relationships, and measures.
Importing and processing data.
Managing roles and security.
Deploying and synchronizing models.

Key Concepts

The T-SQL scripting language is structured around a hierarchical JSON object model. Some core components include:

Model: The root object representing the entire tabular database.
Tables: Collections of rows and columns.
Columns: Fields within a table.
Relationships: Links between tables.
Measures: Calculations performed on data.
Partitions: Subdivisions of tables for data management.
Roles: Security constructs for data access.

Getting Started with T-SQL

You can execute T-SQL scripts using various tools:

SQL Server Management Studio (SSMS): Provides a dedicated editor for T-SQL scripts.
Visual Studio with Analysis Services Projects: Allows for script development and deployment.
PowerShell: Using the SqlAS commandlets or SMO objects.
REST APIs: For programmatic access from applications.

Example: Creating a Simple Table

Here's a basic example of a T-SQL script to create a simple "Products" table:

                json
                
{
    "script": "{\n  \"create\": {\n    \"object\": {\n      \"database\": \"AdventureWorksDW2022\",\n      \"schema\": \"\\\"dbo\\\"\",\n      \"table\": \"\\\"Products\\\"\"\n    },\n    \"elements\": [\n      {\n        \"column\": {\n          \"name\": \"ProductID\",\n          \"dataType\": \"int\",\n          \"sourceColumn\": \"ProductID\",\n          \"isKey\": true\n        }\n      },\n      {\n        \"column\": {\n          \"name\": \"ProductName\",\n          \"dataType\": \"string\",\n          \"sourceColumn\": \"Name\"\n        }\n      },\n      {\n        \"column\": {\n          \"name\": \"ProductNumber\",\n          \"dataType\": \"string\",\n          \"sourceColumn\": \"ProductNumber\"\n        }\n      }\n    ]\n  }\n}"
}

Example: Creating a Measure

This script adds a measure to calculate the total sales amount:

                json
                
{
    "script": "{\n  \"alter\": {\n    \"object\": {\n      \"database\": \"AdventureWorksDW2022\",\n      \"table\": \"\\\"Sales\\\"\"\n    },\n    \"elements\": [\n      {\n        \"measure\": {\n          \"name\": \"Total Sales\",\n          \"expression\": \"SUM(Sales[SalesAmount])\"\n        }\n      }\n    ]\n  }\n}"
}

Microsoft Learn