There’s a few blog posts out there that describe how to define and run server-side traces for SSAS (such as this one) but recently I was looking into how to subscribe to a server-side trace that’s already running, and while there’s a good thread on the forum I didn’t find much other information out there on the subject. Time for a blog post, then.
First of all, you need a trace running on the server before you can subscribe to one. Luckily, you can find out what traces are running by querying the discover_traces DMV as follows:
select * from $system.discover_traces
Unless you’ve specifically turned it off, you’ll see at least one trace running on each SSAS instance: the flight recorder trace, which has the ID FlightRecorder. You can then subscribe to a trace by executing an XMLA command like this:
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/"> <Header> </Header> <Body> <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> <Command> <Subscribe xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <TraceID>FlightRecorder</TraceID> </Object> </Subscribe> </Command> <Properties> <PropertyList> </PropertyList> </Properties> </Execute> </Body> </Envelope>
Or, alternatively, anywhere you can run an MDX query (for example from an MDX query window in SQL Management Studio) you can run just the Subscribe:
<Subscribe xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <TraceID>FlightRecorder</TraceID> </Object> </Subscribe>
Once you’ve executed either of the above, the command will run indefinitely until you cancel the session it’s running on (it may look like it’s hanging, but it isn’t). To cancel the session you’ll need to find its SPID, which again you can find by querying the discover_commands DMV:
select * from $system.discover_commands order by command_start_time
You can then take the SPID and execute an XMLA Cancel command like this one:
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <SPID>1887</SPID> </Cancel>
Once you’ve done this you’ll see the data from your trace returned at last:
I had thought this might be an easy-ish way of getting trace data into PowerPivot for analysis, but unfortunately as soon as you kill the session the subscribe is running on you see an error in the PowerPivot window. But it will work in SSRS if you use an OLEDB connection and can find an easy way of killing a session from inside an SSRS report – I’m sure it’s possible, using this technique, but it won’t be elegant. To be honest, though, in most cases saving the trace to a table in SQL Server, or to a file, will be more convenient if you want to use trace data for analysis.