Identifying cause of duplicate attribute keys in SSAS

At work today, I was a bit stumped when SSAS reported the cube wouldn’t build, due to a duplicate key found in the source database, although having checked this there was none to be found.

However after consulting with a colleague, and finding this article on the internet, it turns out that there was a white space after the value of an attribute in the source DB. After SSAS runs the DISTINCT query on the source DB, it trims the white space, and because of that it created a duplicate key.

The post-processing of attributes can be customised in the dimension designer in SSAS. For me it was an issue with the source data, but possibly for you, it might be required to tweak the trimming and null replacement functions.