I have a dimension attribute value as "Us" and "US". While processing the dimension the SSAS is giving duplicate key error as both the values do not have the same cases. However in the SQL database this does not seem to be an issue. Please help me understand why this actually happens
Sounds like your SQL database (or at least that column) uses a case sensitive collation but SSAS uses a case insensitive collation. Can you change both to the same collation? The problem is that a SELECT DISTINCT query in SQL returns a list which isn't distinct according to SSAS and its collation.