By default, SQL 2008 sets the dimension error handling property to Custom, which breaks any dimensions that have duplicate attributes. For instance, if you have a state or province column in your dimension and it’s not unique, it will “report and stop” when handling the error.
The fix is to select “report and continue”, or set “default” for your error handling, or setup a composite key that makes the attribute unique, or configure relationships that make it unique.
There are some other things that SSAS does behind the scenes. This article provides some more info.
The reason is that when processing the dimension, SSAS by default does a right trim and this eliminates not only the spaces, but also any of the three special characters (tab, line feed and carriage return) we added! Note how this differs from T-SQL where these characters are not impacted by RTRIM as can be seen here: