SSAS 2008: Data Management Views

[Via Vidas Matelis]

Vidas recently did a post about a couple of new features that will be available in SSAS 2008 (but that are not in the July CTP). One of which was DMVs (Data Management Views.

These allow you to do things like the following:

SELECT * FROM $system.discover_connections

SELECT * FROM $system.discover_sessions

SELECT
  connection_id
  , connection_user_name
  , connection_host_application
  , connection_start_time
FROM $system.discover_connections

It appears that these DMV's might be providing a "table like" interface over the XMLA discover commands. If you can't wait for SQL 2008, you can get similar functionality from a set of .Net Stored procedures that are a part of the Analysis Services Stored Procedure project (affectionately known as ASSP) Which allow you to do things like:

CALL ASSP.DiscoverConnections()

CALL ASSP.DiscoverSessions()

These functions don't include the facility to specify which columns are returned like the new DMVs will, but they are still quite handy

You can also easily cancel connections, sessions or SPIDs (but this still relies on the SSAS 2005 Cancel mechanism, which is not as reliable as the one that is being built for SSAS 2008)

eg.


CALL ASSP.CancelConnection(<connectionID>)

CALL ASSP.CancelSPID(<SPID>)

CALL ASSP.CancelSession(<sessionGuid>)

And something that is handy for performance testing is the ability to clear the cache for the current database

CALL ASSP.ClearCache()

or for a specific cube in the current database.

CALL ASSP.ClearCache("Adventure Works")

Which is a lot simpler than having to use XMLA snippets.

There is a lot of goodness in ASSP, this is just the tip of the iceberg, you can either download the source and see how we did things or you can just download the compiled DLL and start using it. If you have not seen this project yet, do yourself a favour and check it out.