Select the product you need help with

Error messages when you try to process a database or a cube in SQL Server 2005 Analysis Services: "The attribute key cannot be found" and "The record was skipped because the attribute key was not found"

Article ID: 922673 - View products that this article applies to.

SYMPTOMS

In Microsoft SQL Server 2005 Analysis Services, you try to process a database or a cube by using SQL Server Business Intelligence Development Studio or SQL Server Management Studio. However, the process operation fails, and you receive the following error messages:

Error message 1

Errors in the OLAP storage engine: The attribute key cannot be found: Table: TableName, Column: ColumnName1, Value: Value1. Table: TableName, Column: ColumnName2, Value: Value2.

Error message 2

Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: generated attribute X of Dimension: DimensionName from Database: DatabaseName, Cube: CubeName, Measure Group: MeasureGroupName, Partition: PartitionName, Record: RecordNumber.

Back to the top | Give Feedback

CAUSE

This issue occurs because a fact table for a cube has one or more records that contain an attribute key, and this attribute key does not exist in the corresponding dimension table. This behavior may occur when you have not processed the corresponding dimension before you process the cube or when the underlying tables actually have mismatched data. If the "Value:" field in the message has no number after it, the fact table must contain null data.

Back to the top | Give Feedback

RESOLUTION

To resolve this issue, you must verify that your data source points to the following locations:

The correct underlying data source instance, such as an instance of SQL Server 2005
The correct database.

Then, correct the underlying records that contain the problematic attribute key. To do this, use one of the following methods.

Use an existing attribute key

Update the records to use an existing attribute key by running a statement resembles the following:

Update <TableName> set <KeyName>=<ExistingKeyValue> where <KeyName>=<BadKeyValue> or <KeyName> IS NULL

Match the key values in the fact table

Insert additional rows into the dimension table to match the key values in the fact table. If null values exist, use one of the following methods:

Replace the null values with actual values.
Configure the dimension or dimensions to have an unknown member by setting the UnknownMember and UnknownMemberName properties. You can make the unknown member either visible or hidden depending on your needs. For more information about how to define the unknown member, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms170707.aspx
(http://msdn2.microsoft.com/en-us/library/ms170707.aspx)
Use all the following settings in the Change Settings dialog box:
- Set the KeyErrorAction property to ConvertToUnknown.
- Set the NullKeyNotAllowed property to IgnoreError or ReportAndContinue.
- Set the NullKeyConvertedtoUnknown property to IgnoreError or ReportAndContinue.
- Click Ignore errors count.
You can set these settings instance-wide, or you can use a custom configuration for each dimension.

Ignore the error

If you want to process the database or the cube without correcting the data, you can set the error configuration for the process operation to ignore the error. You should only do this as a temporary workaround when you fix the underlying data. Otherwise, you may receive unexpected results from your multidimensional expressions (MDX) queries. To ignore the errors, follow these steps:

In the Process Database – DatabaseName dialog box or the Process Cube – CubeName dialog box, click Change Settings.
In the Change Settings dialog box, click the Dimension key errors tab.
Click Use custom error configuration.
In the Key not found list, change the default value from Report and continue to Ignore error.
Click Ignore errors count.
Click OK to close the Change Settings dialog box.
Click OK to process the database or the cube.

Additionally, you can set the error configuration for the cube or the partition to ignore the error. For more information, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms180058.aspx

(http://msdn2.microsoft.com/en-us/library/ms180058.aspx)

Back to the top | Give Feedback