Click here to Skip to main content
11,570,352 members (42,826 online)
Click here to Skip to main content

"OSQL Utility" for SSAS 2005

, 18 Jun 2008 CPOL 15.8K 58 14
Rate this:
Please Sign up or sign in to vote.
A script that lets you run many XMLA files against a SSAS 2005 database.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Igor Krupitsky
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

You may also be interested in...

Comments and Discussions

 
QuestionWhat about ascmd Pin
Member 46495631-Mar-09 14:30
memberMember 46495631-Mar-09 14:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150624.2 | Last Updated 18 Jun 2008
Article Copyright 2008 by Igor Krupitsky
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid