Introduction
One cool thing about Microsoft Analysis Services 2005 is that it lets you script many administrative tasks (such as role creation and cube processing). The script files are saved with an XMLA extension and are written in Analysis Services Scripting Language (ASSL) format.
The problem arises when you need to run a lot (100+) of these files against a SSAS database. Unfortunately, I could not find a utility to run these files programmatically. This article describes how to create a VBS script file to run many XMLA files against SSAS.
Background
The trick is to use HTTP access to send the Execute XMLA request to SSAS. The execute request, along with letting you send MDX commands, lets you send ASSL commands.
Using the Code
Here is the code for the VBS script file:
Const ForAppending = 8
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim sFolder: sFolder = fso.GetParentFolderName(WScript.ScriptFullName)
Dim oLogFile: Set oLogFile = _
fso.CreateTextFile(WScript.ScriptFullName & ".log", True)
Dim sUrl
Dim sUserName
Dim sPassword
LoadConfigFile
If WScript.Arguments.Count = 0 Then
ProcessFolder sFolder & "\XMLA"
Else
For i = 0 to WScript.Arguments.Count -1
ProcessFile WScript.Arguments(i)
Next
End If
Log "Done!"
oLogFile.Close
Sub ProcessFolder(sMyFolder)
If Not fso.FolderExists(sMyFolder) Then
Log "Folder does not exist: " & sMyFolder
Exit Sub
End If
Dim oFolder, oFile
Set oFolder = fso.GetFolder(sMyFolder)
For Each oFile In oFolder.Files
If LCase(Right(oFile.Name, 5)) = ".xmla" Then
ProcessFile oFile.Path
End If
Next
End Sub
Sub LoadConfigFile()
Dim sConfigFile: sConfigFile = sFolder & "\XmlaConfig.xml"
If Not fso.FileExists(sConfigFile) Then
Log "Configuration file does not exist: " & sConfigFile
WScript.Quit
End If
Dim oDoc: Set oDoc = CreateObject("MSXML2.DOMDocument")
If Not oDoc.Load(sConfigFile) Then
Log "Configuration file could not be loaded: " & _
sConfigFile & " " & oDoc.parseError.reason
WScript.Quit
End If
sUrl = oDoc.SelectSingleNode("settings/url").Text
sUserName = oDoc.SelectSingleNode("settings/user").Text
sPassword = oDoc.SelectSingleNode("settings/password").Text
End Sub
Sub ProcessFile(sFilePath)
If Not fso.FileExists(sFilePath) Then
Log ("File does not exist: " & sFilePath)
Exit Sub
End If
Log "Executing: " & sFilePath
Dim sConents: sConents = GetFileContents(sFilePath)
Dim strQuery: strQuery = EncloseTag("Command", sConents)
Dim strProps: strProps = EncloseTag("Properties", _
"<PropertyList><Timeout>0</Timeout></PropertyList>")
Dim sNS: sNS = " xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/""
xmlns:xsi = ""http://www.w3.org/2001/XMLSchema-instance""
xmlns:xsd=""http://www.w3.org/2001/XMLSchema"""
Dim sPayload: sPayload = "<?xml version='1.0'?>" & _
"<SOAP-ENV:Envelope " & sNS & ">" & _
"<SOAP-ENV:Body>" & _
" <Execute xmlns=""urn:schemas-microsoft-com:xml-analysis"" >" & _
strQuery + strProps & _
" </Execute>" & _
"</SOAP-ENV:Body>" & _
"</SOAP-ENV:Envelope>"
Log PostData(sUrl, sPayload, sUserName, sPassword)
Log ""
End Sub
Private Sub Log(sLine)
oLogFile.WriteLine sLine
End Sub
Private Function EncloseTag(sTag, sValue)
EncloseTag = "<" & sTag & ">" & sValue & "</" & sTag & ">"
End Function
Private Function PostData(sUrl, sData, sUserName, sPassword)
Dim oHttp
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
oHttp.setTimeouts 0, 0, 0, 0
oHttp.Open "POST", sUrl, False, sUserName, sPassword
oHttp.setRequestHeader "SOAPAction", _
"""urn:schemas-microsoft-com:xml-analysis:Execute"""
oHttp.Send sData
PostData = oHttp.responseText
Set oHttp = Nothing
End Function
Public Function GetFileContents(sFilePath)
Dim sContents
Const ForReading = 1
Const TristateMixed = -2
Set oTextFile = fso.OpenTextFile(sFilePath, ForReading, False, TristateMixed)
Do While Not oTextFile.AtEndOfStream
sContents = sContents & oTextFile.ReadLine
Loop
oTextFile.Close
GetFileContents = sContents
End Function
Deployment
The most difficult part is the configuration. Here are the steps:
- Setup HTTP access to SQL Server 2005 Analysis Services: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx.
- Make sure that XMLA virtual directory is set to basic authentication only. Note that you can also use Anonymous access (with a user that has admin access to your SSAS server), but this option is less secure.
- Change the configuration file (xmlaConfig.xml) to point to your XMLA provider (like http://MyServer/xmla/msmdpump.dll). Set the Windows user name (domain\username) and password that has admin access to your SSAS server.
- Drop your XMLA files on top of the script file (xmla.vbs)
- Optionally, you can create a subfolder called XMLA in the same folder where the script file resides. The script file will look for the XMLA subfolder and run all XMLA files within it.
Points of Interest
Here is a code I wrote earlier to run multiple SQL files against a SQL Server database using the OSQL utility: http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=1087&lngWId=5.