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:
- An actual duplicate key is found where a value for a child in a hierarchy occurs with two different parent values.
- 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]
will use the MIDDLE_NAME value for [Middle Name] unless it is NULL and then will use the empty string instead.
Great post. I have exactly the middle name scenario.