SSAS: Sending XMLA commands using AdomdClient

There was a question on the forums a little while ago about what were the minimal libraries that would need to be installed in order to write a program that would allow someone to process a cube. Well it can be done with just the ADOMD.NET client libraries, which the user will need on their machine anyway if they are going to browse the cubes.

Below is about the shortest VB.NET "application" that you would need to process a cube. It's a console application and all you need to do is to add a reference to the Microsoft.AnalysisServices.AdomdClient assembly and you are good to go.

Obviously this is just sample code and all the variables at the top are hard coded, but you get the idea.

The other hassle is that the XML/A command is restricted by the IDs of  the Database and Cube. The Object IDs are used whenever you want to restrict an XML/A command to work on a specific object. You can find out what the ID is by right clicking on these objects in SSMS and viewing the properties, it starts out with the same value as the Name property, but the name can be changed while the ID cannot. Programmatically there are only a few ways of getting an object's ID which I find a bit frustrating - I will run through these in a future post.

  Sub Main()
        Dim serverName As String = "localhost"
        Dim databaseName As String = "Adventure Works DW"
        Dim databaseID As String = databaseName
        Dim cubeID As String = "Adventure Works"
        Dim cn As String = "Provider=MSOLAP;Data Source=" & serverName & _
                           ";Initial Catalog=" & databaseName)
        Dim cn As New AdomdConnection(cnString)
        Console.WriteLine("Opening Connection...")
        cn.Open()
        Dim cmd As AdomdCommand
        cmd = cn.CreateCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "<Batch " & _
            "xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
            "<Parallel><Process> <Object>" & _
            "<DatabaseID>" & DatabaseID & "</DatabaseID>" & _
            "<CubeID>" & cubeID & "</CubeID> " & _
            "</Object><Type>ProcessFull</Type>" & _
            "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & _
            "</Process> </Parallel>" & _
            "</Batch>"
        cmd.ExecuteNonQuery()
     End Sub


Print | posted on Tuesday, June 5, 2007 7:06 AM