"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.