2

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?

Brandon McClure
  • 1,329
  • 1
  • 11
  • 32

1 Answers1

2

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)

Brandon McClure
  • 1,329
  • 1
  • 11
  • 32
  • Thank you for this answer. I am using C# script with TOM for processing several tables at once and I am trying to figure out why the MaxParallelism options does not limit the number of tables being processed. I understand you tested this for multiple partitions inside one table, but what about several tables? In C# processing begins only after you call the SaveChanges method. I tried saving it first with MaxParallelism option set but with no effect. – PacoDePaco Mar 07 '19 at 16:25
  • Never mind, it worked :) Setting MaxParallelism = 1 as an option in Model.SaveChanges() method made table processing sequential for me (done it both prior to processing and after requesting the refreshes). – PacoDePaco Mar 08 '19 at 12:42