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

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

$srv = new-object Microsoft.AnalysisServices.Server

#Connect to the server.
$srv.Connect("localhost")
#Use the database Foodmart 2005.
$dbname = "Foodmart 2005"
$db = $srv.Databases.GetByName($dbname)

# 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"
	#Iterate all the cubes
	write-host "Before deletion"
	foreach ($cb in $db.Cubes)
	{
		#Print the names of the hierarchies in the "Customer" cube dimension.
		$dimCustomer = $cb.Dimensions.FindByName("Customer")
		if ($null -ne $dimCustomer)
		{
		   foreach ($hier in $dimCustomer.Hierarchies)
		   {
			   write-host $cb.ToString() ": " $hier.ToString()
		   }
		}
	}
	# Remove the hierarchy named "Customers" from the database dimension
	# named "Customer".
	$dbDim = $db.Dimensions.GetByName("Customer")
	$dbHiear = $dbDim.Hierarchies.GetByName("Customers")
	$dbDim.Hierarchies.Remove($dbHiear)
	# Iterate all the cubes and make sure that no cube has the hierarchy
	# "Customers" in the dimension "Customers."
	write-host "After deletion"
	foreach ($cb in $db.Cubes)
	{
		$dimCustomer = $cb.Dimensions.FindByName("Customer")
		if ($null -ne $dimCustomer)
		{
			foreach ($hier in $dimCustomer.Hierarchies)
			{
				write-host $cb.ToString() ": " $hier.ToString()
			}
		}
	}
}
else
{
	write-host "No cubes in Analysis Services database '$dbname'"
}

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