SSAS Parallel MaxParallel Issues


You may have got very excited when you find out that you can control the number of partitions you can process in your processing process, aren't you? I was at least.

We have lots of cubes and they are very resource intensive so we couldn't afford to do process all of the partitions in every run. We have designed a custom solution to process X number of partitions in the day job and Y number of partitions in the minute/hour job, ofcourse X is bigger than Y which mean we process more partitions in the day job than minute /hour job.

Our SSAS servers are less busy when compared to the SQL server which is the source for our cubes, so when we let the SSAS decide the number of partitions to process in parallel, it is processing way too many partitions in parallel and SQL Server was not able to handle that much load and is failing by the deadlocks of the processing job.

That's when we decided to play with the Parallel MaxParallel setting in the XLMA of the Analysis Server.

  oServer.CaptureXml() = True

If varPartitionsToProcessInParallel <> -1 Then
    oServer.CaptureLog.Add("<Parallel MaxParallel=""" & varPartitionsToProcessInParallel.ToString() & """>")
End If

'Figure out the paritions to process

If varPartitionsToProcessInParallel <> -1 Then
    oServer.CaptureLog.Add("</Parallel>")
End If

oServer.CaptureXml() = False

oServer.ExecuteCaptureLog(True, True)

Executing the above code throws the following exception.

 An Error has occured while processing The Parallel element at line 9, column 46 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Batch/Parallel..

By looking at the XLMA generated I have found that two parallel elements are added to the generated script and the reason is I am adding the Parallel element manually and ExecuteCaptureLog is adding the other as we have set the "Parallel" argument to True.

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Parallel>
<Parallel MaxParallel="4">
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessData</Type>
  <Object>
    <DatabaseID>XXXXX</DatabaseID>
    <CubeID>VwXXXXX</CubeID>
    <MeasureGroupID>XXXXX</MeasureGroupID>
    <PartitionID>XXXXX</PartitionID>
  </Object>
</Process>
</Parallel>
</Parallel>
</Batch>

Changing the Parallel argument to False gave the right XLMA and all is cool after.

oServer.ExecuteCaptureLog(True, False)

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Parallel MaxParallel="4">
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessData</Type>
  <Object>
    <DatabaseID>XXXXX</DatabaseID>
    <CubeID>VwXXXXX</CubeID>
    <MeasureGroupID>XXXXX</MeasureGroupID>
    <PartitionID>XXXXX</PartitionID>
  </Object>
</Process>
</Parallel>
</Batch>

Here is the simplc code to find out what is being sent to the server to execute.

Dim FILE_NAME As String = "C:\temp\processingmanagerparallel.txt"

Dim objWriter As New System.IO.StreamWriter(FILE_NAME)

objWriter.Write(oServer.ConcatenateCaptureLog(True, False))
objWriter.Close()

Comments

Popular posts from this blog

Duplicate Folders Freeware - Not Duplicate Files

SSRS Compile Errors

Free SQL Server Management Studio Addin/Plugin/External Tool to get object information quickly