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
, 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)


CALL ASSP.CancelConnection(<connectionID>)
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.

Print | posted on Monday, August 13, 2007 10:54 AM

Comments on this post

# re: SSAS 2008: Data Management Views

Requesting Gravatar...
Yeah, I'd recommend ASSP too!
Left by Combi Boiler on May 12, 2010 9:04 PM

# re: SSAS 2008: Data Management Views

Requesting Gravatar...
life-saver for ssas 2005, still amazed they have such archaic ways of killing runaway queries in ssas 2005. thanks!
Left by Jordan on Jul 21, 2010 10:54 PM

# re: SSAS 2008: Data Management Views

Requesting Gravatar...
Very informative..
Thank you for posting.
Left by Aftab Ansari on Dec 15, 2011 5:13 AM

Your comment:

 (will show your gravatar)