"Duplicate attribute key" error when attribute is not a key
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
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.