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 these procedures:

SQL Server

• Using SQL Server Management Studio (SSMS), connect to the database engine hosting the IT Analytics database
• In Object Explorer, right-click the server hostname and select Properties
• In the Server Properties window, select the Connections page
• Under the Remote server connections heading:
  • Confirm the Allow remote connections to this server checkbox is selected
  • Set the Remote query timeouts setting to 0 (unlimited), or increase the setting to 360000 (corresponding to the recommended value of the ExternalCommandTimeout setting on the SSAS server)
• Click the OK button
• In Object Explorer, right-click the database engine hostname and select Restart

SQL Server Analysis Services (SSAS)

• 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