A PowerShell provider for Analysis Management Objects

The more I play with PowerShell the more I like it. And now that I have a provider working that allows me to link in an Analysis Services server and navigate through the object hierarchy.

Here is an early picture of the Shell (developed while PowerShell was still know by its code name of Monad)

The screenshot above shows a PowerShell console that is connected to a drive called “amo“ which is linked to the SQL05 instance of Analysis Services 2005 on my localhost. One of the biggest differences between PowerShell and other shells is the way it passes objects about instead of strings. You can see that I have wrapped the AMO objects and you have full access to the properties and methods of these objects. You can see in this example that the "amo" drive has an AmoServer property which exposes a Microsoft.AnalysisServices.Server object.

So what can you do with an AMO Provider in PowerShell that you can't do in a .Net program? Once the provider is installed you can simply run scripts from text files to manager your AS 2005 server. If you need to change the behaviour of the script, or if you need a slightly different variation of the script, you can simply fire up note pad and alter it.

Below is an examples of some simple commands:

You can see from these examples that you can easily get a list of all the databases on the server and their estimated sizes. The second listing is of all the dimensions in the "Adventure Works DW" sample database and the last date that they were processed

Fairly simple examples, but consider the following script:

# Get a reference to the AMO 'drive' 
$svr = (get-item amo:) 
# Start a transaction 
$svr.BeginTransaction() 

# This is a PowerShell error handler, if any errors are raised the 
# transation will be rolled back. 
trap { 
   write-host "An Error has occurred, the transation is being rolled back." 
   $svr.RollbackTransaction 
   write-host $_ 
   BREAK 
} 

# get a list of dimensions from Adventure Works DW  
# that start with the letter "P" 
$dims = get-child "\databases\Adventure Works DW\dimensions" | where-object {$_.name.StartsWith("P")} 

# Process those dimensions 
foreach ($dim in $dims) {$_.Process()} 

# Get the current date and time 
$date = date 

# get a list of dimensions that have not been processed  
# in the last 30 days 
$dims = get-child "\databases\Adventure Works DW\dimensions" | where-object {$date.Subtract($_.LastProcessed).Days - gt 30} 

# process those dimensions 
foreach ($dim in $dims) {$dim.Process()} 

# Commit the Transaction 
$svr.CommitTransaction 

The examples I have been working with at the moment have just focused on displaying information and processing various objects. But because of the object based nature of PowerShell you can really do anything with it that you can do with a .Net program. I will see if I can work up some examples of modifying structures, merging partitions and other interesting things.

I think this is pretty cool, but then that could be just me. :)  

I would be interested in comments from anyone else that thinks this might be useful.

Print | posted on Sunday, May 7, 2006 8:14 PM

Comments on this post

# re: A PowerShell provider for Analysis Management Objects

Requesting Gravatar...
Hi Darren,
I think it is a fantastic idea and that such a provider would be very usefull in everyday DW maintenance. I was looking for such a tool for some time. Will it ever be publically released ?
Nice job again!
Left by Gian Piero Anselmi on Jun 26, 2007 12:46 AM

Your comment:

 (will show your gravatar)