Cube processing timeout error prior to job completion

Products

• IT Analytics

Issue/Introduction

Cube processing jobs fail and display the following error in the IT Analytics Events Viewer:

Cube processing error. Possible causes include insufficient permissions to read Cubes Data Sources, external connection timeout on the Analysis Server or remote connections timeout on SQL databases. Error: OLE DB or ODBC error: Query timeout expired; HYT00. Errors in the OLAP storage engine: An error occurred while the dimensions, with the ID of '<dimension ID>', Name of '<dimension name>' was being processed.

The SQL Server Agent job log contains the following error:

<Error ErrorCode="3238395904" Description="OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00." Source="Microsoft SQL Server <version> Analysis Services" HelpFile="" />

Cause

Cube processing takes longer than the current query timeout values set on either the SQL Server database engine hosting the IT Analytics database or on the SQL Server Analysis Services (SSAS) server hosting the IT Analytics cubes.

Resolution

To increase query timeout values, follow this procedure:

• Using SQL Server Management Studio (SSMS), connect to the Analysis Services server hosting the IT Analytics cubes
• In Object Explorer, right-click the server hostname and select Properties
• In the Analysis Services Properties window, select the General page
• Select the Show Advanced (All) Properties checkbox
• Locate and edit the following settings:
  • ExternalCommandTimeout = 360000
  • ExternalConnectionTimeout = 360000
  • ServerTimeout = 360000
• Click the OK button
• In Object Explorer, right-click the Analysis Services hostname and select Restart

Also, Check the Timeout Value for the SQL Server to make sure that we don't hit it's Remote Query Timeout while extracting data:

• Open up SSMS and connect to the SQL Server hosting the CMDB database
• In Object Explorer, right-click the hostname and select Properties
• in the Server Properties window, select the Connections page
• Locate the "Remote query timeout (in seconds) and adjust as needed (3600 = one hour)
  • An hour or two should be ample time in most scenarios
  • It's not recommended to use 0 as a malformed query may run for a long time, using up system resources