After I published blog entry about a  SSIS package that processes dimensions, I received a suggestion from Darren Gosbell (his blog is here) that instead of building XMLA code by concatenating strings, I  should use the  CaptureXML option from an  SSAS server object and then process dimensions using dim.Process method and execute XMLA using ExecuteCaptureLog routine.

I remembered that I tried to use dim.Process method when I originally wrote this package about a year ago, and I had performance related problems. So I re-tested this package by removing code that builds XMLA and instead just executing dim.Process method. This worked, but it took me 6 minutes to process 57 dimensions in my test database. A  package that builds XMLA code and submits it to the  SSAS server was able to do the  same thing in 25 seconds. That is a  big difference. I guessed that results are related to the fact that in my manually built XMLA code I added the  <Parallel> tag to batch and that makes all dimensions to be processed, processed  in parallel.

At first I thought that the  option using CaptureXML property would produce the  exact same results as the dim.Process method. But thanks to Darren Gosbell’s additional explanations, I setup my tests to see if CaptureXML makes any difference. I was quite surprised by the results. As I did not use CaptureXML method before, I was not sure what it does. I found that basically after you set CaptureXML property to True, statements to SSAS are not executed, they are just logged internally. You can later execute that log using ExecuteCaptureLog call. Most importantly ExecuteCaptureLog accepts 3 parameters that are very important:
Transactional (True/False)
Parallel (True/False)
pocessAffected (True/False)
In my tests I did not use third parameter, as it was irrelevant.

My results with different parameters are bellow. I added comments on what is different in XMLA code submitted (captured with profiler).

  • Transactional = True, Parallel = True, Execution Time : 22 sec, Note: Executes Batch with parameter: <Batch xmlns=… <Parallel>
  • Transactional = True, Parallel = False, Execution Time: 28 sec, Note: Executes Batch with parameter: <Batch xmlns=…
  • Transactional = False, Parallel = False, Execution Time: 6 min, Note: Executes Batch with parameter: <Batch Transaction=”false” xmlns=…
  • Transactional = False, Parallel = True, Error: Errors in the OLAP storage engine: Element Parallel is not supported in non-transactional batch.

As you can see the biggest impact to performance was the fact that multiple dimensions are processed in a single batch. Submitting each transaction in separate batch increased processing time 20 times.

So after learning all this I was able to simplify my package significantly. I will even look at  rewriting my packages that does cube/measure group and partition re-processing.

Thanks to comments and example from Jesse Orosz (his blog), I  added very simple error handling - I will  fail package if ExecuteCaptureLog will produce error. For simplicity of this package, I  did not introduce proper error logging.

Below are new steps to create an  SSIS package that processes all dimensions (process full option) in a single database:

  • Create a  new package.
  • In connection manager add a new Analysis Services connection, name  the connection DM. If different name is used, adjust script below. Set this connection to point to your required server and SSAS database.
  • To control flow area add new ”Script” tasks. Rename it to ”Process All Dimensions”
  • Double click on this new task and go to Script screen and press on button “Design Script”.
    In Project explorer select “References” and after right mouse click select “Add reference”. Choose Analysis Management Objects.

Image

  • Replace script with this:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
      Public Sub Main()
              Dim ExecutionSuccessfull As Boolean = True ‘ If true, package executed without errors
              Dim sProcessType As String = “ProcessFull”

              Dim oConnection As ConnectionManager
              oConnection = Dts.Connections(”DM”)
              Dim sServer As String = CStr(oConnection.Properties(”ServerName”).GetValue(oConnection))
              Dim sDatabaseID As String = CStr(oConnection.Properties(”InitialCatalog”).GetValue(oConnection))
              Dim oServer As New Microsoft.AnalysisServices.Server
              oServer.Connect(sServer) ‘ connect to the server and start scanning down the object hierarchy
              Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabaseID)

              If oDB Is Nothing Then
                      ExecutionSuccessfull = False
                      GoTo Done
              Else
                      Dim oDim As Microsoft.AnalysisServices.Dimension

                      oServer.CaptureXml() = True ‘ Start capturing XML.
                      For Each oDim In oDB.Dimensions
                              ‘ This will generate XMLA, but because CaptureXML is True, will not execute it!
                              oDim.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)
                      Next
                      oServer.CaptureXml() = False ‘ Stop capturing XML
                      ‘ Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
                      ‘ These are very important parameters!

                      Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
                      Dim oResult As Microsoft.AnalysisServices.XmlaResult

                      oResults = oServer.ExecuteCaptureLog(True, True)

                      Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

                      ‘Log the errors and warnings
                      For Each oResult In oResults
                              For Each oMessage In oResult.Messages
                                      If oMessage.GetType.Name = “XmlaError” Then
                                              ‘The processing failed
                                              ExecutionSuccessfull = False
                                      Else
                                              ‘It’s just a warning.
                                              ExecutionSuccessfull = True ‘ if you want to fail on warning, change this to False
                                      End If
                              Next oMessage
                      Next oResult

              End If
Done:
              oServer.Disconnect() ‘ disconnect from the server — we are done

              If ExecutionSuccessfull Then
                      Dts.TaskResult = Dts.Results.Success
              Else
                      Dts.TaskResult = Dts.Results.Failure
              End If
      End Sub

End Class

  • Close script editor
  • Save Package with your preferred name
  • Package will look similar to this:

Image

  • You are ready to execute it
     

As you can see this script is much simpler than my original version.

For proper production version I will have to add proper error handling - save errors/warnings to dts log.

You can find forum where we talked about this package here.

Note: If in script editor you see blue underline for Microsoft.AnalysisServices definitions, then you have to copy Microsoft.AnalysisServices.dll to the .NET folder.
Default location from: C:\Program Files\Microsoft SQL Server\90\sdk\Assemblies\Microsoft.AnalysisServices.DLL
Default location to: C:\WINDOWS\Microsoft.NET\Framework\v2.0.?\

Vidas Matelis

June 5, 2007 Note: Added to script library http://www.ssas-info.com/analysis-services-scripts