How to process SSAS cube structures concurrently from PowerShell script using Mircrosoft.AnalysisServices namespace

Question

TL;DR: Is there a way for a PowerShell script calling Microsoft.AnalysisServices functions to process multiple cube structures concurrently?

I have a Microsoft SSAS cube that needs several measure groups processed before the rest of the cube is processed later in the job plan. I have created a PowerShell script that enumerates the measure groups to process and calls measureGroup.Process('ProcessFull') from the Microsoft.AnalysisServices namespace. This works to process the measure group, dimension, et.al.

However, processing the measure groups in this manner doesn't allows SQL Server 2014 to parallelize the processing. A cube that takes on average 2 hours to fully process was running for 7 hours before we killed it.

Is there a way in PowerShell to batch the processes so that they are sent at the same time to the cube? If this could be done, it would allow the server to do concurrent processing instead of one object at a time.

I have taken a look through the documentation on the MSDN as well as consulted Google, but was unable to find an answer. Thanks!

Answer 1

You should check this method in PowerShell: http://ssas-info.com/analysis-services-scripts/1238-powershell-script-to-process-all-dimensions-and-cubes-in-one-db-limiting-workload

Answer 2

Have a look at powershell jobs:

Background Jobs

Here's a quick example that you could adapt to run your measuregroup processing:

$cmd = {
  param($a)
  Write-Host $a
}

$list = @("a","b","c")

$list | ForEach-Object {
  Start-Job -ScriptBlock $cmd -ArgumentList $_
}

It's quite simple, you define your script block in the $cmd variable, this is where you would put your logic around processing the measure group. The $list variable could contain a list of the measure groups to process.

You then start a job for each item in the list, which executes the code in the script block, passing through the item in the list as a parameter. In this example it simply prints out the parameter that you passed in. You can of course pass in as many parameters as you like.

To get the results, you can use the Get-Job cmdlet to check the status of the jobs and Receive-Job to get the output. Remove-Job can then be used to clear finished jobs from the queue.

The following command run after the code above will get the results of all the jobs (in this case just the a,b,c that we passed in and then will remove it from the queue:

Get-Job | ForEach-Object { Receive-Job $_.Id; Remove-Job $_.Id }