none
Canceling requests sent to an OLAP server

    Question

  • Hi

     

    I am building a rich client application in C# that sends requests to an OLAP server. Now the datasets I receive are quite large and I want to give users the option to cancel the request (just like the cancel button in SQL Server Mangement Studio). Can you guys please guide me as to how can I go about it?

     

    Any word would be deeply appreciated.

     

    Thanks

     

    Wednesday, March 19, 2008 4:24 PM

Answers

All replies

  •  

    You can send a Cancel XMLA statement, see http://msdn2.microsoft.com/en-us/library/ms187149.aspx

    Tuan

    Wednesday, March 19, 2008 5:26 PM
  • Hello,

     

    please read the post here: http://blog.vyvojar.cz/radim/archive/2006/07/31/8834.aspx

     

    You can take ActivityViewer as good starting point. In my scenario I've converted it to web application and deployed to Reporting Services portal. Also, you can filter displayed sessions only to current user. But you need to be aware of security settings, if I'm not wrong then user has to be server-wide admin.

     

    Radim Hampel

    Wednesday, March 19, 2008 8:36 PM
  • Hi Radim

    Thanks for you prompt reply. But the situation I am in does not demand an admin's intervention. At the server side we do have automated techniques to take care of "orphaned" requests. What I am really interested in is how do I make my application actually cancel it. As of now I am going to try out Tuan's suggestion and will let you know what happens next.

    Thanks again to both of you.
    Thursday, March 20, 2008 8:01 AM
  • A word of caution when using <Cancel> XMLA request - It does not terminate the server operartion most of the times. For problamatic queries, even after you issue a Cancel request for that connection, the SSAS will not release it. Not sure why this is the case.

     

    Thursday, March 20, 2008 1:23 PM
  • Check the CancelCoordinatorCount property, you can decrease a bit from its default value, which will force the engine to check the IsCancelled property more often. As a result, the queries will be stopped faster. We had big problems with that, but after some post SP2 updated + with using that CancelCoordinatorCount thighs got much better.
    Thursday, March 20, 2008 1:35 PM
  •  Arun wrote:
    A word of caution when using <Cancel> XMLA request - It does not terminate the server operartion most of the times. For problamatic queries, even after you issue a Cancel request for that connection, the SSAS will not release it. Not sure why this is the case.

     

     

    The only way this will happen is if you are not an administrator of the SSAS. I wrote an in-house VB.NET SSAS Monitoring tool that allows canceling of any query and have only come into the problem you describe a few times over the past year. Usually if the I/O of the query is out of this world, it occurs. I think we've, as a team, canceled well over 1000+ queries thus far. My application cancels by SPID and sets CancelAssociated=True.

    Thursday, March 20, 2008 9:25 PM
  • Happened to test this last week. When I specify the connection and session, the client connection is disconnected but the process seems to be executing in the server.

     

    I had a long running dimension processing (process add) for a large dimension (> 7 mil records) that caused the SSAS to hang in prod server. (the process runs atleast 4 times a day without issues and used to compelte in 5 min. But this was running for more than an hour),  I issued a cancel XMLA request and the client was disconnected but the msmsdrv CPU time did not come down and when I ran discover sessions, I could see the command in the results. At last I had to restart the msolap service to cancel it.

     

    For queries though, the cancel works without any issues.
    Tuesday, April 29, 2008 2:17 PM
  • Have tested against queries. Works fine. As of now I haven't spotted any issues.

     

    Thanks to all of you.

     

    Monday, May 5, 2008 4:24 PM