ssas dimension processing key not found error

Question

I have this strange case where I am trying to process a dimension, the data source is MSSQL and when I try to process this dimension I am getting this error

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'application', Column: 'Full_Name', Value: 'Mr Peter McDonald'. The attribute is 'Applicant Full Name'.

Since I think I know what the problem is, I have set the case sensitive property for this column and I tried processing again and it's giving me the same result.

when I query for the column using like, I get two records

Peter McDonald

Peter Mcdonald

I would normally assume that setting the case sentivity would solve the problem, but it's not working in this case, so I was wondering if anyone knows how to resolve this.

Thanks a lot for the help in advance!

Answer 1

Sorry guys for getting back too late. I haven't logged into stackoverflow for a long time.

The short version : This is basically caused by SQL server is not case sensitive for comparison by default. So if you want to prevent this problem you have to make the table or the database case sensitive - set collation to ASCS

Slightly longer version : Even though MSSQL matches Case insensitive what it still stores values differently, and this becomes an issue for SSAS when it tries to do a distinct on a particular column. It's been a while so I can't remember exactly but it will have two values for 1 index and it will complain like the question I have posted.