I’m currently having an interesting email conversation about the lack of a query governor in Analysis Services and how useful it would be, and Darren Gosbell mentioned a new connection string property I wasn’t aware of: timeout, which allows you to specify a timeout in seconds for your queries. To test this you’ll need to open a tool which supports AS connection string properties (SQLMS doesn’t) like MDX Sample app, and simply put
timeout=1
in the connection string. This means that any query which takes longer than one second to execute will fail in one second. How could I not know about something as useful as this?
UPDATE: I’ve just heard from Mosha that apparently all XMLA command properties can be used in the connection string.
OMG! That IS useful! However, like the XMLA "Cancel" command, I think it will suffer from the fact that apparently not all sub-routines in AS are built to handle the cancelling of a query – hence it will not always terminate immediately on the server. It\’s still a great tip, though. 🙂
Just to clarify – Timeout is not the new connection string property. It existed since AS2000 SP4. Also, since AS2005 uses XMLA as underlying protocol, _ALL_ XMLA command properties are available as connection string properties both in OLEDB and ADOMD.NET.
Mosha (http://www.mosha.com/msolap)
Also, its in milliseconds, not seconds.
https://msdn.microsoft.com/en-gb/library/dn140245.aspx
The docs are wrong here. I’ve just tested it and it’s definitely in seconds.