2

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!

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
Gilbrilthor
  • 432
  • 4
  • 14

2 Answers2

2

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

Cédric L. Charlier
  • 777
  • 4
  • 13
-1

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 }
steoleary
  • 8,968
  • 2
  • 33
  • 47
  • In this case, would SQL Server queue those process jobs to happen one after the other? One of the big time savings in SSAS is to have the server process it concurrently, which right now, I wait for the return of each process job. If I could find out if SQL Server did indeed process multiple distinct process calls concurrently, then I think this would work well. – Gilbrilthor Aug 08 '14 at 17:21
  • Not sure on that, you'd have to test, but SQL server can process concurrently so is be surprised if it did in in series rather than parallel. – steoleary Aug 08 '14 at 19:05
  • If you apply this kind of script it will process in series and not in parallel. Indeed, SQL Server will queue the different calls. If you want to process in parallel you should send all the requested dimensions in the same xmla batch command and specify that it should be done in parallel. It's possible with AMO by using the CaptureLog and ExecuteCaptureLog methods on the server object. – Cédric L. Charlier Aug 10 '14 at 16:13