How to increase ExternalCommandTimeout in SSAS

Friends,

When ever you re running complex queries at the time of cube process you may get unexpected errors like given below.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

or

OLE DB error: OLEDB or ODBC error: Query timeout expired; HYTOO

Let me tell you a case where I faced this problem. One of my team member has written a NAMED QUERY in SSAS DSV in which he is joining with more than 20 HUGE  tables and it returns 100 millions records with 70+ columns. This is pretty huge for a dimension table and hence we were getting this issue.

This types of issues can be eliminated by either of the ways given below.

First – Stupid !! Go and modify or TUNE the query.

Second – Increasing the ExternalCommandTimeout value so that it won’t timeout.

First thing you understood I guess and lets see the  steps involved in increasing the ExternalCommandTimeout value.

• Connect to SSAS from SSMS.
• Right click on the server in SSMS and select properties.

• Select Show Advanced Properties option.
• Increase the ExternalCommandTimeout  value as shown below. If you set it to 0(ZERO) then it will be treated as infinity.

Click on OK and try to execute the cube process again. Hope this works !!

Regards,
Roopesh Babu V