SQL Server – Analysis Services (SSAS) – “The Attribute Key cannot be found when processing Table…The attribute is”

Background

Using SQL Server Data Tools, added a new dimension to our data source.  Unfortunately, for this added dimension we have yet to actually define the database foreign key constraints and so within our “Data Source View” we manually hand-wired the relationships between the foreign key table (Fact table) and the primary key (Dimension table) .

DataSource-EditRelationship

Error

Everything went well with building the project.  But, ran into error with actual deployment.

Error Text


Internal error: The operation terminated unsuccessfully.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Errors in the OLAP storage engine: An error occurred while processing the 'FactSales' partition of the 'FactSales' measure group for the 'IISLogDW' cube from the FullData database.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Server: The current operation was cancelled because another operation in the transaction failed.

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_fact', Column: 'UserID' Value: '293379'. The attribute is 'UserID'.

Errors in the OLAP storage engine: The attribute key was converted to an unknown 
member because the attribute key was not found. Attribute User Custno of Dimension: 
DimCust from Database: Sales_FullData, Cube: IISLogDW, Measure Group: Fact Sales, 
Partition: FactSales, Record: 15520831.

Error Image:

AttributeKeyCannotbeFound

Fix

Fix – Cube Process

  • Select Cube
  • Right click on the cube
  • And, from the drop down menu, Select the “Process” menu
  • In the “Process Cube” screen, click on the “Change Settings …” button
  • In the “Change Settings” window, access the “Dimension Key Errors” Tab
  • The default choice is “Use default error configuration”, select the “Use custom error configuration
  • Within the “Use Custom error configuration” Tab, select the following options
    • Key error action :- “Convert to Unknown”
    • Processing error limit  \ Ignore errors count
    • Error log path :- file name for a valid on your machine
  • Click the OK button to exit the “Change Settings” window
  • In the Process Cube window, click the “Run” button

ProcessCube-ChangeSettings-DimensionKeyErrors-PostChanges

Upon re-run, the errors are logged as warnings, and cube rebuild completes.

ProgressProgress-ErrorsLoggedAsWarnings

The downside of this quick fix is that your changes will be lost once the current processing session is complete.

If you return to the “Process cube” by following the steps listed below:

  • In the Solution Explorer, Select Cube
  • Right Click on the selected Cube, and from the drop-down menu select the “Process…” button
  • Click on the “Change Settings” button
  • Access the “Dimension Key errors” tab
  • You will notice that the “Use default error configuration” chosen is chosen and even upon clicking on the “Use custom error configuration” all previously entered choices are lost

Use Default error configuration

ChangeSettings-DimensionKeyErrors-UseDefaultErrorCondition

Fix – Cube’s Error Handling Configuration

Change Cube’s error handling configuration

  • In the Solution Explorer, Select the Cube
  • Double-click on the selected cube
  • In the properties panel, transverse to the “ErrorConfiguration” property and you will notice that the current choice is “(default)
  • Within the”ErrorConfiguration” property choices, please choose “Custom

SolutionExplorer-Cube-ErrorConfiguration (before)

ErrorConfiguration – Default Selections

Here are the default selections.

Cube-ErrorConfiguration-DefaultChoices

 

ErrorConfiguration – Suggested Changes

Here are the suggested changes:

Property Value More …
KeyDuplicate ReportAndContinue
KeyErrorAction ConvertToUnknown
KeyErrorLimit -1 Continue irrespective of how many errors we encounter
KeyErrorLimitAction StopLogging
KeyErrorLogFile File name on Server to place and log errors
KeyNotFound IgnoreError
NullKeyConvertedToUnknown IgnoreError

 

Image

SolutionExplorer-Cube-ErrorConfiguration (after)

Conclusion

It seems that error suppression makes sense when one is dealing with a very large databases.

You want to capture the offending errors and you also want to complete processing and simply classify the “orphaned” records as “unknown” per this specific attribute / dimension.

References

References – General

  • 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”
    http://support.microsoft.com/kb/922673

References – Cube

References – Partition

References – Process

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s