Five methods for creating partitions of SSAS Tabular tables programmatically
Need to generate a partition for a fact table for each year, product or region? This blog gives five approaches, so that you can choose which method is for you.

Posted by Andy Brown on 23 July 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Dynamically partitioning tables in SSAS Tabular data models

So here's the problem: I have a table of purchases, and want to create one partition for each year between 2015 and 2018.

Table of purchases

I have a fact table like this, and want to split into partitions (let's say one for each year).

I could set up my partitions manually using the SSAS Tabular menu, but I want to programmatically create them all in one go.  How to proceed?

This blog doesn't give a full answer, but instead summarises all of the approaches that you can take.

Method 1 of 5 - use the TMSL Scripting Language

The Tabular Model Scripting Language (TMSL) allows you to create, delete and amend tables, data sources, partitions, roles and the like.  To see what it looks like, use Management Studio to connect to an Analysis Services database and right-click on a table to script it:

Scripting a table

You can do this in a normal database, but because you're connected to an SSAS database it will generate TMSL, not SQL.

Here's the start of the script generated by this:

Start of TMSL

The start of the script generated. The file (if saved) has an .XLMA extension.

 

You can use this site to see how to write TLMS to generate partitions.

Method 2 of 5 - use .NET programming

If you're most comfortable programming in C#, you can connect to the Microsoft.AnalysisServices.Tabular namespace and manipulate its objects:

Sample C# code

If this sort of thing is meat and drink to you, go to this Microsoft site giving you more details.

Method 3 of 5 - use SSIS

This is similar to the method above, since it involves lots of coding in a language like C#:

SSIS task

This method uses either this SSIS task to run an XMLA file that you've created dynamically, or uses an SSIS script task.

 

You can see more details of how to create dynamic partitions in tabular models within SSIS here.

Method 4 of 5 - use Powershell Scripts

If you know the PowerShell scripting language, you can use code like this:

PowerShell

If this floats your boat, you can see full details on how to proceed here.

Method 5 of 5 - use a Third Party Add-in

SSAS Partition Manager is a third-party program which does what its name suggests!

Conclusions and Recommendations

So ... which one would I choose? It's all down to what you currently know.  I know C# and SSIS, so I'd probably go for the 3rd solution, but if you're a PowerShell guru I'm sure the 4th method would make more sense. 

However, the honest answer to which solution I'd choose is ... none of them.  I'd like a solution which runs within my SSAS Tabular project, and this doesn't yet seem to exist!

This blog has 0 threads Add post