Analysis Management Objects (AMO) and Powershell 2.0
Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 34
This chapter discusses the AMO managed assembly, which describes a hierarchical model of major objects and minor (dependent) objects for admininstering Analysis Services. The diagram located at http://msdn.microsoft.com/en-us/library/ms345083.aspx is more comprehensive than the one the book has on page 671. The Microsoft Developer Network (msdn.microsoft.com) website has full developer resources, and for this topic see http://msdn.microsoft.com/en-us/library/ms124924.aspx.
The authors provided extensive C# code for this chapter. As with Chapter 33, I decided to translate the console applications into Powershell 2.0. This blog post will go through the solutions provided by the author, and show the PowerShell code (I provided a PowerShell introduction in the Chapter 33 review, and therefore I skip that preliminary and necessary step for this blog post).
AMODependentDelete:
# 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()
}
This code shows how to delete. In my case, I made a copy of the authors’ FoodMart 2008 Analysis Services database, made a backup, and then restored to another name FoodMart 2005. I also added conditional code to check for the number of cubes available before attempting to delete elements. My check is only one of many possible ways to validate whether processing should continue, and I concede that a single criteria is unlikely in a production environment. More likely, you would have a list of several criteria before making changes.
AMOImpactAnalysis:
# 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()
}
This code also uses my fake FoodMart 2005 database because I did not want to affect the working FoodMart 2008. The code includes several enumerated constants (ENUM) in the Update function, and my code shows how to specify those constants. The code also includes the powershell back tick N code, which tells write-output to go to a new line. That back tick is NOT the single quote, but is instead the character (on my English keyboard) which is left of the “1″ key. There are several back tick commands which work with write-output. I will mention too that in making these translations I was often using the “Object Browser” in Visual Studio 2010, which helped me to see the classes. Perhaps future versions of the default PowerShell IDE will also have an integrated Object Browser.
AMODependencyCalculator:
# 3403_AMODependencyCalculator.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.
$dbname = "Foodmart 2008"
$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"
$dim = $db.Dimensions.GetByName("Customer")
# Create DependenciesCalculator.
$dependsCalculator = new-object Microsoft.AnalysisServices.DependenciesCalculator
# Analyze the objects affected by deletion of the "Customers" hiearchy.
$objects = $dim.Hierarchies.GetByName("Customers")
$dependencies = $dependsCalculator.GetDeleteDependents($db, $objects, $false)
write-host "Dependencies"
#Iterate over all dependencies and print their description.
foreach ($dependencyList in $dependencies.Values)
{
foreach ($dependency in $dependencyList)
{
write-host " " $dependency.Description
}
}
}
else
{
write-host "No cubes in Analysis Services database '$dbname'"
}
}
if ($srv.Connected)
{
$srv.Disconnect()
}
This code illustrates the interdependence between major and minor objects, and surfaces what would change if certain objects were altered or removed. Since no actual changes happen, I changed the original code to use FoodMart 2008.
AMODependencyCalculator2:
# 3404_AMODependencyCalculator2.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.
$dbname = "Foodmart 2008"
$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"
# Create DependenciesCalculator
$dependsCalculator = new-object Microsoft.AnalysisServices.DependenciesCalculator
# Analyze the objects affected by deletion of the DSV table
$objects = $db.DataSourceViews.GetByName("FoodMart 2008")
$dependencies = $dependsCalculator.GetDeleteDependents($db, $objects, $false)
write-host "Dependencies"
# Iterate over all dependencies and print their description.
foreach ($dependencyList in $dependencies.Values)
{
foreach ($dependency in $dependencyList)
{
write-host " " $dependency.Description
}
}
}
else
{
write-host "No cubes in Analysis Services database '$dbname'"
}
}
if ($srv.Connected)
{
$srv.Disconnect()
}
This second dependency calculator shows that relationships might be invalidated if some objects were deleted. The lesson I believe to be most important is that AMO provides granular control over the objects in the Analysis Services database. The authors believe that understanding the dependencies is as important as enumerating the objects. I typically prefer using deletions only for entire databases, and otherwise using alterations either in the current database, or making a copy to a new Analysis Services database. As this code snippet shows, partial object deletion can result in a Analysis Services database with incomplete relationships.
AMOSessionShare:
# 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()
}
}
}
This code shows how to share a connection with AMO and ADOMD.NET. The code also includes a transaction start and end, showing how to potentially rollback a series of commands. The PowerShell function CreateCube will return all objects created during that function’s run, and therefore I intentionally made $cube the last object it makes. Then, the code will set the new cube value to be the last object that the function created (referenced by array); if only one object had been made, then it would reference object zero of the array, the only object made. I did not use a similar logic for CreateDataItem (but I could have) since that function only produces one object. This technique shows how to return an object in PowerShell, since unlike C#, PowerShell returns all objects and not just the last one you state.
AMOProcessCancel:
This visual solution shows how to cancel an AMO process. I changed the database to “FoodMart 2008″. Functionally, the code worked for me sometimes. What the code is supposed to do is continue to process the cube and display a message that process completed. If someone hits the “Cancel” button, the code is supposed to display another box, gracefully showing that someone exited the process. However, sometimes, I would “Cancel” the process and it would trigger an exception. I do not know why I had that result (if you have an answer, send it to me and I will give you credit here).
AMOObjectLoad:
# 3409_AMOObjectLoad.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
& {
[string] $connectionString = "Datasource=localhost;"
# following code will connect to the server and send DISCOVER_XML_METADATA request,
# then AMO will populate all properties of the server object and
# will create collections of Major contained objects
$srv.Connect($connectionString)
# following line will not send a request to the server, the Database collection already exists on the server
foreach($database in $srv.Databases)
{
# following line will not send request to the server, Name is already populated
write-host $database.Name
# following line will send request to the server, to retrive all properties of Database object, including it’s children that are major objects
write-host " " $database.Translations[0].Language
}
#…
}
if ($srv.Connected)
{
$srv.Disconnect()
}
The point of this code is that Analysis Services allows metadata enumeration upon connection.
AMODisconnectedMode:
# 3410_AMODisconnectedMode.ps1
# Mark Tabladillo
#
# Last Updated: July 23, 2010
# Program Created: July 23, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
################################################################
# We are oversimplifiyng this method to make
function CreateDatabase
{
$database = new-object Microsoft.AnalysisServices.Database("New Database", "New Database")
$database
}
################################################################
& {
[IO.Directory]::GetCurrentDirectory()
[string] $fileName = "amoserializedb.xml"
# Call helper function that will create database, including the whole tree of its child objects,
# such as data source, cube, dimensions, and so on.
$db = CreateDatabase
# Create an XML writer that will be used to serialize the AMO objects into a file.
$utf8 = [System.Text.Encoding]::UTF8
$writer = new-object System.Xml.XmlTextWriter($fileName, $utf8)
# Save the definitions of the database and the tree of AMO objects into the file.
[Microsoft.AnalysisServices.Utils]::Serialize($writer, $db, $false)
$writer.Close()
# ...Do some operations
# Create an XML text reader that will be used to deserialize the AMO objects
$reader = new-object System.Xml.XmlTextReader($fileName)
# Read the objects from the file and create a database object.
$newDB = new-object Microsoft.AnalysisServices.Database
$newDatabase = [Microsoft.AnalysisServices.Utils]::Deserialize($reader, $newDB)
# $newDatabase = [Microsoft.AnalysisServices.Utils]::Deserialize($reader, (new-object Microsoft.AnalysisServices.Database))
$reader.Close()
# Create server object and connect to the Analysis Server
$srv = new-object Microsoft.AnalysisServices.Server
& {
$srv.Connect("localhost")
# Add the newly created database to the database collection, if no database with the same ID already exists in the collection.
if (! $srv.Databases.Contains($db.ID))
{
$srv.Databases.Add($db)
$db.Update()
}
else
{
$ae = new-object System.ApplicationException("Database $db.ID already exists on the server")
throw $ae
}
}
if ($srv.Connected)
{
$srv.Disconnect()
}
}
As the book states on page 693, this mode allows developers to work in a disconnected mode without having to have a continuous live connection to Analysis Services. That option is available in Business Intelligence Development Studio (BIDS), but applications you make can have that same property. In the PowerShell translation, I found that PowerShell wants to only accept PowerShell objects as variables when instantiating a new-object or when calling a static method (like Deserialize). I therefore added the variables $utf8 and $newDB to allow PowerShell to understand these steps.
AMOScripter:
# 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
}
The Scripter object allows creating XML scripts with DDL commands. Though, if the goal is to help administrators with automation, the method I instead is to write the commands completely in PowerShell. Note that what I provided is a PowerShell translation of how to use the scripter object. Either way can work, and be aware that in large production environments, different administrators may be performing either or both of these methods (XML scripts or PowerShell).
AMOTrace:
This visual solution accesses the Trace object during processing. In my case, I changed the Analysis Services database to “FoodMart 2008″ and I did receive four graceful messages in a message box, “Object referernce not set to an instance of the object.” Then, I received the intended final message “Processing has been completed”. I do not know why I have the former message, but if you have an answer send it to me.
AMOErrorHandling:
# 3415_AMOErrorHandling.ps1
# Mark Tabladillo
#
# Last Updated: July 23, 2010
# Program Created: July 23, 2010
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
& {
try
{
# Open a connection to the server.
$server = new-object Microsoft.AnalysisServices.Server
$server.Connect("localhost")
# …Do some AMO operations
}
catch [Microsoft.AnalysisServices.ConnectionException]
{
write-host "[Microsoft.AnalysisServices.ConnectionException]"
write-host $_.Exception.Message
# Show a dialog box requesting the server name from the user.
# ...
}
catch [Microsoft.AnalysisServices.OperationException]
{
write-host "[Microsoft.AnalysisServices.OperationException]"
foreach ($result in $_.Exception.Results)
{
foreach ($message in $result.Messages)
{
if ($message -is [Microsoft.AnalysisServices.XmlaWarning])
{
write-host $message.Description, "warning"
}
else
{
write-host $message.Description "error"
}
}
}
}
catch [Microsoft.AnalysisServices.ResponseFormatException]
{
write-host "[Microsoft.AnalysisServices.ResponseFormatException]"
throw
}
catch [Microsoft.AnalysisServices.OutOfSyncException]
{
write-host "[Microsoft.AnalysisServices.OutOfSyncException]"
throw
}
finally
{
if ($server.Connected)
{
$server.Disconnect()
}
}
}
In PowerShell, error handling should be done with the TRY, CATCH, and FINALLY statements.
Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6
