About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture


Report Portal

SSIS package to process all dimensions in SSAS 2005 DB

March 29th, 2007 by Vidas Matelis

Vidas: My next blog entry contains updated code !

It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.

  • In connection manager add a new Analysis Services connection, name connection “DM”. If a different name is used, adjust the script below
  • For a package add Boolean type variable oNeedsFullReprocessing. A true value will mean that the dimensions will need ProcessFull option, false will mean that ProcessUpdate is required.
  • For a package create variable pDimProcessCmd with type String. This will contain an XML command
  • Add to package Script task. For this task in script window set properties:
      ReadOnlyVariables pNeedsFullReprocessing
      ReadWriteVariables pDimProcessCmd
  • Click on “Design Script”
  • In Project Explorer, right mouse click on “References”, then “Add Reference” and choose “Analysis Management Objects”. This will add reference “Microsoft.AnalysisServices”
  • Add code from below to this script 

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    Public Sub Main()
        Dim oNeedsFullReprocessing As Variable = Dts.Variables(“pNeedsFullReprocessing”)
        Dim sNeedsFullReprocessing As String = CStr(oNeedsFullReprocessing.Value)
        Dim sProcessType As String = “ProcessUpdate”
        If sNeedsFullReprocessing = “True” Then
            sProcessType = “ProcessFull”
            sProcessType = “ProcessUpdate”
        End If
        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)
        Dim sXMLA As String = “”

        If oDB Is Nothing Then
            MsgBox(“Did not find expected database: ” & sDatabaseID, MsgBoxStyle.OkOnly, “Error looking for partition”)
            GoTo Done
            sXMLA = “”
            sXMLA = sXMLA + “<Batch xmlns=^http://schemas.microsoft.com/analysisservices/2003/engine^>” & vbCrLf
            sXMLA = sXMLA + ”  <Parallel>” & vbCrLf
            Dim sDimID As String
            Dim oDim As Microsoft.AnalysisServices.Dimension
            For Each oDim In oDB.Dimensions
                sDimID = oDim.ID
                sXMLA = sXMLA + ”    <Process xmlns:xsd=^http://www.w3.org/2001/XMLSchema^ xmlns:xsi=^http://www.w3.org/2001/XMLSchema-instance^>” & vbCrLf
                sXMLA = sXMLA + ”      <Object>” & vbCrLf
                sXMLA = sXMLA + ”        <DatabaseID>” & sDatabaseID & “</DatabaseID>” & vbCrLf
                sXMLA = sXMLA + ”        <DimensionID>” & sDimID & “</DimensionID>” & vbCrLf
                sXMLA = sXMLA + ”      </Object>” & vbCrLf
                sXMLA = sXMLA + ”      <Type>” & sProcessType & “</Type>” & vbCrLf
                sXMLA = sXMLA + ”      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>” & vbCrLf
                sXMLA = sXMLA + ”    </Process>” & vbCrLf
        End If

        sXMLA = sXMLA + ”  </Parallel>” & vbCrLf
        sXMLA = sXMLA + “</Batch>” & vbCrLf
        sXMLA = Replace(sXMLA, “^”, “”””)
        Dts.Variables(“pDimProcessCmd”).Value = sXMLA
        oServer.Disconnect() ‘ disconnect from the server — we are done
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

  • Close script editor
  • Add taks “Analysis Services Execute DDL” and connect it to previously created script task.
  • In DDL screen change properties:
    Connection: DM
    SourceType: Variable
    Source: User::pDimProcessCmd
  • Save package.
  • Done

Vidas Matelis  


March 29, 2007. Darren Gosbell, MVP comments

Since you are making an AMO connection to the server anyway, you might want to make use of the CaptureXML and CaptureLog properties in your package as this would save you from having to do manual string concatenations to build the XMLA command. In fact you would not actually even need to pull out the xmla, you could use the ExecuteCaptureLog method.

So in pseudo code it would go something like the following:

Code SnippetServer.CaptureXml = True
for each dim in Database.Dimensions

 The actually dim.Process calls get captured and the whole batch would get sent through when the ExecuteCaptureLog is called.

Just a suggestion


Vidas: My next blog entry contains updated code !

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

Posted in SSAS, SSIS | 1 Comment »

One Response

  1. Eric Lord Says:

    The only problem that I’ve found with this approach is that there seems to be a 4000 char limit on “Strings” in SSIS. So, if your XMLA reaches that limit, it will be truncated within SSIS and break the step that executes it.