3

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?

Chilledrat
  • 2,593
  • 3
  • 28
  • 38
ttarchala
  • 4,277
  • 2
  • 26
  • 36

3 Answers3

3

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.

James
  • 433
  • 4
  • 14
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
Darren Gosbell
  • 1,941
  • 13
  • 17
0

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

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64