SSAS cube error – warning – A duplicate attribute key has been found when processing: Table:, Column:, Value: The attribute is

Yesterday we suddenly started getting errors while processing SSAS cube. The ETL for cube processing failed with various errors

<Error ErrorCode=”3239837702″ Description=”Server: The current operation was cancelled because another operation in the transaction failed.” Source=”Microsoft SQL Server 2012 Analysis Services” HelpFile=”” /><Error ErrorCode=”3238002695″ Description=”Internal error: The operation terminated unsuccessfully.” Source=”Microsoft SQL Server 2012 Analysis Services” HelpFile=”” />

And with WARNING:

A duplicate attribute key has been found when processing: Table:, Column:, Value: The attribute is.

We focused on the above warning.

On investigation we found out that the source system started adding invisible line feed [LF] or char(10) data. These are invisible character.

Query to detect them: select * from DimEntityTable1 where [Feild1] like ‘%’+char(9)+’%’ or [Feild1] like ‘%’+char(10)+’%’ or [Feild1] like ‘%’+char(13)+’%’

We removed it from both Dim and Fact tables.

Viola the cube processed successfully!

SQL to update:

update DimEntityTable1
set Feild1 = REPLACE(Feild1,CHAR(10),”)
where [Feild1] like ‘%’+char(10)+’%’

update FactTable1
set Feild1 = REPLACE(Feild1,CHAR(10),”)
where [Feild1] like ‘%’+char(10)+’%’

Having said this the blog by Hilmar Buchta in Part -I and Part – II is ultimate:

Reason 1 (likely): The most likely reason for that error is that you are having NULL values in your attribute key column.If you simply created the attribute by dragging it from the source view, BIDS only sets the key column (name and value column default to the key column in this case), so for example if you have a column ‘Product Group’ in your source table and drag it to your dimension, the product group (Text field) will automatically become the key for this attribute. The attribute is listed in the error message (in the example above it is ‘Product Group’).

Solution: Try avoiding those NULL values in your data source (for example by using a DSV query and the T-SQL coalesce-function). When your source data is a data warehouse it’s also a good practice to avoid null values as they complicate the queries to the data warehouse.

Reason 2 (likely): You defined an attribute relationship between two attributes of the dimension but the data in your source tables violates the relationship. The error message gives you the name of the conflicting attribute (text part ‘The attribute is…’). The attributes has a relationship to another attribute but for the value stated in the error message (‘Value: …’) there are at least two different values in the attribute that the relationship refers to. If you have BIDS Helper installed, you can also see the error details and all violating references when using the ‘Dimension Health Check’ function.

Solution: You may solve the error by making the key of the attribute unique. For example:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘DimDate_x0024_’, Column: ‘Month’, Value: ‘April’. The attribute is ‘Month’.
In this example, the Month attribute violates an attribute relationship (maybe Month->Year) for the month April meaning that April appears for more than one year. By adding the year to the key of the month attribute you would make the relationsship unique again.

Reason 3 (not that likely): You have an attribute with separate key and name source fields. When you check the data, you see that keys are appearing more than once with different entries in their name columns (note that it’s not a problem if the key appears more than once if only the name column is the same). In this case you will usually also see the key value in the error message, for example:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_Product2’, Column: ‘ProductCode’, Value: ‘1’. The attribute is ‘Product Name’.
This means that the attribute ‘Product Name’ uses the source column ‘ProductCode’ as the key and for the product code 1 there is more than one name.

Solution: Use a unique key column (unique with respect to the name column)

Reason 4 (likely): This is a variation of reason 1 from my previous post. Reason 1 mentioned a NULL value in the key column being converted to a blank string which might conflict with another row that really has a blank string value. The same situation happens (as one of the comments mentioned) with a NULL value being converted to 0 for a numeric column.

Solution (identical as in reason 1): Try avoiding those NULL values in your data source (for example by using a DSV query and the T-SQL coalesce-function). When your source data is a data warehouse it’s also a good practice to avoid null values as they complicate the queries to the data warehouse.

Reason 5 (not very likely): The collation of the SQL Server relational database is “more detailed” than the collation on the SSAS side for an attribute’s key column. For example, SQL Server uses a case sensitive collation (like SQL_Latin1_General_CP1250_CS_AS) while SSAS uses a case in-sensitive collation. This causes SQL Server to treat a and A as two different values, while SSAS treats them as the same value (giving the duplicate key error). The same thing happens with an accent sensitive collation.

Solution: If you can, use the same collation type on both sides. If not, you could access the database table using a view that changes the collation or change the collation for the SSAS key column(s).

Reason 6 (not very likely): Your using the translation feature of the Enterprise Edition and for a certain attribute key you’re getting more than one translation.

Solution: Make sure that the translations are consistent (from the view-point of the attribute key)

If you want to suppress this warning/error then at Dimension level you can change the ErrorConfiguration from default to Custom and set the KeyDuplicate property to IgnoreError

ErrorConfiguration –> KeyDuplicate –> IgnoreError

For further details you can go to blog-1 or blog-2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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