Collation

Jamie Thomson discusses in his Blog a problem with different collations in SQL Server and SSAS that results in the following error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing.

The problem is closely related to the problems we described here and here.

The difference between Names and descriptive Attributes

Depending on the configuration of your SSAS landscape (DB/OS/Locale of the data source, OS/Server Locale and Column Collation of the SSAS machine/cluster), you can get very strange processing errors in your dimensions especially related to character-based attributes/names.

For example, the default collation settings in SSAS (ignore whitespaces, case-insensitivity) will usually unify more (European/German-rooted) strings than a "distinct" selection in the database of your choice. NULL entries which SSAS cannot stand at all in dimension processing are also a constant cause of problems in that respect ...

If you now wonder, why this leads to processing errors in certain attributes (duplicate key error) and not in others, please remind the setup of each attribute into a key and a name column (the latter pointing to the former in the standard-case).

One of the fundamental differences between the two is that a "select distinct" of the key columns MUST NOT have two rows which will be unified by SSAS collation processing while this is not necessarily true for name columns in which case an arbitrary value is taken as the representative.

duplicate key error

Hilmar Buchta explains in his Blog A duplicate attribute key has been found when processing… some common reasons for duplicate key errors in processing error messages. The most likely reasons are:

• NULL values in the attribute key column.
• data that violates the attribute relationships defined in the dimension.
• A third reason is that there are separate key and name source fields for an attribute, so there is more than one name corresponding to a key.
• A related issue results from collation effects: distinct string values from an oracle source are considered as duplicates by SSAS because they only differ in capitalization (upper/lower case), trimming or some strange characters (as the german "ß" that will be replaced by "ss" or "ä", "ü", "ö" etc.).