This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Method 1 of 5 - use the TMSL Scripting Language
Method 2 of 5 - use .NET programming
Method 3 of 5 - use SSIS
Method 4 of 5 - use Powershell Scripts
Method 5 of 5 - use a Third Party Add-in
Conclusions and Recommendations

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.

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:

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:

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:

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#:

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:

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!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post