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.
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.
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.
Use an existing attribute keyUpdate 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 tableInsert 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:
- 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.
Ignore the errorIf 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.
This behavior is by design.