Vidas Matelis Analysis Services Blog

A few weeks ago I asked a question on Analysis Services 2008 forum if there will be PowerShell support for SSAS 2008. I got an answer from Microsoft, that no native support is planned. I know that Darren Gosbell released PowerShell Provider for SSAS 2005, but for the different reasons I cannot use it with all clients. But just this week Carl Rabeler (Microsoft) posted a new updated set of Analysis Services 2005/2008 Samples. One folder in these samples contained 4 PowerShell script examples that shows how to use it with Analysis Services. I tested these samples and they worked on a standard SSAS 2008 installation. Today Carl Rabeler actually updated these samples – fixed some minor issues and added one more sample – to get a size of Analysis Services database.

So I grabbed that latest Carl’s example and added a few lines of code to iterate through cubes, measure groups, partitions and dimensions and print a simple list of names and sizes. I tested this code on my virtual SSAS 2008 machine and this worked without any problems. I got very excited – so we do have some PowerShell support with SSAS 2008.

Just to see an error message, I run the same code on SSAS 2005 machine (without additional providers installed) and to my surprise it worked as well. So we do have PowerShell support for Analysis Services 2005 and 2008. From PowerSSAS documentation I can see that that provider allows you to navigate Analysis Services database like drive. Standard provider behaves differently, but it does let you to do quite a lot. In my previous posts I showed examples on how to use Windows Scripting Host (WSH) to write scripts for Analysis Services. But I know that WSH has many limitations. For example, Analysis Services supports different set of parameters passed to the same method. I had problems that WSH was expecting just one set of parameters for each method (I believe). I do not know yet what are limitations are with PowerShell, but I hope that somebody who worked with PowerShell a lot (Darren Gosbell, maybe you?) will blog about this. If no, I’ll investigate and post here later myself.

The good news is that Analysis Services 2005 and 2008 supports PowerShell. So it is definitely an option for your scripting needs.

You might wonder why I am so excited about PowerShell? Some of our clients do not give us access to their production environment. So if you need to send client a small fix, it is much easier to script that fix and send it like file, instead of sending a list of steps that describes what to do. Changes could be very simple – like add new translation to a specific attribute, or hide/show a measure in the perspective or cube.

Anyway,  here is my code that I tested on SSAS 2005 and 2008 versions:

## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “localhost”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

foreach ($d in $server.Databases )
{
 Write-Output ( “`nDatabase: {0} Size: {1}`n” -f $d.Name, $d.EstimatedSize )

 foreach ($cube in $d.Cubes) {
  Write-Output ( “Cube: {0}” -f $Cube.Name )
  foreach ($mg in $cube.MeasureGroups) {
   Write-Output ( ” Measure Group: {0} Size: {1}” -f $mg.Name, $mg.EstimatedSize )
   foreach ($part in $mg.Partitions) {
    Write-Output ( ”  Partition: {0} Size: {1}” -f $part.Name, $part.EstimatedSize )
   } # Partition
  } # Measure group
 
  foreach ($dim in $d.Dimensions) {
   Write-Output ( “Dimension: {0}” -f $dim.Name)
  } # Dimensions

 } # Cube

} # Databases

Note: My blogging software replaces double quotes with different symbol, so if you’ll grab this code, then be sure to fix double quotes. 

And here are results – this printout is from SSAS 2008 server with just Adventure Works DW database installed. SSAS 2005 results are very similar:

PS C:\Utilities\Scripts> .\dbinfo.ps1
Database: Adventure Works DW Size: 34535913
Cube: Adventure Works
 Measure Group: Internet Sales Size: 2390949
  Partition: Internet_Sales_2001 Size: 29642
  Partition: Internet_Sales_2002 Size: 78911
  Partition: Internet_Sales_2003 Size: 993042
  Partition: Internet_Sales_2004 Size: 1153218
 Measure Group: Internet Orders Size: 1816480
  Partition: Internet_Orders_2001 Size: 32637
  Partition: Internet_Orders_2002 Size: 86898
  Partition: Internet_Orders_2003 Size: 765137
  Partition: Internet_Orders_2004 Size: 894744
 Measure Group: Internet Customers Size: 1223972
  Partition: Customers_2001 Size: 10307
  Partition: Customers_2002 Size: 27960
  Partition: Customers_2003 Size: 538016
  Partition: Customers_2004 Size: 610657
 Measure Group: Sales Reasons Size: 3215
  Partition: Internet_Sales_Reasons Size: 1034
 Measure Group: Reseller Sales Size: 3308697
  Partition: Reseller_Sales_2001 Size: 304565
  Partition: Reseller_Sales_2002 Size: 801746
  Partition: Reseller_Sales_2003 Size: 1223908
  Partition: Reseller_Sales_2004 Size: 673284
 Measure Group: Reseller Orders Size: 1295396
  Partition: Reseller_Orders_2001 Size: 140197
  Partition: Reseller_Orders_2002 Size: 330174
  Partition: Reseller_Orders_2003 Size: 480043
  Partition: Reseller_Orders_2004 Size: 275198
 Measure Group: Sales Summary Size: 2987696
  Partition: Total_Sales_2001 Size: 80094
  Partition: Total_Sales_2002 Size: 441643
  Partition: Total_Sales_2003 Size: 1144905
  Partition: Total_Sales_2004 Size: 1118404
 Measure Group: Sales Orders Size: 2153776
  Partition: Total_Orders_2001 Size: 133590
  Partition: Total_Orders_2002 Size: 332047
  Partition: Total_Orders_2003 Size: 831851
  Partition: Total_Orders_2004 Size: 801419
 Measure Group: Sales Targets Size: 3656
  Partition: Sales_Quotas Size: 1514
 Measure Group: Financial Reporting Size: 404464
  Partition: Finance Size: 373520
 Measure Group: Exchange Rates Size: 293811
  Partition: Currency_Rates Size: 289223
Dimension: Promotion
Dimension: Product
Dimension: Customer
Dimension: Geography
Dimension: Reseller
Dimension: Sales Territory
Dimension: Employee
Dimension: Scenario
Dimension: Department
Dimension: Organization
Dimension: Account
Dimension: Date
Dimension: Source Currency
Dimension: Sales Reason
Dimension: Reseller Sales Order Details
Dimension: Internet Sales Order Details
Dimension: Sales Channel
Dimension: Destination Currency
Dimension: Sales Summary Order Details
PS C:\Utilities\Scripts>