SQL Server 2008 and Analysis Services DMV's

One of the cool new features coming in SQL Server 2008 Analysis Services are Dynamic Management Views (DMV).  Actually I never thought of them as DMVs coming from my background, but since everyone else calls them that, there's no reason we shouldn't either.

So what are they and how did they come about?

DMX users are most likely familiar with the syntax:

SELECT * FROM MyDataMiningModel.CONTENT

What you may not know is that this is really a DMX query against the MINING_CONTENT schema rowset - we simply exposed via the SQL-like syntax of DMX for convenience, placing a schema restriction on the model name.

While we were implementing some features around resource monitoring, we realized we could expose the information we provided through this mechanism and make it much more usable than schema queries, since most query tools can't do them.

So what we did was to add the ability to query any arbitrary Analysis Services schema rowset through DMX.  For example

SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODELS

returns the MINING_MODELS schema.  As you would expect, you can select any subset of columns, filter by arbitrary where clause, and for those adventurous enough, use as input to other DMX statements - e.g. INSERT INTO or PREDICTION JOIN.

In my opinion, some of the most interesting applications are in integration with other BI tools.  For example, you can now create SSIS packages that operate on every dimension, cube, mining model, etc.  You can create a selector in Reporting Services populated with the result of an AS schema query.  You can even create AS cubes based on AS cube information!

Some examples:

SELECT * FROM $SYSTEM.MDSCHEMA_SETS WHERE SCOPE=2 // return all session named sets

SELECT SERVICE_DISPLAY_NAME FROM
             $SYSTEM.DMSCHEMA_MINING_SERVICES
              WHERE MSOLAP_SUPPORTS_DATA_MINING_DIMENSIONS // return names of algorithms that can create OLAP dimensions