Processing an AS2005 cube from the command line with XMLA

There was a question recently on the Analysis Services newsgroup asking how to process an Analysis Services 2005 cube using XMLA from the command line. Below is a script based on Chris Harrington's  excellent Thin Olap blog. This is the absolute minimum script required to get an Analysis Services database to process (ie. No error handling <g>).

On my machine I processed my “Sandpit“ database (the sandpit is where I play) with the following command

    cscript xmlaProcess.vbs Sandpit http://locahost/sql05/msmdpump.dll

In order to run this script from your environment all you need to do is:

  1. copy and paste the following script to a file called xmlaProcess.vbs
  2. If it is not already configured you will need to setup HTTP access on your server
    (see here for information on how to configure AS 2005 for HTTP access: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx)
  3. and then run it from the command line by executing
    cscript xmlaProcess.vbs <database> <server> (as a user with Admin rights)

Have Fun!

// =================
// START OF SCRIPT //
// =================
'----------------------------------------------------------------------------------------------
' Name       : xmlaProcess.vbs
' Author     : Darren Gosbell
' Date       : 11 Mar 2006
' Description: Processes the specified Analysis Services Database.
' Notes      : From a Command prompt type the following
'
'              cscript xmlaProcess.vbs <Database> <http Address>
'
'           eg. cscript xmlaProcess.vbs Sandpit http://localhost/xmla/msmdpump.dll
 
'
' Revision History:
' Date          Who Ref#    Description
' 24/02/2006    DPG n/a     Initial version
'----------------------------------------------------------------------------------------------

'// Create XMLHTTP instance
Dim oHttp
Dim lResolve
Dim lConnect
Dim lSend   
Dim lReceive
Dim sUrl, sXmlaProcessCmd, sXmlaPre, sXmlaPost
Dim sDatabase, sXmla

If wscript.arguments.count = 2 then
  sDatabase = wscript.arguments(0)
  sUrl = wscript.arguments(1)
Else
  wscript.echo "You must specify the <database> <http Address> parameters in order to run this script"
  wscript.quit(1)
End If

Set oHttp = CreateObject("MSXML2.ServerXMLHTTP.4.0")

lResolve = 60 * 1000
lConnect = 60 * 1000
lSend    = 60 * 1000
lReceive = 60 * 1000
oHttp.setTimeouts lResolve, lConnect, lSend, lReceive

oHttp.open "POST", sUrl, false

oHttp.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
oHttp.setRequestHeader "SOAPAction", """urn:schemas-microsoft-com:xml-analysis:Execute"""

sXmlaPre = "<?xml version='1.0'?>" _
            &
"<Envelope xmlns=""http://schemas.xmlsoap.org/soap/envelope/""><Body>" _
            & "<Execute xmlns=""urn:schemas-microsoft-com:xml-analysis""><Command>"
sXmlaPost = "</Command><Properties><PropertyList></PropertyList>" _
            &
"</Properties></Execute></Body></Envelope>"

'// This is the process command that was copied from SQL Server Management Studio
sXmlaProcessCmd =
"<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" _
  & " <Parallel>" _
  &
"   <Process xmlns:xsd=""http://www.w3.org/2001/XMLSchema""" _
  &
"        xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">" _
  & "    <Object>" _
  & "      <DatabaseID>" & sDatabase & "</DatabaseID>" _
  & "    </Object>" _
  & "    <Type>ProcessFull</Type>" _
  & "    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" _
  & "    </Process>" _
  & " </Parallel>" _
  &
"</Batch>"

sXmla = sXmlaPre & sXmlaProcessCmd & sXmlaPost  
oHttp.send sXmla

Print | posted on Tuesday, April 11, 2006 9:07 PM

Comments on this post

# re: Processing an AS2005 cube from the command line with XMLA

Requesting Gravatar...
What would be the way to execute the same task using XMLA/.Net without using a webservice? An example plz.
Left by ROdrigo on Dec 13, 2007 7:04 AM

# re: Processing an AS2005 cube from the command line with XMLA

Requesting Gravatar...
How did you able to pull the process script from
'// This is the process command that was copied from SQL Server Management Studio
?

Thanks!
Left by Tony Lam on Jul 25, 2008 8:35 AM

# re: Processing an AS2005 cube from the command line with XMLA

Requesting Gravatar...
It was easier to explain this with some pictures, so I did a post here: http://geekswithblogs.net/darrengosbell/archive/2008/07/27/ssas-scripting-out-a-process-command.aspx
Left by Darren Gosbell on Jul 27, 2008 10:58 PM

Your comment:

 (will show your gravatar)