Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Anyone who has been reading this blog for a while will know that I was quite taken with PowerShell when it was released and was even playing with it back when it was only known by it's code name of "Monad".

Well, back when it was still known as Monad, I started experimenting with writing a provider for Analysis Services. It was one of those side projects that I was toying with in my spare time and I kept telling myself that I just need to add one or two more features and then it would be ready for release. Well there are still more features I would like to add, but I figure I am at a point now where I have a good understanding for how it all hangs together and how I can add additional functionality going forward.

So today I am happy to announce the first public release of what I am calling powerSSAS. I have released it as an open source project on codeplex and you can visit the home page here www.codeplex.com/powerSSAS or download the installer from the Releases page.

PowerSSAS_Download_small

So, you might be wondering - Why would I want to use PowerSSAS? - well, I'm glad you asked, let's have a look shall we?

PowerShell includes functionality that allows it to present any hierarchical data store as a "drive" so that it can be navigated in much the same way as you would navigate the file system. PowerShell itself includes a number of these providers including ones for the registry and environment variables as well as some for PowerShell objects like functions and aliases. The core functionality of powerSSAS is the ability to present information from the AMO library as if it was a drive.

The screenshot below is taken from PowerShell Analyzer which is a very cool tool for developing PowerShell scripts (I am fortunate enough to have a complimentary copy of it). One of the features of PowerShell Analyzer is it's provider explorer, which gives you a Windows Explorer style view of your PowerShell providers.

powerssas

Below is another screenshot from the Provider Explorer in PowerShell Analyzer, this time I have called the drive "amo" - you could connect to any number of servers and create a drive for each one.

image

The ServerProperties is interesting as it lets you browse all the properties. If you look at the example below you can see that I have selected the LogDir setting and the properties window in PowerShell Analyzer displays all the properties that relate to the LogDir setting.

image

In the screenshot above you can see that I have a particularly imaginatively named role called "Role" which has the highly secure "Everyone" group as a member - this is an example of how never to set up a role :)

But the goodness does not stop there. Even though PowerShell Analyzer rocks, you don't need it to work with powerSSAS.

 Below is a screen shot of a session with the default PowerShell shell. (I don't normally have a purple prompt, but it helps to differentiate the input/output from the prompt)image

So let's step through what was going on with each of those commands:

PS > add-pssnapin powerSSAS

This adds the powerSSAS assembly to this shell session, making the provider and all the cmdlets available in this session (there are ways this can be automated, but that is a topic for another post)

PS > new-psdrive demo powerSSAS localhost

Here I am telling powerSSAS that I want a new drive called "demo", that I want to create it using the "powerSSAS" provider and that it should connect to the "localhost" server (this can be any valid SSAS server instance that you have access to)

PS > demo:

This line navigates to the "demo" drive, this is not a standard provider feature, if you look at the functions on the system you will see that I have made the provider generate a function with the name of the drive followed by a colon which sets the location to the powerSSAS drive. This is a trick that the PowerShell team themselves use to navigate to file system drives like C, D, E, etc by typing C:, D:, E: etc.
This puts us in the context of the AMO Server object.

PS > cd databases

This line navigates us into the Databases container of the server

PS > dir

This line displays the context of the current container, at this point you could also have also typed "gci" or "ls" these along with "dir" are all aliases to the underlying Get-ChildItem cmdlet.

PS > cd "Adventure Works DW"

This line navigates to the "Adventure Works DW" database object

PS > cd cubes

This navigates to the cube collection of the database

PS > dir | ft ID, Name, State, LastProcessed

This line gets all the child items for the cube collection, pipes them to "ft" (which is an alias for format-table) and displays the ID, Name, State and LastProcessed properties.

You'll notice that this command is different line 5. this is partly show a different method of listing an object's properties and partly because I have setup a default format for databases, but I have not done one yet for cubes, so it currently dumps out all the properties for a cube which is not so easy to read.

Hopefully even from this simple example you can start to see some of the potential.

But that's just the beginning, consider the following:

image

PS > cd sessions

We start by navigating to the sessions collection of the server. But wait a minute... the server object does not have a sessions collection... well, it does in powerSSAS. This is an extension that I have built into the provider and I plan to add more of them, connections, traces and jobs are a few obvious ones.

PS > dir

This lists the contents of the "sessions" container. Basically running a DISCOVER_SESSIONS command behind the scenes.

PS > dir | where { $_.LastCommandCpuTimeMs -gt 0 }

 This is where things start to get interesting. Here I am passing the objects returned from the "dir" into the "where-object" cmdlet (aliased as "where") and then I am using a script block to examine each object looking for those with a LastCommandCpuTimeMs of greater than (that's the -gt) 0 milliseconds.

PS > dir | where { $_.LastCommandCpuTimeMs -gt 0 } % { $_.kill() }

Now that I have found the sessions that had a LastCommandCpuTimeMs of more than 0 milliseconds, I want cancel those "expensive" sessions (hey, this is just an example - my laptop is not under a lot of heavy load <g>). So foreach of these objects (% is short for foreach-object) I am calling the Kill() method. (The session class and the associated kill method are powerSSAS types, not part of native AMO)

PS > dir | where { $_.LastCommandCpuTimeMs -gt 0 }

This command shows that those previous two sessions have in fact been canceled.

 

Now if you don't want to create a drive and navigate like this I have built this functionality as cmdlets, in fact the provider is just calling into the cmdlets, so you could also have simply written the following:

PS > get-ASSession localhost

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

I want to improve the ways you can call the clear-ASSession cmdlet, but this is how it is working at present

Finally, scripts like the one I blogged about last year where you could process all dimensions that begin with the letter "P", or any that have not been processed in the last 30 days are entirely possible.

So, give it a go and see if you like it. If you have any ideas, questions or requests you can contact me through this blog or you can post something in the powerSSAS discussion list or issue tracker.

There is a bit of a learning curve to figuring out how PowerShell works, but once it clicks you will be hooked too. I plan to continue work on enhancing powerSSAS and plan to share more about what you can do with it and some things I learned while building it.

Print | posted on Monday, November 19, 2007 11:24 PM

Comments on this post

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
Awesome, Darren. Can't wait to play with it.
Left by Kevin Dente on Nov 20, 2007 7:02 AM

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
How can I export data from the SSAS into excel (e.g.)?
the Provider Explorer doesn't support exporting !
Left by Asaf on Dec 27, 2007 12:59 AM

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
I don't think you can do it from the Provider Explorer, but 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 this:

dir | Select-Object Name,State,LastSchemaUpdate,LastProcessed | export-Csv c:\dims2.csv
Left by Darren Gosbell on Dec 31, 2007 8:10 AM

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
Downloaded POWERSSAS from the this site. However it is not getting installed on my machine. Instutil.log file shows the following error message:

Quote

An exception occurred during the Install phase.
System.InvalidOperationException: Unable to get installer types in the C:\Program Files\powerSSAS\powerSSAS.dll assembly.
The inner exception System.Reflection.ReflectionTypeLoadException was thrown with the following error message: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information..

UnQuote
Left by RAHUL on Feb 22, 2008 10:10 PM

# Hello World?

Requesting Gravatar...
When you do a "dir" from the root and export to CSV, you retrieve a column "Hello World"...

Also, on codeplex, your instruction states "add-psdrive" instead of "new-psdrive".

This rocks! Will you be updating for 2008?
Left by Kory on Mar 04, 2008 5:21 AM

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
just found this post, and it made my day.
Left by Karl Prosser on Sep 16, 2008 4:05 PM

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
@Kory - Hi Kory, sorry it's taken so long to reply. Somehow I missed your comment. I am planning to update to 2008, I have this working on my local machine, I have just gotten caught up in other things. And I will check, but I think the "Hello World" is a test script property in the types files, so that should be an easy fix, thanks for letting me know.

@Karl - Glad to have made your day. Powershell Analyzer rocks - I still use it more than any other powershell tool.
Left by Darren Gosbell on Sep 16, 2008 4:32 PM

# re: Announcing the release of PowerSSAS: a PowerShell provider for Analysis Services

Requesting Gravatar...
@RAHUL - You would need to have AMO installed on your workstation for PowerSSAS to work, this is the only pre-requisite that you need. If you have BIDS on your machine you should have this already.
Left by Darren Gosbell on Sep 16, 2008 4:36 PM

# source code

Requesting Gravatar...
Seen source code on codeplex available, but can't download any source code.
Other download location available ?
Left by Philip Germann on Jan 16, 2009 9:53 PM

Your comment:

 (will show your gravatar)