Collation and language settings in tabular models

Collation and language settings are tricky; so tricky that my two planned blog posts on this topic can only scratch the surface of what goes on under the covers here. This post is a simplified overview of collation and language settings for Denali. In a future post I will show you an example of how to work with collations.

There are two read-only properties on tabular models, Collation and Language, that give you a hint as to what the engine is thinking when it formats and sorts your data. For tabular models, we show you what the database settings are, and you can observe them by clicking on the bim file and viewing its properties:

The language is used for some data formatting. For example, the language hard codes the currency symbol format, so the currency symbol doesn’t change by the report viewer’s locale (very important!). The collation is used for sorting data, comparing data, and figuring out what to do with pasted data. The MSDN overview of languages and collations (which applies to both multidimensional and tabular models) provides some additional information on this subject.

When you create a new tabular project in SQL Server Data Tools (BIDS), the language is always set for you and serialized as part of the model. Here is how the language of the model gets chosen:

This language is not editable after it has been set. It is also persistent across modeling environments. If your model is 1033, it stays 1033 even if you open it in German BIDS. The language is serialized all the way down from Database to Cube to Dimension. The tools keep the language the same across all of these objects, and never mixes up the languages.

Now to the collation. The collation may or may not be serialized as part of the model, as the table below describes:

When the collation is not serialized, the Collation property in the property grid appears blank. This means that the collation is blank 95%+ of the time. How are you supposed to know what the collation is? Well, the engine figures out what collation to use based on the language that was set for the tabular model. It uses the same routines as SQL engine does to pick an appropriate default collation. This collation reference from SQL setup gives a pretty accurate mapping of the default collation for each LCID. Once the engine picks the collation, it uses it consistently when sorting, processing, and comparing data.

Thank you Brandon Unger for tolerating my incessant pestering while writing this post. I’m glad you still answer with my emails and IMs with helpful information.