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

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

# Create a server object.
$srv = new-object Microsoft.AnalysisServices.Server
& {
	# Connect to the server.
	$srv.Connect("localhost")
	# Choose the database.
	$db = $srv.Databases.GetByName("FoodMart 2005")	
	
	# Check if there are actually cubes in the named database
	$cubeCount = $db.cubes.count
	if ($cubeCount -ge 0)
	{
		write-host "Analysis Services database '$dbname' has $cubeCount cubes"
		write-host "Processing..."
		# Add the attribute named Marital Status from the Customer dimension.
		$dbDim = $db.Dimensions.GetByName("Customer")
		$attr = $dbDim.Attributes.GetByName("Marital Status")
		$dbDim.Attributes.Remove($attr)
					
		# Allocate the collection for the warning that might be returned to the application.
		$warnings = new-object Microsoft.AnalysisServices.XmlaWarningCollection
		# Allocate the collection for the details of impact analysis.
		$impactDetalCollection = new-object Microsoft.AnalysisServices.ImpactDetailCollection
		# Analyze the objects that will be affected if the database were updated, but don’t update the database.
		$db.Update([Microsoft.AnalysisServices.UpdateOptions]"ExpandFull", [Microsoft.AnalysisServices.UpdateMode]"Default", $warnings, $impactDetalCollection, $true)

		# Iterate the collection of impact details and print: impacted objects, impact, and description of the impact.
		foreach ($impactDetail in $impactDetalCollection)
		{
			write-host $impactDetail.Object.Name "`n" $impactDetail.Object.GetType() "`n" $impactDetail.Impact.ToString() "`n" $impactDetail.Description
		}
	}
	else
	{
		write-host "No cubes in Analysis Services database '$dbname'"
	}
}

if ($srv.Connected)
{
	$srv.Disconnect()
}