Search code examples
powershellssas-tabularssas-2016

Reduce parallelism when processing a SSAS Tabular model using TOM

I process our tabular models (Compatibility level 1200) using the Tabular Object Model and a powershell script. The script uses some metadata to determine what partitions need to be processed for a given table, and then does a process full on those partitions. In some cases, the script will do a Process Full on the entire table (For instance after the first time the model was deployed to the server)

When the script processes an entire table, I want to be able to control how many concurrent queries are executed against the data source at any given time, so in my script, I create a new instance of a Microsoft.AnalysisServices.Tabular.SaveOptions and set the MaxParallelism property to a number between 1 and 10. I then save the changes to the model on the server, and wait for processing to complete.

$serverTable.RequestRefresh([Microsoft.AnalysisServices.Tabular.RefreshType]::Full)
$db.Update( "ExpandFull")
$saveOptions = New-Object Microsoft.AnalysisServices.Tabular.SaveOptions
$saveOptions.MaxParallelism = $maxParallelism   
$result = $db.Model.SaveChanges($saveOptions)

If I monitor the SQL server that the table is connecting to, I see several queries (8 most of the time) from my SSAS box regardless of what I set MaxParallelism to. Reading through the documentation on that property, This value doesn't guarantee parallelism, as the server may enforce other limits. I don't see any server properties that mention parallelism. What are the other limits/why does this property not affect the number of queries that are run at the same time?

Solution

  • The script is calling the methods in the wrong order, so the $maxParallelism value is not set on the server until after the processing is complete!

    --Call the SaveChanges method with the SaveOptions before you RequestRefresh
$saveOptions = New-Object Microsoft.AnalysisServices.Tabular.SaveOptions
$saveOptions.MaxParallelism = $maxParallelism  
$result = $db.Model.SaveChanges($saveOptions)
$db.Update( "ExpandFull")
$serverTable.RequestRefresh([Microsoft.AnalysisServices.Tabular.RefreshType]::Full)

    Testing this with different values for $maxParallelism I can see that SSAS is now starting the same number of queries as the value I passed in. (up to a max of 8)