One day our cube processing job failed with errors
<Error ErrorCode=”3239837702″ Description=”Server: The current operation was cancelled because another operation in the transaction failed.” Source=”Microsoft SQL Server 2012 Analysis Services” HelpFile=”” /><Error ErrorCode=”3238002695″ Description=”Internal error: The operation terminated unsuccessfully.” Source=”Microsoft SQL Server 2012 Analysis Services” HelpFile=”” />
<Error ErrorCode=”3240034316″ Description=”Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of..
<Error ErrorCode=”3240034317″ Description=”Errors in the OLAP storage engine: An error occurred while the ‘Non-RelatedAttr 1’ attribute of the ‘Dim Non-Related 1 ‘ dimension from the ‘CubeName’ database was being processed.”
<Error ErrorCode=”3238395904″ Description=”OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.” Source=”Microsoft SQL Server 2012
Amongst these was a warning which was the real cause of this error: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008
<Warning WarningCode=”1092550658″ Description=”Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Dimension_Dimtablename’, Column: ‘ABC-Column1’, Value: ‘XYZ’. The attribute is ‘ABC-Attribute1’.” Source=”Microsoft SQL Server 2012 Analysis Services” HelpFile=”” />
As per the blog directions we started working on the warning issue. When we checked the data in the database, we found duplicate data in table ‘Dimension_Dimtablename’ for field ‘ABC-Column1’ with value ‘XYZ’.
When we opened up the SSAS project in SSDT and checked the value for keyColumn for attribute ‘ABC-Attribute1’ for the related dimension, it was as usual by default the same as the attribute name ‘ABC-Attribute1’.
As per the technet post – 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.
That’s exactly how we changed keyColumn for attribute ‘ABC-Attribute1’ of the warning related dimension and Viola! our error was fixed.
To understand more about KeyColumns Dimension attribute property please go through this blog.