This post shows how to process SSAS Tabular tables and partitions with TMSL. TMSL stands for Tabular Model Scripting Language, and it is used for working with SSAS Tabular databases programmatically starting with compatibility level 1200 (SQL Server 2016). Multidimensional models and previous versions of SQL Server all use Analysis Services Scripting Language (ASSL for XMLA).
A common use for TMSL is automating the processing of tabular models using the SQL Agent, SSIS, PowerShell, or some other application. One typical processing sequence is a series of Process Data commands followed by a Process Recalc at the end. Here’s what the TMSL code looks like in this scenario:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
{ "sequence": { "operations": [ { "refresh": { "type": "dataOnly", "objects": [ { "database": "DatabaseName", "table": "Table1Name", "partition": "Table1Partition" }, { "database": "DatabaseName", "table": "Table2Name", "partition": "Table2Partition" }, { "database": "DatabaseName", "table": "Table3Name" }, { "database": "DatabaseName", "table": "Table4Name" } ] } }, { "refresh": { "type": "calculate", "objects": [ { "database": "DatabaseName" } ] } } ] } } |
Parallel Processing
A major improvement beginning with SQL Server 2016 is parallel processing. In previous SQL Server versions, all commands were processed sequentially. Parallel processing uses more resources, but can result in significantly shorter processing windows. In the TMSL code above, the Process Data commands will be processed in parallel, followed by the Process Recalc.
According to Microsoft, “The default behavior is to use as much parallelism as possible.” You can limit the number of threads by assigning a value to maxParallelism in the sequence object. You can find more information at the Microsoft Doc “Sequence command (TMSL)“.
Just one note: using TMSL is good for small static scripts. Generating dynamic TMSL and/or large scripts is very bad for performance, using TOM from PowerShell or other languages is much better (and simpler).
That’s great to know. Thanks, Marco!
Hi Chris/ Russo,
I have a tabular model with more than 1 billion records and we need a daily full load processing. Is there any way I can achieve parallel partition processing using PowerShell script?
Sanjeet, SSAS Tabular 2016 will process multiple table partitions in parallel by default if processed in the same operation. You can observe this behavior in SSMS if you select multiple table partitions and perform a process data or process full.
See here – Sequence command (TMSL)
Is there anyway I can dynamically refer to a Partition. I have one partition by quarter and I want to refresh the last 2 quarters whatever those maybe in a name formatted like YYYYQ# (ex 2018Q1).
You would need to code a solution (C# or PowerShell) to accomplish this. Microsoft has published a whitepaper and code sample that demonstrates a robust way to do this (See link below). You could perhaps use a simpler script if you wanted a more basic solution.
https://blogs.msdn.microsoft.com/analysisservices/2017/01/17/whitepaper-and-code-sample-for-automated-partition-management/
Hello,
I have a Tabular model and having been trying to get the data to refresh from the queries through TMSL in a SQL Agent job so that I can view updated data. I’ve tried the above with no luck. Any tips on how I can achieve this goal?
Are you getting an error? Have you tested the TMSL script directly in SSMS? If you can isolate the problem to the SQL Agent, perhaps it’s a permissions issue? The account used to run the SQL Agent job needs to have sufficient permissions to process SSAS models.
Hello,
I have two TMSL refresh full script to refresh two separated objects in a Tabular cube :
Let’s say TMSL1 to refresh full a dimension/partition A and TMSL2 to refresh a dimension/partition B (data independant objects).
When i run TMSL1 and TMSL2 at the same time, one wait for the other to finish then it process.
We have tabular 2016 and we know that Tabular 2016 do parallel but only if all tables/partitions are in the same TMSL refresh command.
We cannot do that because TMSL1 and TMSL2 are executed from different locations (international context to feed a tabular model from different hubs).
Do you have an idea on how to fix that ? is there any configuration that could be setted up in server to enable that ?
Many thanks for your retrun,
Regards,
Lhoussine EL OMARI
I’m not aware of a way to process multiple objects concurrently in separate transactions.
HI Chris,
in your example, is the combination of “data only” and the final “calculate” equivalent to a “full” processing of each table ?
I am currently using “full” for all the 20 tables in my tabular. This is because I need to get all data modifications (new record, deleted record, updated records) from the data source.
Do you think this is the right choice ?
Thanks a lot
Alberto
Yes, a Process Data followed by a Process Recalc is equivalent to a Process Full. This is a common pattern if you only need to refresh data in certain tables. If you need to refresh every table in a model, or if the model size is small, a Process Full is the easiest way to process it.
Hello Chris,
I am trying to do a process ADD. I have the XMLA script for the process add which increments the Data daily into a partition. My question is should I be using a partition for the incremental load or just incrementally load the table? The processing time for an incremental load on a table and an incremental load on a partition of the table seem to be the same. Is this because a process add adds the data to the table or the partition and recalculates the hierarchies, etc of the entire database? I am a little confused what a process add is doing when it runs. We have a tabular model SSAS. I’ve read of Process Data and then Recalc but should that be the way to go or just go with a process ADD for incremental load?
Thanks!
Hello Socrates. I’d suggest taking a look at the article below for a detailed description of how Process Add works.
https://www.sqlbi.com/articles/using-process-add-in-tabular-models/
Hi Chris,
I have a Fact table with multiple partitions based on years in Tabular cube. I don’t want to process all of them. Just need to process one(which is the current year). Because my data is not going to changes for older years. How can I do this
Thanks,
How to process the table partitions parallel in tabular cube using TMSL
Hi Chris,
I have big fact table in tabular cube and with month wise partition and how to process the partitions parallel using TMSL.
Thanks
Hello Chris,
Currently I have 5 partitions which get refreshed everyday. If I do dataonly and then recalc then the cube is not accessible by the users till the recalc completes. My recalc requires around 1 hr and till it gets finished no one is able to connect to the cube through excel pivot. Any suggestions here?