How to view MS SSAS 2005 sessions and queries

Question

When browsing the cube in Microsoft SQL Server Analysis Services 2005, I would like to peek at the MDX (supposedly) queries generated by client access tools such as Excel. Is there a tool or method that enables me to do just that?

I'm really looking for something like Oracle's v$sessions -- I know about sp_who and sp_who2 for the relational SQL Server, but is there one for MSAS?

Answer 1

Use SQL Server Profiler - it can connect to Analysis Services... When you create a trace make sure you click "Show All Events" and capture the "Execute MDX" events.

Answer 2

I remember doing something along these lines a few years ago. I am not sure that Analysis Services will actually log the MDX it uses, but it does log something. I believe you can right-click the server properties in AS, and there is a tab to tell it a file to log queries to.

(Sorry I cant be more specific, it was a fair while ago, and I havent got AS in front of me nowadays!)

Answer 3

If you want to see sessions you can do a DISCOVER_SESSIONS xmla call, but that sends back an XML result. To get a tabular result you can use the DMV function from the ASStoredProcedure project

Then you can do something like:

call ASSP.DMV("SELECT * FROM $System.DISCOVER_SESSIONS");

SSAS 2008 has native support for DMV's so you can just do:

SELECT * FROM $System.DISCOVER_SESSIONS