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 (which you may need to copy in as “FoodMart 2005”) 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/26/analysis-management-objects-amo-and-powershell-2-0/

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

PowerShell
Edit|Remove
# 3407_AMOSessionShare.ps1
# Mark Tabladillo
#
# Last Updated: July 23, 2010
# Program Created: July 23, 2010

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


################################################################
# create a very simple cube
function CreateDataItem
	{
		param(
			[Microsoft.AnalysisServices.DataSourceView] $dsv, 
			[string] $tableName, 
			[string] $columnName
		)
		write-host "CreateDataItem"
		$dataTable = $dsv.Schema.Tables[$tableName]
		$dataColumn = $dataTable.Columns[$columnName]
		$dataItem =  new-object Microsoft.AnalysisServices.DataItem($tableName, $columnName, [Microsoft.AnalysisServices.OleDbTypeConverter]::GetRestrictedOleDbType($dataColumn.DataType))
		$dataItem
}


################################################################
function CreateCube 
	{
		param(
			[Microsoft.AnalysisServices.Database] $database
		)
		
		write-host "CreateCube"
		# Create a new cube.
		[Microsoft.AnalysisServices.Cube] $cube = $database.Cubes.Add($database.Cubes.GetNewID(), $database.Cubes.GetNewName())
		# Add the reference to the database dimension to the cube.
		$dim = $database.Dimensions.GetByName("Customer")
		$cube.Dimensions.Add($dim.ID)
		# Create a measure group.
		$mg = $cube.MeasureGroups.Add("MeasureGroup1")
		$mg.StorageMode = [Microsoft.AnalysisServices.StorageMode]"Molap"
		$mg.ProcessingMode = [Microsoft.AnalysisServices.ProcessingMode]"Regular"

		# Add a measure group dimension.
		$regMgDim = new-object Microsoft.AnalysisServices.RegularMeasureGroupDimension($dim.ID)
		$mg.Dimensions.Add($regMgDim)
		$mgAttr = $regMgDim.Attributes.Add($dim.Attributes["Customer"].ID)
		$mgAttr.Type = [Microsoft.AnalysisServices.MeasureGroupAttributeType]"Granularity"
		$mgAttrDataItem = CreateDataItem $database.DataSourceViews[0] "dbo_sales_fact_1997" "customer_id"
		$mgAttr.KeyColumns.Add($mgAttrDataItem)

		# Create one measure.
		$meas = $mg.Measures.Add("Unit Sales")
		$meas.AggregateFunction = [Microsoft.AnalysisServices.AggregationFunction]"Count"
		$meas.FormatString = "#"
		$meas.Visible = $true
		$meas.Source = CreateDataItem $database.DataSourceViews[0] "dbo_sales_fact_1997" "Unit_Sales"
		$cube
	}


################################################################
& {
	try
	{
		# Create an ADOMD.NET Connection object. 
		$adomdConnection = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
		$adomdConnection.ConnectionString = "Datasource=localhost; Initial Catalog=Foodmart 2008;"
		$adomdConnection.Open()

		# Create an AMO Server object.
		$server = new-object Microsoft.AnalysisServices.Server
		# Connect to the server using the same session that the ADOMD.NET connection uses.
		$server.Connect("Datasource=localhost", $adomdConnection.SessionID)

		$database = $server.Databases.FindByName("Foodmart 2008")
		# CreateCube returns a collection of objects
		$CreateCubeObjects = CreateCube $database
		# Create a new cube, use explicit typing, subtract one because array numbering starts with zero
		[Microsoft.AnalysisServices.Cube] $cube = $CreateCubeObjects[$CreateCubeObjects.count - 1]

		# Start the transaction.
		$server.BeginTransaction()
		# Update the cube. 
		$cube.Update([Microsoft.AnalysisServices.UpdateOptions]"ExpandFull")
		# Process the cube.
		$cube.Process([Microsoft.AnalysisServices.ProcessType]"ProcessDefault")
		# Check that new cube is available through the ADOMD.NET connection.
		# It would not be available if the session weren’t shared
		if ($null -ne $adomdConnection.Cubes.Find($cube.Name))
		{
			write-host "The new cube created by AMO is available through ADOMD."
			# Roll back the transaction.
			$server.RollbackTransaction()
			# Close the AMO connection;this call does not end the session.
			$server.Disconnect($false)
			# Close the ADOMD.NET connection; this call does close the session.
			$adomdConnection.Close()
		}
	}
	catch [Microsoft.AnalysisServices.AmoException]
	{
		write-host "[Microsoft.AnalysisServices.AmoException]"
		write-host $_.Exception.Message
	}
	catch [Microsoft.AnalysisServices.AdomdClient.AdomdException]
	{
		write-host "[Microsoft.AnalysisServices.AdomdClient.AdomdException]"
		write-host $_.Exception.Message
	}
	finally
	{
		if ($server.Connected)
		{
			$server.Diconnect()
		}
		if ($adomdConnection.State -ne [System.Data.ConnectionState]"Closed")
		{
			$adomdConnection.Close()
		}
	}
}