SSAS MD Processing error duplicate attribute key due to collation msimatch ?

Question

I have a varchar column that I use as an attribute in a dimension whose value serves as key (I don't have a key or a SK for it). In this column I have different values and that's the root of the issue, I have Phase Mise en oeuvre and Phase Mise en œuvre.

When I process the cube I have the following issue : Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'CubeClarity_DimWBS', Column: 'SousSysteme_Labe', Value: 'Phase Mise en oeuvre'. The attribute is '2 - Regroupement Sous-système'.

I'm pretty sure that the issue is caused by SQL Server thinking it's different values when SSAS thinks it's the same value because when I replace Phase Mise en œuvre by Phase Mise en oeuvre I can process the cube and I have also œ char in others values.

I'm struggling with this issue since few days, I search on Internet and didn't find any solution. I try several things without luck :
The SQL Server collation is SQL_Latin1_General_CP1_CI_AS and SSAS MD was Latin1_General CI AI. I tried to change the SSAS collation to Latin1_General CI AS (I don't manage the SQL Server so I can't change collation on it).

In the XMLA script to process the cube the collation was French_CI_AI, I changed it to French_CI_AS then Latin1_General CI AS without success. I tried also to English_CI_AS which is not supported.

A quick and dirty solution would be to replace all œ by oe but the problem may occur with other char so it's not a long term solution.

Any hints or solutions are welcome

Answer 1

Hi @Passuello Christophe ,

Welcome to Microsoft Q&A!

I'm pretty sure that the issue is caused by SQL Server thinking it's different values when SSAS thinks it's the same value because when I replace Phase Mise en œuvre by Phase Mise en oeuvre I can process the cube and I have also œ char in others values.

The collation of the SQL Server relational database is “more detailed” than the collation on the SSAS side for an attribute’s key column. For example, SQL Server uses a case sensitive collation (like SQL_Latin1_General_CP1250_CS_AS) while SSAS uses a case in-sensitive collation. This causes SQL Server to treat a and A as two different values, while SSAS treats them as the same value (giving the duplicate key error). The same thing happens with an accent sensitive collation.

Solution: If you can, use the same collation type on both sides. If not, you could access the database table using a view that changes the collation or change the collation for the SSAS key column(s). For more information, please refer to this blog and Analysis Services Troubleshooting.

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.