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

Comments on this post

# re: SSAS: Sending XMLA commands using AdomdClient

Requesting Gravatar...
Hello
I face the same issue, running XML query against a server to Warming up the cache memory. I perform the operation in a dynamic multiple blocks, with external loop, "foreach Loop Container" in ssis which nourishes me the names of the CUBES.
My question is, how can I create a situation (using VB.NET) allows stopping the process until the command has ended and only then move to the next one and again send XML commands to the server and proceed only after the command has finished running ETC ...
Currently the shooting process All orders rapidly and affects.
It is important to say that when I run the query with XMLA query in MANEGMENT STUDIO Warm up the cache memory is successful .
Thanks in advance,
Doron
Left by Doron on Jul 07, 2014 10:06 PM

# re: SSAS: Sending XMLA commands using AdomdClient

Requesting Gravatar...
@Doron - There is a downloadable SSIS cache warmer here http://www.bidn.com/blogs/DevinKnight/ssis/797/ssas-cache-warming-with-an-ssis-package which is an implementation of the technique described by Chris Webb. I'm pretty sure this one executes the commands in serial. Otherwise it depends on your VB.NET code - the only thing I can guess is that maybe you are using an asynchronous method like BeginExecute and you should switch to Execute. But it's hard to say without being able to see the code.
Left by Darren Gosbell on Jul 08, 2014 7:01 AM

# re: SSAS: Sending XMLA commands using AdomdClient

Requesting Gravatar...
ֲ@Darren-
How can i add Photos of my Solution?

Left by Doron on Jul 08, 2014 10:32 PM

# re: SSAS: Sending XMLA commands using AdomdClient

Requesting Gravatar...
@doron - you can't add images in the comments. I'll reply to your in email and we can discuss this offline.
Left by Darren Gosbell on Jul 09, 2014 7:12 AM

# re: SSAS: Sending XMLA commands using AdomdClient

Requesting Gravatar...
Hi, I am doing a project and I saw your comment on msdn page.
Run a code similar to yours, but I get the following error:

<<
Server: The operation has been cancelled. Internal error: The operation terminated unsuccessfully.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while processing the 'Pam Agencia' partition of the 'Pam Agencia' measure group for the 'AGENCIA' cube from the Proyecto_CUBO database.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
>>

I would appreciate your help.
I also left a msdn question, here is the link:

https://social.msdn.microsoft.com/Forums/es-ES/eb8a4af9-6a96-4dfd-9282-63a02b88290b/ejecutar-solamente-un-cubo-olap-en-vb?forum=vbes
Left by Hector on Jan 16, 2015 10:30 AM

Your comment:

 (will show your gravatar)