Mind that collation : SSAS

jamiet — Mon, 15 Feb 2010 19:29:45 GMT

I’ve been using Analysis Services a lot lately in my current day job and recently encountered an issue that I believe people need to be aware of. The issue transpired to be a bug in our application rather than in Analysis Services itself but it was brought about by a misunderstanding of how Analysis Services handles uniqueness for dimension attributes and its worth highlighting the issue here.

I’ll demonstrate with a simple demo. Take this very simple [Product] dimension table:

We only have two products in here but note the [ProductCategory], both have the same value of “fruit” but they have different case-sensitivity. As far as SQL Server is concerned those values are the same because I have set it to have a case-insensitive collation:

Note: The CI of Latin_General_CI_AS stands for case-insensitive

Analysis Services on the other hand has a case-sensitive collation:

Onto that Analysis server I have deployed a [Product] dimension that has [Product Category] as an attribute:

And when I process that dimension I get an error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_Product', Column: 'ProductCategory', Value: 'fruit'. The attribute is 'Product Category'. Errors in the OLAP storage engine: An error occurred while the 'Product' attribute of the 'Product' dimension from the 'SSAS Collation Demo' database was being processed.

OK, so what’s going on here? Well fundamentally the problem is that our SQL Server and Analysis Server are using different collations. Analysis Services asks SQL Server to give it a distinct list of [ProductCategory] and SQL Server responds by giving back a single value (‘Fruit’) and hence our [Product Category] attribute only has one member. When Analysis Services attempts to process the its key attribute (i.e. [Product]) it tries to specify the relationship between the two members and the appropriate member in the [Product Category] attribute; it fails to do that because ‘fruit’ does not exist (because Analysis Services is using a case-sensitive collation) and thus we get a processing error.

You may think that the simple solution would be to change the collation of our Analysis Server but in fact that probably is not the correct course of action. What we should do is change our [Product Category] attribute so that instead of being uniquely identified by the [Product].[Product Category] field it is instead uniquely identified by [Product].[ProductId]. In other words our dimension changes from this:

to this:

Note the change in the <ColumnID> property.

After that, it all works because our unique list of [Product Category] is defined by [Product][ProductId] instead of [Product].[ProductCategory]!

I hope this has been useful to you. Its important to know about collations when SQL Server and Analysis Services are communicating with each other and hopefully this post serves to heighten the awareness.

Incidentally, this situation could probably be mitigated by normalising [ProductCategory] into a dimension table all of its own but given the lambasting I received last time I fired a shot across Kimball’s bows I think I’ll leave that one well alone :)

@Jamiet

SSIS Junkie : collation

Mind that collation : SSAS