I was processing a SSAS database to test some aggregations today, and I noticed some errors and came across some unexpected behavior.
When I was processing the User dimension (among others), I got an error similar to the following:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: ‘dbo_DimUser’, Column: ‘Country’, Value: ‘US’; Table: ‘dbo_DimUser’, Column: ‘Region’, Value: ‘NY’; Table: ‘dbo_DimUser’, Column: ‘City’, Value: ‘Albany ‘. The attribute is ‘City’. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute User of Dimension: User from Database: OfficeLive, Record: 1234.
Now, if I was processing a cube and saw this, I would immediately think a referential integrity issue. Not so in this case. So I looked at the missing key value, in this case ‘Albany ‘. The string had some trailing spaces… but that shouldn’t be a big deal, since the Key property in BIDS was set to perform right trimming.
But with a little sleuthing, I found something interesting: the error message was <gasp> a lie! When I looked at the database, it wasn’t a trailing space (which is what appeared in the error message), it was a trailing tab. The character were automagically converted in the error message. So it wasn’t getting trimmed and matching the already existing ‘Albany’ key. The weird thing was that it was just failing, because of the tab character in the key. I actually would have expected to see two entries for ‘Albany’ (one with some whitespace, if you looked hard enough) in the dimension. I actually prefer this, but it did take me a little by surprise…
Cheers,
David