Analysis Services 2008: Issue and Resolution #2

Here is another Analysis Services error message which I’ve seen crop up from time to time:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_mytablename’, Column: ‘ColumnName’, Value: ‘UNK’. The attribute is ‘ColumnName’.

There are a number of ways to get rid of this. I’m in favour of sorting out the source, but if the source does not belong to you then it can be difficult to take the action that you might like.

1. Sort out the source.

You may not be the ‘owner’ of the source. If you are, then there must be duplicates in the source somewhere, and this will require some digging around to discover the duplicates. This should be easy to discover using basic counts and grouping in SQL, and sorting the count in descending order so that the offending keys bubble to the top of the SSMS screen.

2. Get rid of NULLS.

If amending the NULLs isn’t an option for you, then perhaps you can circumvent this issue by using a VIEW in the DSV.

Note that SSAS will convert nulls to zeros. So, if your key contains NULLs, you will end up with these items converted to zeros. Thus, if you have more than one NULL, your column will contain lots of zeros; hence the duplicate attribute key error. The conversion to zeros might well be hiding NULLS in the source system, so it’s always good to have a look in the source column.

3. Analysis Services properties

In the properties, set errorprocessing to default or change keyduplicate setting to ‘ignore’. This might be a ‘quick fix’ but it isn’t ideal since it may well result in issues later on .

I hope that helps!

Add to Technorati Favorites

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