ssas dimension processing key not found error

3
votes

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!

2
sql-server-2008ssasbusiness-intelligencedimension
I don't know if that is the case but the value is 'Mr Peter McDonald' and your query returns Peter McDonald. If it was a case of multiple keys the error would say so – Athanasios Kataras
Sorry I am not sure if I am understanding your comment correctly, are you referring to the difference of having and not having quote? – rlee923
oh that part, yeah sorry I missed that when I copy pasted the value, both of them have Mr – rlee923
I would suggest to you to do the following then. Make a named query on the table you use as the dimension-table, and add a new incremental key column to use as a key for dimension. – Athanasios Kataras
can you post a screenshot of your schema? – Bill Anton

2 Answers

2
votes

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.

0
votes

you need to re-deploy your cube after you change the case-sensitive settings.