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
# 3411_AMOScripter.ps1
# Mark Tabladillo
#
# Last Updated: July 23, 2010
# Program Created: July 23, 2010

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


################################################################
function ScriptDatabase
{
	# Create a server object.
	$srv = new-object Microsoft.AnalysisServices.Server
	& {
		[IO.Directory]::GetCurrentDirectory()
		[string] $fileName = "scriptDB.xml"

		# Connect to the server.
		$srv.Connect("localhost")
		$db = $srv.Databases.FindByName("Foodmart 2008")
		# Create the scripter object.
		$scripter = new-object Microsoft.AnalysisServices.Scripter
		# Create xmltext writer, which will be used to script the command into the file
		$utf8 = [System.Text.Encoding]::UTF8
		$writer = new-object System.Xml.XmlTextWriter($fileName, $utf8)
		# Create a scriptInfo object.
		$scriptInfo = new-object Microsoft.AnalysisServices.ScriptInfo($db, [Microsoft.AnalysisServices.ScriptAction]"AlterWithAllowCreate", [Microsoft.AnalysisServices.ScriptOptions]"Default", $false)
		# Create an array of the scriptInfo objects. In this example, the array contains just one element.
		$scriptInfos = new-object Microsoft.AnalysisServices.ScriptInfo[] 1
		$scriptInfos[0] = $scriptInfo
		# create a scripter object.
		$scripter.Script($scriptInfos, $writer)
		# Close the XML writer.
		$writer.Close()
	}
	if ($srv.Connected)
	{
		$srv.Disconnect()
	}	
}


################################################################
function ScriptDatabaseAlter
{
	# Create a server object.
	$srv = new-object Microsoft.AnalysisServices.Server
	& {
		# Connect to the server.
		$srv.Connect("localhost")
		$db = $srv.Databases.FindByName("Foodmart 2008")
		$scripter = new-object Microsoft.AnalysisServices.Scripter
		[IO.Directory]::GetCurrentDirectory()
		[string] $fileName = "scriptDBAlter.xml"
		# Create XMLtext writer, which will be used to script the command.
		$utf8 = [System.Text.Encoding]::UTF8
		$writer = new-object System.Xml.XmlTextWriter($fileName, $utf8)
		# Create an array of  major objects. In this example, the array contains just one element.
		$objects = new-object Microsoft.AnalysisServices.MajorObject[] 1
		$objects[0] = $db
		# Script Alter command.
		$scripter.ScriptAlter($objects, $writer, $false);
		# Close the XML writer.
		$writer.Close()
	}
	if ($srv.Connected)
	{
		$srv.Disconnect()
	}	
}


################################################################
function ScriptBatch
{
	# Create a Server object.
	$srv = new-object Microsoft.AnalysisServices.Server
	& {
		# Connect to the server.
		$srv.Connect("localhost")
		[IO.Directory]::GetCurrentDirectory()
		[string] $fileName = "scriptDBBatch.xml"
		# Create XMLtext writer, which will be used to script the command.
		$utf8 = [System.Text.Encoding]::UTF8
		$writer = new-object System.Xml.XmlTextWriter($fileName, $utf8)
		# Start a transactional batch.
		[Microsoft.AnalysisServices.Scripter]::WriteStartBatch($writer, $true)
		foreach ($db in $srv.Databases)
		{
			# Script the processing of the database.
			[Microsoft.AnalysisServices.Scripter]::WriteProcess($writer, $db, [Microsoft.AnalysisServices.ProcessType]"ProcessFull")
		}
		# End the batch.
		[Microsoft.AnalysisServices.Scripter]::WriteEndBatch($writer)
		# Close XML writer
		$writer.Close()
	}
	if ($srv.Connected)
	{
		$srv.Disconnect()
	}		
}


################################################################
& {
	ScriptDatabase
	ScriptDatabaseAlter
	ScriptBatch
}