"Duplicate attribute key" error when attribute is not a key

Question

I am getting the following error when processing a Dimension:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Orders', Column: 'Project', Value: 'client service stuff'. The attribute is 'Project'.

'Project' is an attribute of the 'Orders' dimension, but not a key. Nowhere did I indicate that the Project column is a key! I should be able to have as many duplicates as necessary, just like a first name field.

I'm new at Analysis Services project and really need to get past the fact that SSAS is constantly complaining about duplicate values when it should be perfectly OK to have duplicate values. I'm sure this must be something simple that I'm overlooking.

Edit: I realize that it is possible to set KeyDuplicate = ReportAndContinue/ReportAndStop and it is also possible to set KeyColumns and NameColumns. But this multistep process seems very cumbersome for what would seem should be a very normal operation, like adding Address1, Address2, Address3, Firstname, Zipcode, and other fields that normally are duplicated. I can't believe this cumbersome process need to be applied to all such fields?

Thanks in advance.

Answer 1

This is usually a result of having both blanks and NULLs in the source table/view.

Essentially, SSAS does this for every attribute SELECT DISTINCT COALESCE(attr,'') FROM SOURCE

Analysis services by default converts NULLs to blanks, resulting in duplicate value blanks in the resulting feed - hence the error.

I agree this sucks and is a major pain for new players.

Solution : Remove all nulls from the data source, for example by using ISNULL / COALESCE everywhere, or filtering out rows containing null using where clause, or running update statement to replace all nulls with values before processing the cube, etc.

Answer 2

Right click the attribute and select "Properties". Find "KeyColumn" which is located under the "Source" category within the Properties Window. Edit the "KeyColumn" property, it will display a user friendly window.

Remove the attribute from the right (Key Columns) side of the window and replace it with the actual id column from the left (Available Columns) side.

Then edit the "NameColumn" property, the same window will appear. Move the attribute column (the actual data you want to display) from the left side to the right.

Tested in VS 2010 Shell SSDT.

Answer 3

I had the same issue, and there was no blank or NULL values in the attribute. After some analysis, I found that some strings had line break character on the end. So, if 2 values of the attribute are nearly the same, but one of them has line break character at the end, and the other doesn't, then SSAS raises “Duplicate attribute key” error.
It can be fixed by removing line break character from the attribute.
I created calculated column with following definition:

REPLACE(REPLACE(ISNULL([AttributeColumn], ''), CHAR(13), ''), CHAR(10), '')

I used this calculated column in the cube, and the error disappeared.

Answer 4

Just had this happen to me today and scratched my head for a while as none of the solutions here worked. Finally solved it and thought I'd add my solution for anyone else googling this error and arriving here just as I did.

In my case it was not NULL and blank strings as I had the [NullProcessing] value already set to "UnknownMember". Rather it was the [Trimming] value, in my case it was set to "Right".

While I know how I solved(?) it I am not 100% as to why, but I assume when SQL Server does it's SELECT DISTINCT(col) FROM source and the [Trimming] value is set as such, Analysis server later removes among other things tab chars from the end (which RTRIM in SQL Server for example does not) and ends up with duplicates.

So setting [Trimming] to "None" might solve it, since the tabs was data I did not need (my data is parsed/read/entered from external sources) I simply replaced tabs in the column and after that processing of the cube is fine again.

Answer 5

While my other solution on this page works (and depending on situations might be more ideal), this is an alternate solution:

Here is a mock up of part of my error:

Column: 'attribute1_name', Value: 'Search String'

I did a quick search for:

SELECT dim_id,
       dim_name,
       dim_attribute1.id,
       dim_attribute1.name,
       dim_attribute2.id,
       dim_attribute2.name
  FROM dim_table
    INNER JOIN dim_attribute1 ON dim.attribute1_id = dim_attribute1.id
    INNER JOIN dim_attribute2 ON dim.attribute2_id = dim_attribute2.id
 WHERE UPPER(dim_attribute1.name) = UPPER('Search String')

It turns out that there were two different entries for dim_attribute1.name which matched this:

1. Search String
2. search string

The first solution split them without issue, so it is a working solution (plus the performance bonus). However an alternative (if one wants to keep the text values as keys) is to change the Collation:

Key Columns → Column Name → Source → Collation

To include 'case sensitive'.

Other similar issues can be white space characters and other easy to not spot subtle changes in the text.

Answer 6

I had a similar issue today (same error message), for the sake of anyone else getting here with the same problem I put some notes on my wiki: http://www.david-halliday.co.uk/wiki/doku.php?id=databases:oracle&#select_dates_for_ssas_include_hierarchy

My case was SQL (simplified and reworded to defend the innocent):

SELECT dim_id,
       dim_name,
       dim_attribute1.name,
       dim_attribute2.name
  FROM dim_table
    INNER JOIN dim_attribute1 ON dim.attribute1_id = dim_attribute1.id
    INNER JOIN dim_attribute2 ON dim.attribute2_id = dim_attribute2.id

The strange thing was the error was happening for some cases of dim_attribute1_name but not dim_attribute2_name. However this particular case the attribute was exactly the same. In the end the solution was to change the SQL to:

SELECT dim_id,
       dim_name,
       dim_attribute1.id,
       dim_attribute1.name,
       dim_attribute2.id,
       dim_attribute2.name
  FROM dim_table
    INNER JOIN dim_attribute1 ON dim.attribute1_id = dim_attribute1.id
    INNER JOIN dim_attribute2 ON dim.attribute2_id = dim_attribute2.id

Then use in the dimension (hiding the IDs in the list) the id value for the key of the attribute and the name for the name of the attribute. I haven't seen this before but for some reason it happened here. This solution I believe is better than setting the cube to process ignoring duplicate key errors.

I presume that if one is building a dimension joining tables this will give better performance/reliability. But don't quote me on that.

Answer 7

I had the same problem and I found a workaround for it.

Right Click in "Cube" => "Process" => "Change Settings" => "Dimension Key Errors"

Active "User Custom Error Configuration"

Set "Ignore Errors" for this four drop down list "Key Not Found" "Duplicated Key" "Null key converted to unknown" "Null key not allowed"

The problem with keys will be ignored.

Answer 8

I got the problem after I had been playing around with adding an ID into the key column of an attribute. I had since removed the key but found that the select statement during processing was still referring to the ID, making the attribute non unique. I couldn't find a way of resolving this via the attribute properties, so I deleted the whole Dimension and recreated it. This fixed the issue.

Answer 9

Please read this blog: a duplicate attribute key has been found.... Look at the long explanation for reason 1. It will explain why exactly this is happening.

Thanks guys

Ned

Answer 10

None of the above solved for me. What worked was something similar to what Eric W. suggested.

I had to set up multiple Key Columns for my attributes. For example, the attribute "City" needs the Key Columns "Country", "State", and "City".

More info here: https://www.mssqltips.com/sqlservertip/3271/sql-server-analysis-server-ssas-keycolumn-vs-namecolumn-vs-valuecolumn/

Answer 11

In case it helps other quasi-newbies like me, I’ll outline a solution that I finally figured out after struggling with the “duplicate attribute key” error message while trying to deploy a Date dimension spanning multiple years. The error message indicated, for example, that I had duplicate attribute keys in my CalendarQuarter attribute. That initially confused me because every complete year has four quarters (i.e. 1, 2, 3 & 4) so of course I had duplicates. It finally dawned on me that that was the problem--in other words (and contrary to the title of this thread) the attribute WAS the key. I solved it by adding a “CalendarQuarterKey” named calculation column to my DSV’s Date table to yield unique keys for my CalendarQuarter attribute, e.g. “20171” instead of just “1” for 2017 Q1, “20172” instead of just “2” for 2017 Q2, etc. Ditto with my CalendarMonth attribute: every complete year has twelve months (i.e. 1, 2, 3...,11, 12) so of course I had duplicates there as well. Same solution: I added a “CalendarMonthKey” named calculation column to my DSV’s Date table to yield unique keys for the CalendarMonth attribute, e.g. “201701” instead of just “1” for January 2017, “201702” instead of just “2” for February 2017, etc. Then, I used my new “CalendarQuarterKey” & “CalendarMonthKey” columns as the KeyColumn for my CalendarQuarter and CalendarMonth attributes respectively. This may not be the preferred solution, but it worked for me and I can finally get back to building my cube.

Answer 12

I solved by specifying the COLLATION on my views on the relational database as follow.

COALESCE([Descrição da Transação],'') COLLATE Latin1_General_CI_AI

Answer 13

If your data contains both NULLs and '' SSAS give out duplicate attribute key, because it considers NULLs to be ''. You don't have to touch your data to fix this. You can go to your data source view and add a named calculation with expression COALESCE(mycolumn, ''), then use that in your dimension instead of the original column. This will fix the problem at the data source view level and the dimension will process fine.

Answer 14

Lemme give you a workaround if you still want to go ahead with deployment & cube browsing . Under 'process cube' window, change dimension key error settings to custom . You would be able to seamlessly deploy & browse the cube . trade-off here is that you might not get the results which you expected.

Answer 15

some time that need composite key in keyColumns to resolve the duplicate attribute key

Answer 16

I've run into this error many times for various reasons, but recently encountered a rather obscure cause: the presence of the beta ß character in a text column. Despite the fact that the thousands of unique words in the column used a hodgepodge of every obscure ASCII code under the sun, SSAS choked only while processing column values that included the ß symbol. Nulls, duplicates, trimming and the like were all systematically ruled out. This is in all likelihood related to unfathomable and unsolved issue discussed in the MSDN thread SSAS 2012 duplicate key error with 'ss' and 'ß', in which SSAS interpreted ß values as 'ss' for some inscrutable reason, even when the collation settings were correct. In my case, setting the Collation in the SSAS column properties to match the source column's collation of SQL_Latin1_General_CP1_CS_AS on the relational side did not fix this; I had to also change the collation for the entire server. This workaround might be painful in certain environments where other columns depend on different collations, but it skirted this issue in my case and allowed me to process the dimension without a hitch. I hope this helps the next person to stumble over the same "gotcha."