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

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

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

# 3326_AdomdDataReader.ps1
# Mark Tabladillo
# Last Updated: July 22, 2010
# Program Created: July 22, 2010

# [Microsoft.AnalysisServices.AdomdClient.AdomdConnection] 

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

# Create and execute an MDX query
$command = $con.CreateCommand()
$command.CommandText = "SELECT {[Measures].[Store Cost], [Measures].[Store Sales]} ON COLUMNS FROM [Warehouse and Sales]"
$reader = $command.ExecuteReader()

# Read the metadata about the columns
$schemaTable = $reader.GetSchemaTable()
write-host "Schema Information about resultset"
foreach ($schRow in $schemaTable.Rows)
	foreach ($schColumn in $schemaTable.Columns)
		write-host $schColumn.ColumnName ":"
		write-host $schRow[$schColumn].ToString()

# Another way to access that metadata is to call the method AdomdDataReader.GetName(), but if the result contains no rows, this approach won’t work.
write-host "Name of the columns"
for ([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol++)
	write-host $reader.GetName($iCol)

# Iterate the values
write-host "Values in the resultset"
while ($reader.Read())
	for ([int] $iCol = 0; $iCol -lt $reader.FieldCount; $iCol++)
		write-host "Value: " $reader.GetValue($iCol)
		write-host "Type: " $reader.GetDataTypeName($iCol)
# Close the DataReader

# Close the connection