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>"
sXmla = sXmlaPre & sXmlaProcessCmd & sXmlaPost
oHttp.send sXmla