SSAS Quick Reference: Attribute Key Cannot Be Found

Reposted from Todd McDermid's blog with the author's permission. 

This is a reference post for me, mostly - I keep forgetting this stuff about SSAS... particularly cause #2.

Error

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: [table name], Column: [column name], Value: [value]. The attribute is [attribute name].

Explanation

This means SSAS could not find a record in the dimension table [table name] where column [column name] contained value [value].  Essentially, a "SELECT COUNT(*) FROM [table name] WHERE [column name] = [value]" returned zero.

Cause(s) and Solution(s)

#1 - Errors in ETL
This doesn't happen to me - but is apparently most the common cause: You've loaded facts into the fact table, but haven't loaded the requisite dimension members into the dimension table.  (This may be an ETL design error, or a true data error.)  This can ONLY happen if you're not using surrogate keys to map your facts to your dimensions.  (Which is why it can't happen to me.  If you're using surrogate keys, one of your steps in processing the fact data is to look up the surrogate keys - where you'll end up "not finding" one, and have to deal with it there, in your ETL, instead of in cube processing.)
==> Load your dimensions properly.  If you can rearchitect your warehouse to use surrogate keys for dimension members - DO IT.

#2 - Bad Processing Order
This is (apparently) fairly typical.  You may have instructed SSAS to process a Fact table (measure group) before the dimensions it references have been processed.  For example, you've run an ETL process to update your RDBMS warehouse.  The ETL added some new dimension members, and new facts which refer to those new dimension members.  If you process the measure group first, it will try to look up the new dimension keys in the "old" dimension.
==> Process the dimension(s), then the cube.

#3 - NULLs in Your Fact or Dimension Data
Apparently also common due to (IMO) bad warehousing design. 
If you have a NULL value in one of your Fact table keys, SSAS may report that it can not locate a default valued key in the dimension table.  To restate, if an integer type foreign key in the fact table is valued as NULL, SSAS may report it can't locate "0" in the Dimension table. 
If you have a NULL value in one of your Dimension table attributes, SSAS may be unable to find an attribute key in a "snowflake" type warehouse due to rows being excluded because they don't join up to the NULL.  The worst part is that the NULL valued attribute may not be related (in an obvious manner) to the attribute it reports as being unable to be found...
==> Check for NULL values in your Fact tables and Dimension tables.  If you can rearchitect your data warehouse to eliminate all NULL values - DO IT.  (see Kimball Design Tip #43.)

#4 - Data Collation Inconsistencies
The collation of the dimensional database (or column) could be incorrect.  Your RDBMS warehouse could be set to case-insensitivity, but the AS database is set to be case sensitive.  Or it could be accent sensitivity that doesn't match.  Be sure to check individual column collations in the RDBMS too... copying or restoring databases from servers with different collations can cause problems.
==> Change the collation of the RDBMS (server, database, and/or columns) and SSAS to match (case sentitivity and accent sensitivity).

#5 - Materialized Reference Dimensions Processed Incorrectly
If you're using a reference dimension (sometimes called a bridge dimension) in SSAS like the AdventureWorks demo does with the Reseller dimension in order to link it to the Geography dimension, be aware that it doesn't get resolved in Dimension Processing like you might think it should.  Materialized Reference Dimensions are processed in Partition Processing.
For excellent details, see Alberto Ferrari's blog post.
=> Process your Partitions

Todd McDermid is BSc, MCSD.Net, MCTS (SQL 08 BI), MVP with 20 years experience in Software Development and Databases. Currently part of a very lean (and getting leaner) IT department in a building products distributor. Coordinator of the Kimball Method SSIS Slowly Changing Dimension Component project on CodePlex. Moderator on the Microsoft SSIS Forums. His blog can be found at http://toddmcdermid.blogspot.com/ .