SSAS Duplicate Attribute Error – Another Cause

I had  a real head banger this afternoon and I’m not talking about the heavy metal playlist I was jamming to in my iPod.

I had a table that, in addition to the surrogate key, business keys, etc had these columns:

I had a dimension in SSAS where I had a Level1 -> Level2 Hierarchy built. When I tried to process the dimension, SSAS kept kicking out “duplicate attribute error” on Perry. I did the usual checking, yes my attribute relationships were OK, the Key property was built correctly, etc.

So then I moved to look at the data itself. I first did a SELECT * FROM CoolShow WHERE Level1 = ‘Phineas and Ferb’ and Level2 = ‘Perry’.

I got back 4 rows. Hmm. After some more head banging (Guns ‘n Roses, Paradise City) I wound up doing a SELECT * FROM CoolShow WHERE Level1 = ‘Phineas and Ferb’ and I get back 42 rows with Perry. Hmm, I say to myself, “self, that looks odd”. To which self replied “duh”.

Then self suggested I do a SELECT ‘*’ + Level2 + ‘*’ FROM CoolShow WHERE Level1 = ‘Phineas and Feb’

This yielded some interesting results, 4 rows read *Perry* the other rows read *Perry *   (Note the blank space between y and * .)

Well obviously I needed a RTRIM, which I dutifully added then reran the query. Only to get the *Perry * again in the output. At this point self said I was on my own and abandoned me to drown its sorrows in a pitcher of margaritas.

I took the output and copied it into an editor that would do hex mode. So what do I see but a 0D 0A in the space between the y and the *, causing me to scream “AH-HA” as Queen’s Bohemian Rhapsody hit its crescendo. I also scared the cat, but I only mention that because cute cat things are supposed to be popular on the internet and I figure it might help my SEO. For those who don’t speak HEX, 0D 0A is 13 and 10, which turn into a Carriage Return and Line Feed.

Now by this point most of you have probably given up on this handy tip, deciding a pitcher of margaritas sounded pretty good and left to find some. But if you are still hanging in, I modified the view with this code:

RTRIM(REPLACE(REPLACE([Level2], CHAR(13), ”), CHAR(10), ”) ) AS [Level2]

Returning to the cube I was able to process the dimension successfully and answer the question of “Where’s Perry?” (Answer: He’s at the bar trying to keep a drunken self from using his evil margaritainator invention.)

So the moral of the story, if you get duplicates error, and your dimension looks okey-dokey, check the data to see if you have some errant CR/LFs. Apparently SSAS doesn’t handle them very well.

Now if you’ll excuse me, I’m going to join self at the bar before self guzzles all the margaritas (self is such a drunken sot). AC/DC, take me away with some “Highway to Hell”!