FIX: Timeout Error Occurs with Large Dimensions in Dimension Editor or Wizard (281117)

BUG #: 11122 (plato 7.x)
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

When you complete the design of a large dimension in Dimension Wizard while the Count level members automatically option is selected, you may receive the following error message:

Unable to count the members of the 'XYZ'level. Unable to open the record set. Error: Timeout expired.

The error may also occur in Dimension Editor.

RESOLUTION

Microsoft SQL Server 2000 (Analysis Services)

To resolve this problem, obtain the latest service pack for the Microsoft SQL Server 2000 (the Analysis Services Components - Sql2kasp1.exe). For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Microsoft SQL Server 2000 Analysis Services

This problem was first corrected in the Analysis Services Components of Microsoft SQL Server 2000 Service Pack 1.
Note The new timeout is three minutes, and after three minutes, the timeout reoccurs.

MORE INFORMATION

With the Member Count property in Dimension Editor, you can enter the Member Count manually, but if you click the button in the property field, or if you click Count Dimension Members on the Tools menu, you are prompted as to whether you want the members counted automatically. This counting process can take a long time. If you click Yes in response to this prompt, you may receive the error message that is described in the "Symptoms" section, and you must manually type a value.

Note Make sure that you type an accurate count for these levels because the aggregation design relies heavily on these counts. To count the members, you can use a tool such as SQL Query Analyzer and query for the count of the members.

You can use syntax similar to the following SQL query to return the number of members for a specific dimension level:

SELECT COUNT(DISTINCT <FIELD_NAME>) FROM <TABLE_NAME>