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

CAUSE

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:

ALTER CUBE [Sales] DROP DIMENSION MEMBER [Customers].[All Customers].[USA].[TX].[Dallas].[Stephanie Conroy]

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

SELECT [Measures].members on 0
FROM [Sales]
WHERE [Customers].[All Customers].[USA].[TX].[Dallas].[Stephanie Conroy]

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.

   Date         Time   Version            Size    File name
   --------------------------------------------------------------
   13-Sep-2003  22:50  8.0.806.0         217,668  Msmdcb80.dll     
   13-Sep-2003  22:50  8.0.0.843       4,055,616  Msmddo80.dll     
   27-Sep-2003  03:02  8.0.864.0       1,032,768  Msmdgd80.dll     
   13-Sep-2003  22:50  8.0.823.0         172,608  Msmdpump.dll     
   27-Sep-2003  03:02  8.0.0.864       9,638,468  Msmdsgn80.dll    
   27-Sep-2003  03:01  8.0.864.0       1,835,588  Msmdsrv.exe      
   13-Sep-2003  22:50  8.0.860.0       2,077,264  Msolap80.dll     
   10-May-2003  08:05  8.0.820.0         213,069  Msolap80.rll