Posted by: Karlo Bartels | 2011/01/23

VBA for Analysis Services

Execute XMLA from VBA

Below you will find a code snippet that issues an XMLA command to an Analysis Server from an Excel workbook by using VBA. This specific example processes a dimension, but can easily be modified to process other Analysis Server objects as well.

The code connects to the Analysis Server by using an ADODB Connection object and the MSOLAP provider. The XMLA command is executed within an ADODB Command.  The code snippet accepts the Analysis Server name, the database ID (not the database name), the ID of the dimension you wish to process and the process type:

Sub ProcessDimension(ServerName As String, DatabaseID As String,
                     DimensionID As String, ProcessType As String)
Dim con As New ADODB.Connection
Dim xmlaCommand As New ADODB.Command
Dim xmlaCommandTemplate As String
On Error GoTo Err_Process
 
 'Create XMLA template (or load it from disk)
  xmlaCommandTemplate = _
  "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
    "<Parallel>" & _
      "<Process>" & _
        "<Object>" & _
          "<DatabaseID>" & DatabaseID & "</DatabaseID>" & _
          "<DimensionID>" & DimensionID & "</DimensionID>" & _
        "</Object>" & _
        "<Type>" & ProcessType & "</Type>" & _
        "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & _
      "</Process>" & _
    "</Parallel>" & _
  "</Batch>"
 
  Application.StatusBar = "Please wait, processing dimension '" & DimensionID & "'..."
 
  'Open a connection to the Analysis Server and execute XMLA command
  With con
    .Open "Provider=MSOLAP.3;Data Source=" & ServerName & ";Integrated Security=SSPI;"
    With xmlaCommand
      .ActiveConnection = con
      .CommandTimeout = 120 'seconds
      .CommandText = xmlaCommandTemplate
      .Execute
    End With
    .Close
  End With
Err_Process_Exit:
  Application.StatusBar = ""
  Set con = Nothing
  Set xmlaCommand = Nothing
  Exit Sub
Err_Process:
  MsgBox "An error occured while processing dimension '" & DimensionID & _         "':" & vbCrLf & vbCrLf & Err.Description, vbExclamation
  GoTo Err_Process_Exit
End Sub
 

You can start the code as follows:

Sub Go()
  ProcessDimension "<Your server name", "<Your DB ID>", "<Your dimension ID>",
  "ProcessAdd"
End Sub
 

Do not forget to set a reference to the Microsoft ActiveX Data Object 2.8 library before trying the sample code.

Download the code here

XMLA Template Library

Instead of hard-coding the XMLA template, you could also create an XMLA template library on disc that contains templates with various process options and process types. The code would have to be modified to load a template file from disc into the xmlaCommandTemplate variable. This guarantees a more generic use of the code.

To easily generate an XMLA template, connect to your Analysis Server in SQL Server Management Studio, browse to the object for which you want to generate an XMLA template, right-click it and choose Process. In the dialog that appears select Script action to file to generate the template:

Create XMLA template

Advertisements

Responses

  1. Awesome
    Do you know how to find the all the partition on a measure group by VBA?

  2. You could use the AMO OLAP classes for this (http://msdn.microsoft.com/en-us/library/ms345083.aspx). Add a reference to the AMO library in VBA and create a For Each construction to loop through the measure groups of a particular cube. The Partitions property of a MeasureGroup class contains information on the measure group partition(s).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: