This post demonstrates how to delete SSAS partitions with TMSL and PowerShell. TMSL stands for Tabular Model Scripting Language, and it is used for working with SSAS Tabular databases programmatically at compatibility level 1200 (SQL Server 2016). Multidimensional models and previous versions of SQL Server all use Analysis Services Scripting Language (ASSL for XMLA).

A common use for TMSL is automating the processing of tabular models using the SQL Agent, SSIS, PowerShell, or some other application. Another use could be deleting partitions used for development after deploying a model. That process goes like this:

  1. A partition is created for development purposes that contains a subset of the data you’re working with. It could be a single month, quarter, or other subset. Working with a smaller dataset in the workspace database speeds development.
  2. A standard partition is also created that contains the entire dataset, a full year, or whatever interval is appropriate for partitioning. Partitioning in depth is outside the scope of this post, but remember that over-partitioning can negatively affect performance.
  3. When you deploy the model, you’ll want to delete the development partitions so that you’re free to process other partitions or entire tables without duplicating data. If you have several development partitions, TMSL and PowerShell can be used to quickly delete all of them with a single click instead of tediously clicking around in SSMS.

The sample script below shows how this is done. The sequence command is used to delete multiple partitions in a single transaction. This is similar to the batch command in XMLA. In this example we’re only performing delete operations, but many different operations can be performed in sequence (And some in parallel).

# ---------------------------------------------------------------------------
# Author: Chris Koester
# Comments: This script deletes development partitions in SSAS Tabular 2016
#
# Reference:
#    TMSL: https://msdn.microsoft.com/en-us/library/mt614797.aspx
#    Sequence command: https://msdn.microsoft.com/en-us/library/mt697587.aspx
#    Invoke-ASCmd: https://msdn.microsoft.com/en-us/library/hh479579.aspx
#
# ---------------------------------------------------------------------------

$server = "hostname\instance"
$db = "DatabaseName"

$deleteDevPartitions = @"
{
  "sequence": {
    "operations": [
      {
        "delete": {
          "object": {
            "database": "DatabaseName",
            "table": "TableName1",
            "partition": "TableName1DevPartition"
          }
        }
      },
      {
        "delete": {
          "object": {
            "database": "DatabaseName",
            "table": "TableName2",
            "partition": "TableName2DevPartition"
          }
        }
      }
    ]
  }
}
"@

Invoke-ASCmd -Server $server -Database:$db -Query:$deleteDevPartitions

To run this in your own environment, you’ll need to replace the server and db variables, and the database, table, and partition JSON values.

Reference

*At the time of writing (2017-01-05) the syntax for the sequence command at MSDN appears to be incorrect. “sequence” should be object and not an array.