FIX: Delete of Dimension Member in Write-Enabled Dimension May Cause Service to Restart

When the Analysis Services service performs update operations on a write-enabled dimension, an error may occur and Analysis Services shuts down after a DROP MEMBER statement runs on the dimension. After the program shuts down, the service tries to restart and the same error occurs again. The service continues to experience the errors until the data folder for the server that is running Analysis Services is either deleted or is renamed.

The application event log will have an Event 117 error with MSSQLServerOLAPService as the source. The text of the event message indicates that the Analysis Services service experienced an unexpected fatal error, and that it tried to restart the server.

If SQL Server 2000 Analysis Services Service Pack 3 has been installed and automatic error reporting is enabled, there may also be an entry in the application event log that indicates that the error reporting process received a bucket ID of 40515735 as part of the error reporting response.

There may be one or more mini dump (mdmp) files generated in the Analysis Services data folder. If you open the mini dump file in WinDBG, or a similar debugging tool, setting the last stack to the error context (.ecxr) and looking at the call stack will show the following calls:



For this problem to occur, all the following conditions must be true:

• The cube that contains the write-enabled dimension uses multidimensional OLAP (MOLAP) storage.
• The cube contains a dimension with the ALL level disabled, and it has more than one member at the top level.
• The dimension member that is being dropped has no data for the default top level member of the dimension with the ALL level disabled.
• The dimension member that is being dropped has data for a non-default top level member of the dimension with the ALL level disabled.

Write-enabled dimensions allow end users to modify the contents of the dimension and to see the immediate affect on the cube. To reflect the changes, Analysis Services removes any aggregates that involve intermediate levels of the altered dimension. This is necessary because write-enabled dimensions allow the movement of leaf level dimension members from one parent to another and previously calculated aggregations may no longer be accurate. Periodically, Analysis Server checks the cubes on the server and determines if they must be re-aggregated as a result of an update operation on a write-enabled dimension. If so, it initiates a back-ground re-aggregation process for those cubes. The service restart that is described in the "Symptoms" section occurs during the re-aggregation process. It is a result of a problem processing the ALTER CUBE statement.

Analysis Services creates an aggregate at the LEAF and the ALL level of write-enabled dimensions. As a result, the DROP DIMENSION MEMBER functionality of the ALTER CUBE statement for write-enabled dimensions is restricted to only allow deletion of dimension members that do not have fact data associated with them in the cubes that contain the dimension. Therefore, when a DROP MEMBER statement is received, Analysis Services issues a query against the cubes that contain the dimension to verify that no data exists for the member that is being dropped. The query that is used to determine the existence of fact data selects the default member of all the dimensions in the cube, except the one that contains the member to be deleted. For the dimension that contains the member that is to be deleted, the query requests data by using the member name from the DROP DIMENSION MEMBER clause of the ALTER CUBE statement.

Here is an example:

When Analysis Services receives this command, it checks the Sales cube by using a query that is similar to the following:


Because the dimension members for other dimensions are not specified in the query, the Analysis Server engine uses the default member of those dimensions. In cases where the ALL level has been enabled, the ALL member is set as the default member for the dimension, unless it is specifically modified by the Analysis Services administrator. However, for dimensions where the ALL level has been disabled, the first member of the top level in the dimension is set as the default member. Because there can be more than one member in the top level of a dimension (see the Time dimension in the Foodmart 2000 database), the query that checks for the existence of fact data during the delete process may not accurately detect the existence of fact data.

If you use the ALTER CUBE statement example that is mentioned earlier in this article assume that the Time dimension for the Sales cube is implemented in the same manner as that in the Foodmart 2000 database. It contains the years 2000 through 2004 at the top level of the Time dimension, and the year 2000 is the default member. If the customer "Stephanie Conroy" has sales data for 2003, but none for the year 2000, the server that is running Analysis Services will not find any data for the default member of the Time dimension (2000), and it allows the deletion of "Stephanie Conroy" from the Customer dimension.

After the dimension member is deleted, the background thread detects that aggregations for the intermediate levels of the Customer dimension must be rebuilt. So, it begins reading the leaf level data for the cube and re-creating aggregates. For a cube with MOLAP storage, the leaf level data is stored in a multi-dimensional format on disk and the re-aggregation process reads the data from the multi-dimensional store. However, leaf level data exists in the multi-dimension store for the deleted Customer dimension member '"Stephanie Conroy." When Analysis Services locates data for "Stephanie Conroy", it cannot locate that member in the Customer dimension. Therefore, it returns a fatal error, that results in the service restart. When the service restarts, it detects that the intermediate aggregations for the Customer dimension in the Sales cube are missing, and it starts the re-aggregation process again. However, because the dimension member is still missing for the leaf level data in the multi-dimensional store, the service will again experience the fatal error. The only way to prevent the error is to delete the data files for the affected cubes and to reprocess them.

The hotfix listed in the "Resolution" section changes the data detection process to make sure that the restriction for the DROP DIMENSION MEMBER clause will detect situations where data exists for non-default members of dimensions without an ALL level. This prevents the situation where data exists in the multi-dimensional store for a deleted dimension member.

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Hotfix information

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

SQL Server 2000 Analysis Services 64-bit

For additional information about a hotfix build designed for a server that is running SQL Server 2000 Analysis Services 64-bit or to find out if a fix is scheduled to be included in SQL Server 2000 Analysis Services 64-bit Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.