Vidas Matelis Analysis Services Blog

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”
        Else
            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
        Else
            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
            Next
        End If

        sXMLA = sXMLA + ”  </Parallel>” & vbCrLf
        sXMLA = sXMLA + “</Batch>” & vbCrLf
        sXMLA = Replace(sXMLA, “^”, “”””)
        Dts.Variables(“pDimProcessCmd”).Value = sXMLA
Done:
        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
   dim.Process()
next
Server.ExecuteCaptureLog(True,False)

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