Handling tabular models with PowerShell

Admittedly I’m not the biggest fan of Visual Studio when it comes to SSAS handling. I’m sure it has a lot of great options when working on regular code, but building cubes feels clumsy. It’s a nightmare to use when you have connection issues and I really don’t want to think about the countless hours of work I have lost due to connection losses to the Analysis Services server. But lo and behold, not all hope is lost, thanks to PowerShell and the Tabular Object Model.

One word of advice. Before editing tabular models this way, you should be familiar with how they work. Otherwise you will either see a lot of error messages or destroy your tabular model right away. The two basic resources you will need are:

The next thing you will need is the SqlServer module in the PowerShell gallery.

Once the module is installed, you can go on connecting to your tabular models using this simple piece of code. Access via PowerShell requires you to have admin rights on the model, but not necessarily on the server.

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
Import-Module SqlServer;
 
[Microsoft.AnalysisServices.Tabular.Server]$server = New-Object Microsoft.AnalysisServices.Tabular.Server;
$server.Connect("my.ssas.server");
$db = $server.Databases["MyTabularModel"];
$model = $db.Model;

Reading from the model

Now that the connection is established, you can start reading from your model. I will show you a few examples, but check out the namespace documentation above for a complete set of classes and methods.

$model.Tables; # Return all tables of the model.
$model.Tables["MyTable"].Partitions; # Returns all partitions of "MyTable".

The objects returned can be used like any other object in PowerShell, so pipe them as much as you need to.

$model.Tables["MyTable"].Partitions | Select-Object Name, SourceColumn, DataType, Type, Expression | Format-Table -AutoSize

Editing the model

A word of warning before you start. While editing the model via PowerShell is a great way to automate things and to avoid a clumsy IDE, there are disadvantages.

There are no help texts and no hints about what you can do, you must know all the options yourself.
There is no dependency tracking. If you rename an object in Visual Studio, all dependencies will be renamed. With PowerShell, that’s your job. If something goes utterly wrong, check out the UndoLocalChanges() method.

Let’s do a very simple change, let’s modify a measure expression. Don’t forget to save your changes to the server afterwards.

$model.Tables["MyTable"].Measures["MyMeasure"].Expression =
  "CALCULATE(SUM(MyTable[Col1],MyTable[IsRelevant] = TRUE())"
;

$model.SaveChanges();

You can also perform more complex tasks, such as creating partitions.

# These are the partitions we want to create.
[string[]]$partitions = @(202101,202102,202103);
 
# I prefer foreach, but you could as well do a "$partitions |" to simply pipe the objects.
foreach ($item in $partitions) {
    $partition_name = $item; # I like a more explicit style.
    $partition_start = "$($item)01" #We want to start this partition on the first of the month.
    $partition_end = ((Get-Date -Year $item.Substring(0,4) -Month $item.Substring(4,2) -Day 1).AddMonths(1)).ToString("yyyyMMdd"); # Calculation for the first of the next month.
 
    Write-Output "-----Generating partition with parameters:-----"
    Write-Output "Partition name: $partition_name";
    Write-Output "Partition start: $partition_start";
    Write-Output "Partition end: $partition_end";
 
    if (-Not $table.Partitions.Contains($item)) { # No need to create partitions that already exist.
        $table.Partitions.Add(
            (New-Object Microsoft.AnalysisServices.Tabular.Partition -Property @{
                Name = "$($partition_name)";
                Source = New-Object Microsoft.AnalysisServices.Tabular.QueryPartitionSource -Property @{
                    DataSource = $model.DataSources["MSSQL"];
                    Query = "
                        select
                              order_date
                            , shipment_date
							, customer_id
							, total_cost
							, parcel_cost
                        from dbo.orders
                        where 1=1
                            and order_date >= '$($partition_start)'
                            and order_date < '$($partition_end)'
                        ;
                    ";
                }
            })
        );
 
        $table.Partitions[$item].RequestRefresh("full"); # In this case we want to refresh the partitions straight away.
    }
};
 
$model.SaveChanges();