SSAS Processing cube failed to duplicate attribute key BUT running the raw sql shows no dups

Question

I am trying to process a cube but it is failing due to duplicate attribute keys. What's weird though is when running the raw sql from the process cube dialog I see no duplicates in the query.

The exact error when processing the cube is:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_vw_DimDRG', Column: 'DRGCode', Value: '743'. The attribute is 'DRG Code'.

When running the generated sql from the process cube dialog in my database I do NOT get any dups. This sql is as follows:

/*raw sql query used in SSAS dimension processing*/
SELECT DISTINCT

[dbo_vw_DimDRG].[DRGCode] AS [dbo_vw_DimDRGDRGCode0_0]

  FROM [dbo].[vw_DimDRG] AS [dbo_vw_DimDRG]

--WHERE DRGCode = '743' /*I added this to filter results*/

It is worth noting that the data is VARCHAR. It is also worth noting that there are multiple '743' values in the data, however this is intentional, and the cube query is using DISTINCT so this shouldnt be an issue. This column is not my primary key, and the primary key column processes just fine.

What I've tried:

1. Using Len() to ensure all the '743' values are the same (maybe there are trailing spaces?)
2. Confirmed that there are not both NULL and '' values in the data.
3. Imported other clients, which use the exact same relational structure (and have multiple '743' values) and they work just fine.

Can you think of anything I might be missing here? I feel like it has something to do with the VARCHAR data not being truly 'Unique' but I don't know enough about SSAS to determine the issue.

Answer 1

If anyone finds this:

Using DATALENGTH() does NOT show hidden characters. I ended up fixing this issue by adding this logic to my column.

REPLACE(REPLACE(ISNULL([AttributeColumn], ''), CHAR(13), ''), CHAR(10), '')

Doing this allowed the cube to process successfully. I guess there must be hidden characters that arent actually picked up by datalength().