Create Partitions in SSAS Tabular with Tabular Editor

In this blog let's see how to create Partitions in SSAS Tabular with the help of Tabular Editor, I am using TE3 as it works best when windows is scaled to 200% for high image resolution but you can use TE2 as well.

So I already have a SSAS model deployed which has couple of tables and the model size is around 8M, I know there is no need to create partitions in 8M rows but for this example I need a small model to refresh quickly as the virtual machine only has 8GB RAM allocated.

As you can see in the below image I don't have any partition in the model apart from the one that is created automatically for the working of the model.

So first we need to declare some parameters so that we can streamline how the M queries look for each table, right now you can see the M code is auto generated one but we will make things centralized so that if we make a change it is applicable to all tables.

First we will declare 2 Parameters that are exactly like parameters of Power BI Desktop and 1 Parameter that actually returns a table which will the snapshot of the database.

I am going to show this with PBI Desktop as it is easy to understand what's happening here.

2 Regular parameters for Server and Database Name:

1 parameter that returns snapshot, for this one you actually need to create a query and not a parameter but it is eventually treated as a Parameter/Shared Expression in the Metadata. Mainly because the queries that are not loaded into the model may still participate in other queries so they appear in the TOM heirarchy under Shared Expressions as they can be referenced by other queries.

If I load them into model and use TE3 you will see all 3 appear under Shared Expression.

Now what we can do is copy the code generate by parameters into SSAS Model with TE3, we can create new Shared Expression using TE3 and all we need to provide is the M code and set the Kind to "M"

Now in the M code of tables that are loaded we can reference the Parameters, so under the only partition available in each table instead of using:

I can use:

And I can use the same pattern for each table, but we can be smart and reduce redundancy of the Database parameter by creating a function that takes table name and loads the table which further improces the model.

Now in each table's partion I just need to use the function:

So now we need to one Function that returns the rows for the year respectively.

Partition Function:

Now that the function for partition is ready we can navigate to the table that we want to partition, in this case Sales. And inside the only Partition available we can use the function.

I renamed the partition from "partition" to "2023" now we can right click on this partition and just create copies for other years using the "Duplicate" option.

Now I am going select all partitions and refresh them, I mean you can do that at the table level as well since all partitions are getting refreshed, but you also have option of refreshing individual or some but not all partition at once.

And now the refresh completes and we have our partitions that hold the data :)

Vertipaq Analyzer shows all the partitions with the Rows that we have loaded:

We can also use the C# scripting feature of TE3 to automate the process of creating a partition, if you have experience with C# outside TE3 you can use this code easily within either TE3 or Visual Studio:

#r "Microsoft.AnalysisServices.Tabular"
using Microsoft.AnalysisServices.Tabular;

Server server = new Server();
string connectionString = "SUMMER\\SQL2022";
server.Connect(connectionString);

Database db = server.Databases.GetByName("Partitions w TE3");
Model model = db.Model;
Table sales = model.Tables["Sales"];

int[] years = { 2018, 2017, 2016, 2015 };

foreach(int y in years)
{
    Partition p = new Partition()
    {
        Name = y.ToString(),
        Source = new MPartitionSource
        {
            Expression = $"fxPartitionByYear({y})"
        }
    };

    sales.Partitions.Add(p);
}

model.SaveChanges();
server.Disconnect();

You can further simplify the automation by relying on the TOM Wrapper Library that TE3/TE2 uses:

TOM Wrapper code:

Table sales = Model.Tables["Sales"];

int[] years = { 2018, 2017, 2016, 2015 };

foreach(int y in years)
{
    sales.AddMPartition(y.ToString(), $"fxPartitionByYear({y})"); 
}