The most common error while processing cube and which everyone face when they are newbie to cubes is "The attribute key cannot be found". If the person is newbie to SSAS then probably he/she will find it hard to get into the exact root cause of error so today I am going to explain this error message and then the resolution for the same. I have created the following example of error message to explain the error in more detailed way.
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_FactSales', Column: 'ProductID', Value: '1111'. The attribute is 'Product ID'.
The above error explains that the fact table named "FactSales" contains column ProductID with value "1111" but the same ProductID is not present in your dimension table. There is a primary key - foreign key relationship exist between the ProductID column of dimension table and fact table named "FactSales" and cube is unable to find ProductID with value 1111 in the dimension table. So the first step one should do is to check either your dimension and fact table contains the value mentioned in the error message ( Value: '1111' in the above example) and the most possible chance is your fact table contains the value (ProductID=1111) but the dimension table for the same does not contain the value (1111). If this is the case then your dimension table is not populated properly so try to bring the ProductID = 1111 in your dimension table. If the ProductID with value 1111 is present in both dimension as well as in fact table then your cube dimension is not updated yet so you can do that by doing "ProcessUpdate" on the corresponding dimension first and then try to process the measure group or partitions.
If you are doing daily processing of your cube (using sql job) then always do ProcessUpdate of your dimensions first and then process your measure groups/partitions.
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_FactSales', Column: 'ProductID', Value: '1111'. The attribute is 'Product ID'.
The above error explains that the fact table named "FactSales" contains column ProductID with value "1111" but the same ProductID is not present in your dimension table. There is a primary key - foreign key relationship exist between the ProductID column of dimension table and fact table named "FactSales" and cube is unable to find ProductID with value 1111 in the dimension table. So the first step one should do is to check either your dimension and fact table contains the value mentioned in the error message ( Value: '1111' in the above example) and the most possible chance is your fact table contains the value (ProductID=1111) but the dimension table for the same does not contain the value (1111). If this is the case then your dimension table is not populated properly so try to bring the ProductID = 1111 in your dimension table. If the ProductID with value 1111 is present in both dimension as well as in fact table then your cube dimension is not updated yet so you can do that by doing "ProcessUpdate" on the corresponding dimension first and then try to process the measure group or partitions.
If you are doing daily processing of your cube (using sql job) then always do ProcessUpdate of your dimensions first and then process your measure groups/partitions.
GreaTTTTTTTTTTTT. really this is what needs to be explained and the way it should be explained to new i saw many posts for solving this error.. more technicalities were mentioned also the cause but in technical way.. You explained the stuff really can be understood by any newbie or any new user. really i got the cause of error, but still i need to ask one question what if Productid 111 is not present in dimension table and still we want the cube to be worked like if its not present then not present. i need to process my cube and any empty value can be shown, but i cannot manually insert into dimension tables the values which are not present.
ReplyDeleteThanks
You ROCK!!! Thanks mate, big help!
ReplyDeleteHi,
ReplyDeleteThanks your post is very helpful. I am getting the following error
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dim_Organisation', Column: 'Organisation_x0020_Key', Value: '34700'. The attribute is 'Organisation Key'
I have checked all dimension tables and fact tables and also underlying database, and there is no organisation key 34700. I am unable to understand why it is generating this error. Any Idea?
I am a newbie in cubes :D Thanks for your help man!
ReplyDeleteBig Help..Thank you. Very clear insight..
ReplyDelete