562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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.|
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.
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.
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.
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.
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.
SSAS Partition Manager is a third-party program which does what its name suggests!
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!
25 Aytoun Street