BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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!