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:

image

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:

Project Template Default Language
Analysis Services Tabular Project Language of BIDS
Import from PowerPivot Language of the PowerPivot Workbook
Import from Server (Tabular) Language of the source database

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:

Project Template Collation
Analysis Services Tabular Project Not serialized
Import from PowerPivot Not serialized
Import from Server (Tabular) Serialized if the user explicitly set the collation on the source database, otherwise not serialized

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.

Comments (2)

  1. >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!).

    Formatting of currency symbols by language is one of the worst things about SSAS today, especially because it used to work years ago and got broken by an ill-advised SP at some point. In my opinion, the Currency format should be removed – if you've got a currency value you should specify a currency symbol explicitly. It's a bit weird that the symbol should be dependent on a server-side setting and I've seen numerous examples of configuration errors messing up reports as a result. Some other things, such as the thousands and decimal separators used, should be dependent on the report viewer's locale and not the server language setting – if I have a cube used across multiple countries then a value seen by a US or UK user as "1,000.01" should be seen by a German user as "1.00,01" to avoid confusion.

    Sorry, rant over, but this has been a sore point in some European SSAS projects for a while now…

  2. cathyk says:

    Chris – thanks for the feedback. I can totally dig why this is not your favourite feature.

    A couple of things. The language is not a server side setting in this case. The language used is determined by the client tools when the model is created. The currency symbol can actually be changed in the property grid. The default though is the language of the model.

    The display formatting of numbers is something that I'll get to in another post. There are some nuances to that behaviour.

    Thanks