This script provides my PowerShell translation of C# code created by the authors of Microsoft SQL Server 2008 Analysis Services Unleashed.  I have tested this code on 64-bit Windows 7 running SQL Server 2008 R2 with Analysis Services, and the Windows PowerShell version 2 IDE (Integrated Development Environment).  This code requires the sample “Food Mart 2008” database available from the authors’ website at http://www.informit.com/store/product.aspx?isbn=0672330016

I shared this code as part of my data mining blog.  I have been reviewing this book, and I made comments on this code at http://www.marktab.net/datamining/2010/07/22/adomd-net-with-powershell-2-0-and-c-4-0/

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.

PowerShell
Edit|Remove
# 3312_AdomdExecute.ps1
# Mark Tabladillo
#
# Last Updated: July 22, 2010
# Program Created: July 22, 2010

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection] 


################################################################
function AdomdExecuteCommandText 
{
	write-host "AdomdExecuteCommandText"
		
	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
	$con.Open()

	# Create the object AdomdCommand
	$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
	$command.Connection = $con;

	# Define the text the request
	$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"

	# Execute command...
	# …
	# Close the connection
	$con.Close
}


################################################################
function AdomdExecuteCommandStream
{
	# This function assumes that you have a file called "Foodmart2008Metadata.xml" and that the "Food Mart 2008" Analysis Services database does NOT exist

	write-host "AdomdExecuteCommandStream"

	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost;"
	$con.Open()

	# Open a stream for reading a file that contains the XML for creating a cube
	$sr = new-object System.IO.StreamReader("..\\..\\Foodmart2008Metadata.xml", $true)
	$command = $con.CreateCommand()
	$command.CommandStream = $sr.BaseStream

	# Execute the command
	$command.ExecuteNonQuery()

	# Close the stream
	$sr.Close()
}


################################################################
function AdomdExecuteCommandCellSet
{
	write-host "AdomdExecuteCommandCellSet"

	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
	$con.Open()

	# Create a simple MDX request
	$cmdText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
	$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText,$con)

	# Execute a method, that returns CellSet
	$cellset = $command.ExecuteCellSet()
	$cellset

	# Close the connection
	$con.Close()
}


################################################################
function AdomdExecuteCommandDataReader
{
	write-host "AdomdExecuteCommandDataReader"

	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
	$con.Open()

	# Create a simple MDX statement
	$command = $con.CreateCommand()
	[string] $query = "SELECT Measures.Members ON columns FROM [Warehouse and Sales]"
	$command.CommandText = $query

	# Execute the method that returns IDataReader
	$reader = $command.ExecuteReader()

	# Read the metadata about the columns in the returned result
	$schemaTable = $reader.GetSchemaTable()
	write-host "Schema"
	for([int] $iSchRow = 0; $iSchRow -lt $schemaTable.Rows.Count; $iSchRow ++)
		{
		for([int] $iSchCol = 0; $iSchCol -lt $schemaTable.Columns.Count; $iSchCol ++)
			{
			   write-host $schemaTable.Rows[$iSchRow][$iSchCol].ToString()  " "
			}
	}
	# Read the result
	while($reader.Read())
	{
		for([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol ++)
		{
			write-host $reader.GetValue($iCol).ToString()
			write-host $reader.GetDataTypeName($iCol)
		}
	}
	# Close DataReader
	$reader.Close();
	# Close the connection
	$con.Close();
}
		
		
################################################################
function AdomdExecuteCommandXmlReader
{
	write-host "AdomdExecuteCommandXmlReader"

	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
	$con.Open()

	# Create a command
	$command = $con.CreateCommand()
	$command.CommandText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"

	# Execute the command and get XMLReader
	$reader = $command.ExecuteXmlReader()

	# Write the XML into a file -- use the results of GetCurrentDirectory to see where the file goes
	[IO.Directory]::GetCurrentDirectory()
	[string] $xml = $reader.ReadOuterXml()
	$writer = new-object System.IO.StreamWriter("cellSetXml.xml", $false)
	$writer.Write($xml)
	# Close the file
	$writer.Close()
	# Close XmlReader
	$reader.Close()
	# Close the connection
	$con.Close()
}


################################################################
function AdomdLoadCellSet
{
	write-host "AdomdLoadCellSet"

	# Open XmlReader from the XML file
	$reader = new-object System.XML.XmlTextReader("cellSetXml.xml")
	$reader
	
	# Load XML into CellSet object
	$cellSet = [Microsoft.AnalysisServices.AdomdClient.CellSet]::LoadXml($reader)
	$cellset

	# Close XmlReader 
	$reader.Close()
}


################################################################
function AdomdExecuteCommandNonQuery
{
	write-host "AdomdExecuteCommandNonQuery"

	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
	$con.Open()

	# Create the command that will send a request for a creation of a calculated member
	[String] $cmdText = "CREATE member [Warehouse and Sales].Measures.MyCalcMember as '1' "
	$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText, $con)

	# Execute the command, without expecting a returned result
	$command.ExecuteNonQuery()

	# Close the connection
	$con.Close()
}


################################################################
function AdomdExecuteCommand
{
	write-host "AdomdExecuteCommand"

	# Open a connection to the server
	$con = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
	$con.ConnectionString = "Datasource=localhost; Initial Catalog=FoodMart 2008;"
	$con.Open()

	# Create a command that sends a simple MDX request
	[String] $cmdText = "SELECT Measures.Members ON COLUMNS FROM [Warehouse and Sales]"
	$command = new-object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($cmdText, $con)

	# Execute a command and return an object of any type.
	[Object] $result = $command.Execute()

	# Analyze the type of object returned 
	if ($null -eq $result)
	{
		# No result
		write-host "No result"
	}
	elseif ($result -is [Microsoft.AnalysisServices.AdomdClient.CellSet])
	{
		# Work with CellSet
		write-host "Work with CellSet"
		[Microsoft.AnalysisServices.AdomdClient.CellSet] $cellset = $result
	}
	elseif ($result -is [Microsoft.AnalysisServices.AdomdClient.AdomdDataReader])
	{
		# Work with DataReader
		write-host "Work with DataReader"
		[Microsoft.AnalysisServices.AdomdClient.AdomdDataReader] $dataReader = $result
	}
	# Close the connection
	$con.Close()
}


################################################################
# Main
AdomdExecuteCommandText
#AdomdExecuteCommandStream
AdomdExecuteCommandCellSet
AdomdExecuteCommandDataReader
AdomdExecuteCommandXmlReader
AdomdLoadCellSet
AdomdExecuteCommandNonQuery
AdomdExecuteCommand