powerSSAS: DMV Equivalents

I was reading Vidas Matelis' recent post on the metadata rowsets in SSAS 2008 and this got me to thinking about a discussion that Chris Webb and I had recently about the new "DMV" functionality is SSAS 2008. And that it basically that they are simply gives you an easier way to access information that is available through XMLA discover commands. And that you could already get to this information relatively easily through the Discover() function in the Analysis Services Stored Procedure (ASSP) project.

This has prompted me to do a bit of exploration around DMV's and schema rowsets and this is the first of a few posts in this area.

This first post in the series is about a way to get at this information through powerSSAS (www.codeplex.com/powerSSAS) and that is by using the send-XmlaDiscover cmdlet.

The easiest way to explain how this cmdlet works is through an example, so let's have a look at one.

Send-XmlaDiscover localhost MDSCHEMA_CUBES | WHERE {$_.CubeSource -eq 1} | Format-Table CubeName, BaseCubeName


Now if you don't have much experience with PowerShell the syntax may seem a little unusual. I'm pretty sure I have said this before, but there is a bit of a learning curve when you first start using PowerShell but it is worth sticking with it as it is really powerful.

So let's break down my example and have a look at what is going on.

1. Send-XmlaDiscover localhost MDSCHEMA_CUBES

You will notice that I start off with the Send-XmlaDiscover cmdlet, this is part of powerSSAS and you will have to have added the snapin to have it available. I am passing two parameters to this cmdlet, the name of the SSAS server and the rowset to return. And what comes back from that cmdlet is a set of objects. This is an important distinction, it is not returning rows of text, but a collection of objects. If you want to see what those objects "look" like, then you can "pipe" the output  to the get-member cmdlet.

eg. Send-XmlaDiscover localhost MDSCHEMA_CUBES | get-member

The get-member cmdlet is great for discovering the "shape" of the objects that are being returned. This can be quite handy with the send-XmlaDiscover cmdlet as it returns differently "shaped" objects depending on the rowset parameter. What I mean by this is that the resulting objects will have a different set of properties depending on the rowset that is queried.

2. | WHERE {$_.CubeSource -eq 1}

To filter the collection of objects returned from send-XmlaDiscover I am using the where-object cmdlet (aliased as "where"). The vertical bar is the pipe character which sends the objects output by send-XmlaDiscover as input to the where-object cmdlet. The parameter that is being passing to the where-object cmdlet is what is known as a script block (it uses curly braces like sets in MDX) and when this script block evaluates to true the input object is passed through to the output.

Inside the script block the underscore variable "$_" represents the current object and I am testing the CubeSource property to see if it is equal to 1. You will notice that the property is called CubeSource and not CUBE_SOURCE, I have built this feature into the cmdlet, in that it removes the underscores and "proper cases" the columns from the xmla rowset. You will also notice that instead of using the "=" symbol to test for equality that powershell uses the -eq operator. In fact powerShell uses a number of two letter operators.

-eq equal to
-ne not equal
-gt greater than
-ge greater than or equal to
-lt  less than
-le less than or equal to
-match matches using regular expressions.


3. | FORMAT-TABLE CubeName, BaseCubeName

In the last section of this script I am piping to the format-table cmdlet (this can also be aliased as "ft") and only returning the CubeName and BaseCubeName properties. Format-Table is a cmdlet that I use quite a bit, it outputs a collection of objects putting each property in it's own column.

So with Send-XmlaDiscover combined with the built-in cmdlets of where-object (where), sort-object (sort) and format-table (ft) you can do pretty much everything that the new DMV's in SQL 2008 can do plus a bit more.

Here are a few more examples for you to consider (each of the three examples should be entered on a single line):

Send-XmlaDiscover localhost DISCOVER_SCHEMA_ROWSETS

Send-XmlaDiscover localhost MDSCHEMA_MEASUREGROUPS | where {$_.MeasureGroupCaption -Match "Sales"} | sort MeasureGroupName | ft CubeName, IsWriteEnabled

Send-XmlaDiscover localhost DISCOVER_PROPERTIES | where {$_.PropertyAccessType -match "Write"} | ft

Print | posted on Monday, December 10, 2007 10:37 PM