Duplicate attribute key error

A frequent error when processing an SSAS cube begins “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing”. I have found that this is usually one of two conditions:

Typical Causes

1. An actual duplicate key is found where a value for a child in a hierarchy occurs with two different parent values.
2. A value for a data item is NULL.

Solution for actual duplicate key

The former is easier to discover and can be resolved by using a compound key for the parent that includes the child key. Look for it when the field in question is part of a hierarchy.  A child can only have one parent.

Solution for null data

The latter can be confounding because the error message is often misleading. It can show a value in the error message, not indicating that the real underlying cause is the NULL value. This can be repaired by using a COALESCE statement to replace a NULL with a value such as ‘Unknown’ or simply ”.

A good practice is to use views as the source of data to a cube, rather than making any modifications within the SSAS data source view. It is in the view that I add the COALESCE function around the data item.

For example, if there are nulls in the MIDDLE_NAME data item, the view

CREATE VIEW vwPeople AS

SELECT FIRST_NAME [First Name],
COALESCE(MIDDLE_NAME,”) [Middle Name],
LAST_NAME [Last Name]
FROM PEOPLE

will use the MIDDLE_NAME value for [Middle Name] unless it is NULL and then will use the empty string instead.