SSAS Quick Reference: Attribute Key Cannot Be Found

Monday, February 2, 2009
SSAS Quick Reference: Attribute Key Cannot Be Found
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
Posted at 11:18 AM
12 comments:
 
Anonymous said...
And if it says it can't find the value "0" then it may actually be receiving a null in the fact table and converting it to zero before trying to find that value.

February 2, 2009 11:57 AM 
 
Todd McDermid said...
TYVM. I've updated the post.

February 2, 2009 4:56 PM 
 
Dibyant S Upadhyay said...
Hi Todd,
Nice post about ssas attribute key error,I was about to light in this topic since this is one of the most frequent error asked in msdn,But you have assembled better very best!
Very nice post.Congrats!!!!
Thanks
Dibyant S Upadhyay

February 11, 2009 6:29 PM 
 
sheela said...
This post has been removed by a blog administrator.
February 25, 2009 2:28 AM 
 
Jayanthi said...
This post has been removed by a blog administrator.
February 25, 2009 2:29 AM 
 
Anonymous said...
I got same problem, but nothing helped. Missing key exists in Dim table, all dimmnsions are fully processed before cube,there are no NULLS in Dim or Fact tables.
Every time I try to process I get different key missing, but always from same Dim table. This particular dimension is huge, 1M+ rows (Dates...) , the only solution i got - rebuild cube from 0 with all dimensions. Cube actually works if I replace that dimension with one with lesser data, so it seems MSAS can not correctly process large dimensions and will occasionaly return less data then processed,resulting in error.

August 19, 2009 4:47 AM 
 
Todd McDermid said...
I highly doubt that SSAS has any issue with processing larger dimensions - but that's always a possibility. The above list is (obviously) not an exhaustive account of the possible causes and remedies for this behaviour.
What I would recommend is that you first post your issue on the SSAS forums on MSDN (http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads/). If you don't get a good resolution, please post your issue to SQL Connect (https://connect.microsoft.com/SQLServer).
If you do resolve the issue, please contact me with the results so I can update this page!

August 19, 2009 5:44 PM 
 
Anonymous said...
I had same problem.After I fixed the missing field in database table I still got "missing attribute" error. To fix - in DataSource View - I edited tables to be a View with "select top (0) .." in the two joining tables. Then processed individual dimensions seperately, then processed the whole cube. Then removed "top (0)", and repeated individual processing followed by cube processing. Fixed.

October 22, 2009 6:35 AM 
 
Thales said...
Nice post!
Thanks a lot!

November 5, 2009 6:04 AM 
 
Anonymous said...
It may be memory usage that is causing the errors (not large dimensions as such):

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/268a4548-bbe7-40e0-b645-94e7e855e0aa

January 9, 2010 11:48 AM 
 
Simon Birch said...
Nice work Todd, I also get the same problem when SSAS data source table refers to a SQL Server view defined as SELECT TOP 10000 * FROM table.

Obviously I was doing this to speed the processing times during development. Eventually solved by introducing a table to store the top 10000 rows, and pointing SSAS at the table.

Cheers
Simon

April 6, 2010 11:14 PM 
 
gooma said...
Ad. #3. I just had the same problem. The solution was to set UnknownMember property in the dimension to 'Visible' and set null processing to UnknownMember in advanced properties of dimension usage (cube design).

July 23, 2010 5:25 AM