Cancelling a query in SSAS is not as straight forward it should be. There’s not an activity monitor for SSAS like the one built into SSMS for the relational engine. To cancel a query you need to end a session or connection. This is documented by Microsoft here: Disconnect Users and Sessions on Analysis Services Server . Basically, you run one of the following DMV queries to get the ConnectionID, SessionID, or SPID that’s executing the query to cancel.
Select * from $System.Discover_Sessions
Select * from $System.Discover_Connections
Select * from $System.Discover_Commands
Then, you pass the Connection, SessionID, or SPID to the respective tag in the following XML/A. This code executes in an MDX query window.
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ConnectionID>111</ConnectionID> <SessionID>222</SessionID> <SPID>333</SPID> <CancelAssociated>1</CancelAssociated> </Cancel>
I tend to cancel the SPID rather than the Connection or Session. To get the SPID I run the first DMV ;
Select * from $System.Discover_Sessions Also, I can see the query text and the user that executed the query with this DMV. I will also note that the cancelling the Session by SessionID throws an error. I’m sure there’s a good solution, but I didn’t troubleshoot it as the cancelling by SPID or ConnectionID works fine.
As you can see, it’s not too difficult to cancel a query. It’s easy to built SSRS reports using these DMVs. Chris Webb wrote a blog post about Killing Queries From Reporting Services. I will probably implement something similar to this. I also found another good post where he writes about Killing sessions automatically with SSIS. I might do this too.