OSQL Utility for SSAS 2005

• Download source code - 1.61 KB

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
 'WScript.Echo 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("Microsoft.XMLHTTP")
 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:

1. Setup HTTP access to SQL Server 2005 Analysis Services: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx.
2. 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.
3. 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.
4. Drop your XMLA files on top of the script file (xmla.vbs)
5. 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.