Processing an AS2005 cube from the command line with XMLA

<< OT: A new name for WinFx ? | Home | Meet Monad - your new best friend >>

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:

copy and paste the following script to a file called xmlaProcess.vbs
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)
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>"