Introduction

It has been a couple of times now that I have been receiving this error while processing dimensions in SSAS. This is a very common error and you might receive something like this:

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

If you see the error, it is evident that there is a duplicate "blank" value in the column, ProductName.

Let us look at the data source and try to find out how the source table looks like.

The source is a Product table that contains the following records. As you can see, all the values in the ProductName table are unique, in other words, there are no duplicate "blank" values. Then what could be the reason for this error in SSAS.

Well, that is how the SSAS engine actually works while processing NULL values at the source. When a dimension is processed in SSAS, the engine executes a SELECT DISTINCT query on all the attributes for that dimension. In the SELECT statement, all the four records will appear as distinct, however, behind the scenes, the SSAS engine converts the NULL values into an empty string (equivalent for "blank") while processing and hence the reason for this error.

So, according to the SSAS engine, the attribute values after processing becomes "Camera", "Mobile", "",  and "" (the last two values being an empty string). This is when the SSAS engine fails to process the dimension and throws the "Duplicate value" error. This behavior of the dimension is actually handled by a property known as Null Processing, which is usually set to Automatic by default. That being set, the SSAS engine will now convert all NULL values into an empty string for text fields and for numerical fields it will convert the NULLs into 0.

Resolution

There are two solutions to resolve this error.

1. Convert the NULL values to "blank" or 0 at the data source.
2. Alter the KeyDuplicate property in the Error Configuration property for the dimension.

Resolution 1: Convert NULL values to blank or 0 in the data source

This is one of the easiest ways to resolve this error. You can simply make sure that there are no NULLs in the source database by wrapping the column names with an ISNULL function. E.g.: The query for this dimension can be modified as below:

SELECT
    ProductID,
    ISNULL(ProductName,'') AS ProductName
FROM [dbo].[Products]

Now, when the SSAS engine will process the dimension, there won't be any duplicates available as only one "blank" value was fetched in the SELECT DISTINCT statement.

Resolution 2: Alter the KeyDuplicate property in the Error Configuration

Alternatively, you can also modify the KeyDuplicate value in the Error Configuration property for the dimension. This can be done by the steps mentioned below.

1. Right-click on the dimension name (Product) and select Properties.
2. Expand the property ErrorConfiguration.
3. The value for the KeyDuplicate is set to ReportAndStop by default.
4. Select IgnoreError from the dropdown and process the cube.
5. There shouldn't be any errors now.

Takeaway

This article explains the reason for Duplicate Value errors in SSAS and also provides a resolution as to how can we get rid of th error as well.

For more information please see the link: https://social.technet.microsoft.com/wiki/contents/articles/19726.analysis-services-errors-in-the-olap-storage-engine-a-duplicate-attribute-key-has-been-found-when-processing.aspx