Share via

Analysis Services Troubleshooting: OLAP storage engine error: A duplicate attribute key has been found when processing

  • Article

The full error message is “SSAS Error:  Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: <tablename>, Column: <columnname>. Value: <value>. The attribute is <attributename>.”

Applies to: 

SQL Server Analysis Services (all supported versions), tabular and multidimensional models

Introduction

This error occurs during processing, when a duplicate key is found for a given attribute.

Attribute keys are used to uniquely identify each member of an attribute. This is especially important when two or more attributes have the same value. For example, suppose you have two employees with a last name of Smith. In this case, you might want the attribute key for LastName to be EmployeeID so that each person is considered independently, and imported as separate rows during processing.

The attribute key is set through KeyColumn in the attribute properties page. As the previous example indicates, KeyColumn can be different from the Name column. The Name column provides the value seen by the application user (such as LastName), whereas the KeyColumn is used to resolve ambiguity during processing, when Analysis Services sends SELECT DISTINCT queries to the relational database.

Cause and resolution

The duplicate attribute key error can occur in multiple situations. In many cases, the recommended solution is to change the KeyColumn by setting it to a unique attribute, or by creating a composite key that results in a unique value when multiple attributes are evaluated as a unit.

Default attribute key is not unique

By default, both the Name and the KeyColumn are based on the same column in the DSV. If that column contains duplicate values, the duplicate key error will occur.

To investigate this error, check the results of the SELECT DISTINCT query that Analysis Services is sending by capturing and running the query yourself.  Most likely, changing the KeyColumn to a source column that provides unique values is the best resolution. For example, if EmployeeLastName has duplicates, set the KeyColumn to EmployeeID instead.

See this forum post for tips on how to investigate this error.

Hierarchies are not unique at the leaf level

Often, a hierarchy (such as Year | Month | Day, Country | Province | City, or Category | Subcategory | Product) is not unique at the leaf level. For example, a City might repeat multiple times throughout the data.

To resolve the ambiguity, create a composite key in KeyColumn that includes Country and Province to add context for each city. For example, a city named Redmond exists in both Washington state and Oregon. Creating a composite key that incorporates state is sufficient to distinguish between each one (USA.Washington.Redmond] and [USA.Oregon.Redmond]).

Note:  Date hierarchies are often subject to the duplicate attribute key error. Be sure to create composite keys at the Day level that specify Year and Month to ensure that keys are unique throughout the table. Always create the composite key for the lowest level attribute in the hierarchy. In our example, the composite key would be created on City.

See http://blog.programmingsolution.net/ssas-2008/period-dimension-time-dimension-creation-with-year-month-day-hierarchy/  for more information.

Duplicate key is a Null or blank

When checking for duplicate values, remember that Null or blank values can also be duplicates. If the error message specifies ‘’ for the value, that is an indication that the duplicate key is a null.

 As with the other scenarios previously mentioned, the fix requires changing KeyColumn. The Name column, which is what the user sees, can be null, blank, or unknown, but the KeyColumn should always be set to an attribute that contains unique values.

Remember that Analysis Services processes nulls as blanks for string columns, and zero for numeric columns. If you discover zeros or blanks that you didn’t expect to find, null processing behavior is the reason.

See this forum post for more information.

Collation, case-sensitivity, data type, control characters, or data length

Analysis Services and the relational database providing the data might have different collations, different settings on case-sensitivity, or different column lengths, truncating values that would otherwise be unique. If you get the duplicate key error, review these settings to ensure they are not the cause.

  • Data type -- One post describes how changing a data type from varchar to nvarchar resolved the issue, allowing Unicode characters to be recognized.
  • Case sensitivity at the dimension level – You can configure the dimension for case sensitivity allowed for differences in case to be processed as unique values. To specify case-sensitivity, use the Collation property on the dimension.  See this post for screenshots that show the property.
  • Case sensitivity at the database level -- Chris Webb points out  that conflicting case-sensitivity settings much higher in the chain (between the relational data source and Analysis Services) can also cause the error to occur.
  • Control characters -- Last but not least, sometimes an attribute value is included in processing because it is determined to be unique by SSAS, but is in fact only unique by accident. A leading character, trailing space, or control character worked its way into the data, creating a nearly identical second instance of that row. The discrepancy is only detected when processing occurs, when the duplicate attribute key error is raised.

Process Update succeeds but Process Full fails

If you get the error under certain processing options, you probably have a legitimate problem (i.e., an actual duplicate key in the data) that should be addressed.

In the forums, you might see posts advocating for changing the ErrorConfiguration property from (Custom) to (Default) as a way to get ProcessFull to succeed. The difference between the two settings is that (Custom) specifies Report and Stop for duplicate keys, while (Default) specifies IgnoreErrors.

Although it can be tempting to shut the error down, a better solution is to keep the duplicate key error intact, and then identify and address the root cause of the error.

ROLAP or ByTable processing generate false positives for this error

In this forum post, Akshai Mirchandani explains that the duplicate attribute key error returns false positives if you are using ByTable processing or ROLAP storage. In this situation, turning off ErrorConfiguration is the right way to go. In Dimension properties, expand ErrorConfiguration and set KeyDuplicate to IgnoreErrors.

Tip: Sometimes dimension or cube settings are inadvertently set to values that we don’t expect. Check the storage mode just in case. Also, you can learn a valuable diagnostic technique from tlum, who notes in this forum post: “Turns out MyDimension was unintentionally set to ROLAP when it should be MOLAP. So, it was processing the MyDimension dimension during the Excel query. Once it was flipped to MOLAP the problem went away.”

Partitions contain duplicate rows, including duplicate keys.

If rows overlap, with two or more partitions containing the same row, this error will occur. Check each partition to ensure that attribute keys are unique across the dimension table, not just within each partition.

See this forum post from Darren Gosbell for more information.

More information

To get more information, see this curated answer: https://curatedviews.cloudapp.net/4569/how-do-i-prevent-duplicate-attribute-keys-in-a-cube

The following forum posts were referenced in this page. If you have trouble with the links, review this list to get the full URL addresses: