powerSSAS - A PowerShell provider for SQL Server Analysis Services

Updated Wiki: Home

dgosbell — Fri, 20 Jun 2008 01:14:09 GMT

Project Description
A PowerShell provider for SQL Server Analysis Services. It implements all the interfaces required to enable you to navigate an SSAS server like a drive.

Currently the provider is a fairly thin wrapper over the AMO object model. The aim is to extend the object model to make easy to work with SSAS from the command line and to also add a number of useful cmdlets to make certain tasks easier.

Introduction to PowerSSAS
PowerSSAS release announcement - this is currently the best overview of what powerSSAS has to offer.

Installation Instructions
The current version comes with a full installer, once it is installed you just need to add the snapin to your session and you are off. The following example will load the provider and create a drive in powershell called "ssas:" that is connect to the default Analysis Services instance on the localhost.

PS > add-pssnapin powerSSAS
PS > new-psdrive ssas powerSSAS localhost

You can then type "ssas:" to navigate to the drive and start using "dir" and "cd" to naviagate around like you would on a file system.

CmdLets

Example Scripts

Getting a list of active sessions

PS > get-ASsession localhost

Getting a list of active connections

PS > get-ASconnection localhost

more Example Scripts

Updated Wiki: Home

dgosbell — Fri, 20 Jun 2008 01:08:59 GMT

PS > add-pssnapin powerSSAS
PS > new-psdrive ssas powerSSAS localhost

You can then type "ssas:" to navigate to the drive and start using "dir" and "cd" to naviagate around like you would on a file system.

CmdLets

Example Scripts

Getting a list of active sessions

PS > get-ASsession localhost

Getting a list of active connections

PS > get-ASconnection localhost

Source code checked in

dgosbell — Wed, 16 Apr 2008 21:30:19 GMT

initial check in on some new cmdlet functionality and refactoring of the discover cmdlets

NEW POST: Future of PowerSASS

dgosbell — Tue, 26 Feb 2008 01:58:01 GMT

It has been a little quiet, but it is still being developed. In fact just last night I compiled a version against SSAS 2008 CTP6 and was exploring the AMO Warnings that you see in BIDS as "blue squiggles". I have a few ideas for future enhancements. I was kind of curious to see what the SQL team came up with for SQL 2008, but there will only be a provider for the relational engine and at the moment it looks a bit rougher than powerSSAS in some areas.

If there is anything in particular that you would like to see addressed just let me know.

NEW POST: Future of PowerSASS

slimjim — Mon, 25 Feb 2008 09:27:56 GMT

Does anyone know the future of PowerSASS. The project seems to be quiet and I wonder if it is still being developed.

UPDATED WIKI: Exporting SSAS information to Excel

dgosbell — Tue, 08 Jan 2008 00:28:00 GMT

If you want to get information out of powerSSAS into Excel you can use the Export-Csv cmdlet that is built-in to PowerShell. Something like the following would export a list of dimensions to a .csv file.

add-PSSnapin powerssas
new-PSDrive ssas powerssas localhost
cd ssas:
cd "Databases\Adventure Works DW\Dimensions"
# this lists out all the properties
dir | export-Csv c:\dims.csv

And if you only wanted certain properties, you could change the last line to something like the following, using the select-object cmdlet (aliased as "select" in this example) to only return the specified properties:

dir | Select Name,State,LastSchemaUpdate,LastProcessed | export-Csv c:\dims2.csv

If you really have special requirements, or if you simply need to use a delimiter other than a comma (eg. if your region uses commas for the decimal separator) then you could roll your own string and redirect that to a file.

add-PSSnapin powerssas
new-PSDrive ssas powerssas localhost
cd ssas:
cd "Databases\Adventure Works DW\Dimensions"
# loop through the dimensions and 
# redirect the constructed string to a file
$( 
  foreach ($d in get-ChildItem)
  {
    "$($d.Name)`t$($d.State)"
  } 
) > c:\dims.txt

Note that Powershell uses the backtick (`) as an escape character, so the `t is interpreted as a tab character and I am collecting the whole loop up inside an expression $(...) and redirecting that through to a file.

Finally if you wanted absolute control over the output, you could instantiate a copy of Excel and control it using the COM object model.

$objXL = New-Object -comobject Excel.Application

But there are issues with that approach, not the least of which is the fact that it does not work on my laptop! Trying to call the Add method of the Workbooks collection throws the following unhelpful error:

"Exception calling "add" with "0" arguement(s): "Old format or invalid type library. <Exception from HRESULT: 0x80028018 <Type_E_INVDATAREAD>>"_

This is possibly because I am using a regional setting of en-AU, not en-US. As googling for this issue turned up the following KB article which was useful - http://support.microsoft.com/kb/320369.

Using the suggested InvokeMember work around looked promising and I did manage to get Excel working to a degree, but I am not going to bother posting that code here as it was pretty messy. And I could not get the second suggested work around of changing the culture of the current thread to work at all from PowerShell.

UPDATED WIKI: Example Scripts

dgosbell — Tue, 08 Jan 2008 00:01:06 GMT

This page links off to various samples which demonstrates the functionality of powerSSAS

UPDATED WIKI: Home

dgosbell — Fri, 23 Nov 2007 01:43:05 GMT

PS > add-pssnapin powerSSAS
PS > add-psdrive ssas powerSSAS localhost

You can then type "ssas:" to navigate to the drive and start using "dir" and "cd" to naviagate around like you would on a file system.

CmdLets

Example Scripts

Getting a list of active sessions

PS > get-ASsession localhost

Getting a list of active connections

PS > get-ASconnection localhost

UPDATED WIKI: send-XmlaDiscover

dgosbell — Wed, 21 Nov 2007 05:51:29 GMT

Cmdlet: Send-XmlaDiscover

This cmdlet sends and XMLA discover command to the server and returns the results.

The following command will display a list of all the possible rowsets that you can query

PS > send-XmlaDiscover localhost DISCOVER_SCHEMA_ROWSETS

CREATED FEATURE: clear-ASCache cmdlet

dgosbell — Wed, 21 Nov 2007 05:16:10 GMT

Add a cmdlet that will execute a clear cache command

CREATED FEATURE: clear-ASSession - allow it to accept objects piped from get-ASSession

dgosbell — Wed, 21 Nov 2007 05:14:38 GMT

Add a parameter that accepts Session objects from the pipeline

CREATED FEATURE: clear-ASSession - add SPID support

dgosbell — Wed, 21 Nov 2007 04:45:06 GMT

Add the ability to cancel a session by SPID

UPDATED WIKI: clear-ASSession

dgosbell — Wed, 21 Nov 2007 04:43:49 GMT

Cmdlet: clear-ASSession

This cmdlet will cancel the specified session on the specified server

clear-ASSession -serverName localhost -ID AFD040D3-34CD-4DEA-AADC-1421E593F70A

UPDATED WIKI: get-ASSession

dgosbell — Wed, 21 Nov 2007 04:41:35 GMT

Cmdlet: get-ASSession

This cmdlet will get a list of the sessions for the specified server

PS > get-ASSession -ServerName localhost

UPDATED WIKI: Home

dgosbell — Wed, 21 Nov 2007 04:40:04 GMT

PS > add-pssnapin powerSSAS
PS > add-psdrive ssas powerSSAS localhost

CmdLets

Example Scripts

Getting a list of active sessions

PS > get-ASsession localhost

Getting a list of active connections

PS > get-ASconnection localhost

UPDATED WIKI: Getting a list of Sessions

dgosbell — Tue, 20 Nov 2007 03:07:13 GMT

At the simplest level you can either call the get-ASSession cmdlet

PS > add-pssnapin powerSSAS # this only needs to be run once per session
PS > get-ASSession localhost

This can be run from any location (provided you have powerSSAS Loaded

Or if you have created a drive then you can navigate to the sessions collection

PS > add-pssnapin powerSSAS
PS > new-psdrive ssas powerSSAS localhost
PS > ssas:
PS > cd sessions
PS > dir

UPDATED WIKI: Getting a list of Sessions

dgosbell — Tue, 20 Nov 2007 03:06:32 GMT

At the simplest level you can either call the get-ASSession cmdlet

PS > add-pssnapin powerSSAS # this only needs to be run once per session
PS > get-ASSession localhost

This can be run from any location (provided you have powerSSAS Loaded

Or if you have Creating a drive then you can navigate to the sessions collection

PS > add-pssnapin powerSSAS
PS > new-psdrive ssas powerSSAS localhost
PS > ssas:
PS > cd sessions
PS > dir

UPDATED WIKI: Getting a list of Sessions

dgosbell — Tue, 20 Nov 2007 03:06:13 GMT

At the simplest level you can either call the get-ASSession cmdlet

 PS > add-pssnapin powerSSAS # this only needs to be run once per session
PS > get-ASSession localhost

This can be run from any location (provided you have powerSSAS Loaded

Or if you have Creating a drive then you can navigate to the sessions collection

PS > add-pssnapin powerSSAS
PS > new-psdrive ssas powerSSAS localhost
PS > ssas:
PS > cd sessions
PS > dir

UPDATED WIKI: Getting a list of Sessions

dgosbell — Tue, 20 Nov 2007 03:04:43 GMT

At the simplest level you can either call the get-ASSession cmdlet

 
PS > add-pssnapin powerSSAS # this only needs to be run once per session
PS > get-ASSession localhost

This can be run from any location (provided you have powerSSAS Loaded

Or if you have Creating a drive then you can navigate to the sessions collection

PS > add-pssnapin powerSSAS
PS > new-psdrive ssas powerSSAS localhost
PS > ssas:
PS > cd sessions
PS > dir

UPDATED WIKI: Example Scripts

dgosbell — Tue, 20 Nov 2007 02:17:25 GMT

This page links off to various samples which demonstrates the functionality of powerSSAS

ListingSessions