Microsoft KB Archive/836055

= Failures may occur during cube processing if you select a member key column that is not correct =

Article ID: 836055

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server 2000 Analysis Services

-



Bug #: 14260 (Plato7x)



INTRODUCTION
When you manually optimize cube schemas to eliminate joins between dimension tables and fact tables, you must make sure that you select the correct columns for the member key column in the dimension. If you select a column that is not correct, it may cause results that are not correct, processing errors, or service shutdowns when the cube is processed.



MORE INFORMATION
In OLAP applications, such as Microsoft SQL Server 2000 Analysis Services, cube processing performance is frequently an area of intense focus for optimization. In many situations, Analysis Services processing performance can be significantly improved if you eliminate joins between the dimension tables and the fact tables for a cube. Cube Editor provides an automated way to eliminate joins. To eliminate joins, click Optimize Schema on the Tools menu. However, Cube Editor may not always eliminate all the joins that database administrators identify as unnecessary for cube processing.

Analysis Services database administrators can manually optimize the schema for a cube in Cube Editor. To do this, the database administrator must open the cube in Cube Editor, and then change the Member Key Column property to the lowest level of a shared dimension in the cube. If the Member Key Column property points to a column in the fact table for the cube, instead of a column in the dimension table, the join between the dimension table and the fact table can be eliminated when the cube is processed. For a join to be optimized by using this method, the following conditions must be true:
 * The dimension must be a shared dimension.
 * The member key column for the lowest level of the dimension must contain the keys that relate the fact table to the dimension table.
 * The key must be the only key that is required to relate the fact table to the dimension table.
 * The keys in the Member Key Column property for the lowest level of the dimension must be unique.
 * The lowest level of the dimension must be represented in the cube.

When a database administrator manually optimizes the schema for a cube, no automated error checking can occur. Therefore, the database administrator must select the correct column from the fact table when the database administrator sets the Member Key Column property for the dimension. If the database administrator does not select the correct column, processing failures may occur or values that are not correct may be returned. Processing failures are more likely to occur when the optimized dimension is also used as a slicer on a partition in the cube.

The following processing errors may occur if the database administrator does not select the correct column:

Processing Database 'FoodMart 2000' failed. No changes have been made to the database.

Processing Cube 'Sales' failed. No changes have been made to the database.

Processing Partition 'Sales' failed. No changes have been made to the database.

Analysis Server Error: Connection to the server is lost;

Could not unlock object (Connection to the server is lost);

Connection to the server is lost;

Additional query words: OLAP

Keywords: kbinfo KB836055

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.