Delete SSAS Tabular Partitions with C#

This post shows how to delete SSAS Tabular partitions with C#. This is an improvement over a PowerShell script I previously wrote about, Delete SSAS Partitions with TMSL and PowerShell, because it is more dynamic and doesn’t require a manually-authored TMSL script.

Specifically, the code below deletes all partitions that contain a specified suffix, which is useful for quickly deleting all partitions used for development purposes. I’ve worked with SSAS models that required several development partitions in order to prevent the workspace database from growing too large. Deleting them manually after a deployment to a server would be tedious, and it would be easy to forget one and end up processing duplicate data. Not that I’ve ever done that ;)

For an example let’s look at the Fact.Purchase table in the Wide World Importers data warehouse database. The query to load the entire table looks like this (Ideally a view should be used):

SELECT [Purchase Key]
      ,[Date Key]
      ,[Supplier Key]
      ,[Stock Item Key]
      ,[WWI Purchase Order ID]
      ,[Ordered Outers]
      ,[Ordered Quantity]
      ,[Received Outers]
      ,[Package]
      ,[Is Order Finalized]
      ,[Lineage Key]
  FROM [Fact].[Purchase]

If this table was really big, a development partition might pull in just a single year as shown below. This partition could be called Purchase - DEV.

SELECT [Purchase Key]
      ,[Date Key]
      ,[Supplier Key]
      ,[Stock Item Key]
      ,[WWI Purchase Order ID]
      ,[Ordered Outers]
      ,[Ordered Quantity]
      ,[Received Outers]
      ,[Package]
      ,[Is Order Finalized]
      ,[Lineage Key]
  FROM [Fact].[Purchase]
  WHERE [Date Key] BETWEEN '20160101' AND '20161231'

For this example I’ve created four partitions with the suffix “DEV”. Here’s a list of every partition in the model:

City                 City
Customer             Customer
Date                 Date
Employee             Employee
Payment Method       Payment Method
Stock Item           Stock Item
Supplier             Supplier
Transaction Type     Transaction Type
Movement             Movement
Movement             Movement - DEV
Order                Order
Order                Order - DEV
Purchase             Purchase
Purchase             Purchase - DEV
Sale                 Sale
Sale                 Sale - DEV
Stock Holding        Stock Holding
Transaction          Transaction

If you wanted to delete these partitions after deploying the model to the server, it would take at least 20 mouse clicks in SQL Server Management Studio. That would get old very fast with frequent deployments. Thankfully C# can do this work for us very quickly.

C# Code

Per usual, I’m running the script in LINQPad. The code works by marking for deletion every partition that contains the suffix specified as the devPrtnSuffix variable. I’ve commented out the line the performs the deletion, so that you can first view the partitions that would be deleted. If the partitions printed to the console are the ones that you intend to delete, you can comment out the Console.WriteLine statement and uncomment the line that performs the deletion.

// Deletes all SSAS partitions that contain a specified suffix
// This can be useful for deleting all development partitions quickly after a deployment

class Program
{
    static void Main()
    {
        var instanceName = @"localhost\sql2016";
        var dbName = "Wide World Importers";
        var devPrtnSuffix = "dev";
        var devPrtnSuffixLen = devPrtnSuffix.Length;

        using (Server server = new Server())
        {
            server.Connect(instanceName);

            using (Database db = server.Databases[dbName])
            {
                Model model = db.Model;

                foreach (Partition partition in model.Tables.SelectMany(t => t.Partitions))
                {
                    //Flag partitions with specified suffix for deletion
                    if (partition.Name.Right(devPrtnSuffixLen).ToLower() == devPrtnSuffix)
                    {
                        Console.WriteLine("{0,-20} {1}", partition.Table.Name, partition.Name);
                        //model.Tables[partition.Table.Name].Partitions.Remove(partition.Name);
                    }
                }
                model.SaveChanges();
            }
        }
    }
}

static class Extensions
{
    public static string Right(this string value, int length)
    {
        return value.Substring(value.Length - length);
    }
}

Reference

• Tabular Object Model (TOM)