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!

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Those scripts are awesome!
-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 }
Improve this answer

3 Comments

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.
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.