SSAS - Duplicate attribute key found when processing

I have been seeing this error time and time again when processing cubes in Analysis Services and thought it would be a good idea to blog about it. When processing Analysis Services cubes, you might see the following error:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'tblCustomers', Column: ‘Customer_Name’, Value: ''. The attribute is ‘Customer Name'.

As you can see, Analysis Services is complaining that there is a duplicate “blank” value in the column Customer_Name. How can there be a duplicate blank value as Analysis Services runs  a SELECT DISTINCT query on the relational table to retrieve column values when processing? Well, a few other things happen “under the hood” when Analysis Services is processing. Let’s consider a scenario where we have the following values in our Customer table:

Customer_Id	Customer_Name
1	ABC
2	XYZ
3	NULL
4

When Analysis Services processes a dimension, it will execute a SELECT DISTINCT query to retrieve a list of all the distinct values for each attribute; in our example, it will retrieve 4 DISTINCT values – ABC, XYZ, NULL, ‘’ (the last one being empty string). Now the tricky thing is that Analysis Services will then convert the NULL value to an empty string and so the list of values after conversion would be - ABC, XYZ, ‘’, ‘’ (the last two being empty strings). Obviously, the distinct list of values for Customer Name aren’t distinct anymore as there are two empty strings. In other words, the NULL value is being converted to an empty string and we already have an empty string in the distinct list. This is when Analysis Services fails to process the dimension complaining that there is a duplicate value “”(empty string). This behavior is actually controlled by an attribute property called “NullProcessing” which is set to Automatic by default. Setting it to automatic means that Analysis Services will convert NULL values to “empty string” if the attribute is of type text and to 0 if the attribute is of type numeric.

Let me give you another example as shown in the table below, and this time, let’s consider a numeric attribute.

Customer_Id	Number_of_Bikes
1	4
2	10
3	NULL
4	0

In this case, the error would be - Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'tblCustomers', Column: ‘Number_of_Bikes’, Value: '0'. The attribute is ‘Number of Bikes’.

Notice that now, Analysis Services is complaining that there is a duplicate zero value. When Analysis Services processes this dimension, it will come up with the following DISTINCT list of values - 4, 10, NULL, 0. It will then convert NULL to 0(zero) since the attribute is numeric. So the list of DISTINCT values after converting NULL to 0 would be - 4, 10, 0, 0. As you can see, the list is not distinct anymore and so Analysis Services would fail to process the dimension.

One of the easiest ways to resolve this is to convert NULL values in your data source to blank or zero if the attribute is text or numeric respectively. For example, you can use ISNULL(Customer_Name, ‘’) in your data source view to convert NULL value to an empty string(if the attribute is text) and use ISNULL(Number_of_Bikes, 0) to convert NULL value to 0( if the attribute is numeric).

Just a side note that you’ll see some other errors when this issue occurs and in most of the cases, these errors would be generic and less than helpful. I have seen the following errors when this issue occurs:

“Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Customer', Name of 'Customer' was being processed”

"OLE DB error: OLE DB or ODBC error: Operation canceled; HY008."

While the first error is generic, the second one could be due to various reasons including the one we discussed. Please see this post for the other possible reasons for the second error – Analysis Services Cube processing fails with error "OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Hope this helps!