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 or download the installer from the Releases page.


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.


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.


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.


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:


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