How to avoid Long running MDX Requests

I wanted to write a small post about ‘Timeout’, which really helped me practically to manage long running MDX requests.

Example of connection string with Timeout

Provider=MSOLAP.3; Data Source= MySSASServerName; Initial Catalog= MySSASDBName; User ID= windowsusername; Password= windowsuserpwd;Timeout=120;

In the above example, requested MDX would be allowed to run in 2 minutes. After 2 minutes request will be cancelled.

This is very important feature and every developer should consider using. Usually OLAP clients provide the flexibility to design the reports by authorized users and/or select the multiple dimension members on rows or columns or pages. So based on selection made by end user, sometimes prepared MDX could be big or complex and requires lot of resources & time to execute the same. So when it is a multi user environment (especially concurrency is high) and end user has got flexibility to design the reports & run the same, then there will be ‘Competency for the resources’ and one request could bring down the server, which will drop everyone’s requests, which nobody wants to happen.

Practically I encountered Production server (power full) going down and cancelling all the request because of Single MDX request. It was horrible situation to know that Server went down.

In order to not to bring down the Production server, I used ‘Timeout’ property in connection string and given flexibility to change this number by Department and by U. Since I’ve given flexibility (Managing Timeout time in a separate table) to specify in the connection string, Reporting Administrator can manage control the Timeout time depending the importance of User or Department usage.

Example: Finance department is working on ‘Month end closure’ or they got to present the Financial statements to their BOD, so obviously their work gets the highest priority. Similarly CEO is performing What-If Analysis and that is important step for defining ‘Strategy’ for entire company. In these type of situations, you do not want to drop their MDX request unless it is too long request to execute

The above mentioned situation is not unique. I would think it is very common requirement and you never want single MDX request bringing the server down. So it is always safer to use Timeout property and manage the Server uptime well.

Clearly there is no hard number of Timeout time that I can recommend because depending the user base, concurrency, complexity of the reports, flexibility that is given to the end user, one should determine how much this number (Time) should be.

Finally, do not miss to handle the error in your OLAP client when Timeout drops the request. Always it is good practice to display the message to end user about why the requesst is dropped. It is also very important to integrate some mechanism to communicate Reporting Administrator about how many requests were dropped. In my case, I generate an email to Reporting Administrator with user & report details when a request is cancelled.

Hope this helps!!