Collation, Localization and Globalization, oh my!

From time to time we get questions about these three topics. Let’s take them one at a time and see how PowerPivot works under the covers. PowerPivot works on all types of collations, languages and regional settings.

(if you like this kind of ‘internationalization stuff’, read on)

1. Collation – or sort order for the underlying data. When you create a workbook we decide the language of the database (collation) which determines the sort order etc. This information is persisted and stays with the workbook as it goes through it lifecycle. The workbook collation always sticks to the collation of the system the author initially created the workbook. If the workbook was created in one collation and is needed in a different one, then you must recreate the workbook in the on a machine with the desired collation.
2. Localization – This term means the language you see in the UI, error message, etc. To see the list of languages PowerPivot supports, click on Settings –> Languages. By default we match the Excel language setting. If the language setting is not available we automatically revert back to English (US). However user has a choice to change the language. If a workbook is created in French and a German user opens the workbook then localized UI string will be German. Under the covers there is a single PowerPivot resource file (installed by Setup) that contains resources for all the languages we support.
3. Globalization – This term means the regional settings you are working on, such as date formats, currency formats, digit separators, etc. We support all types of regional settings. The only difference here is Excel automatically changes the formatting on some cells based on regional settings. However for PowerPivot you must close the window and open it again. There are few cases the formatting is preserved (and does not change) and chief among them is Currency. At this time we don’t have a workaround except to recreate the workbook in the desired currency regional setting.

   But dealing with dates can also be tricky, here is a DAX trick:

   The main problem with sorting of dates is the fact that sorting dates A to Z will likely make the dates appear in a way that does not make a lot of sense (since the data will be sorted ascending based on the month number rather than the year).
   The workaround for this particular sorting issue would be to create a calculated column, where you would use the DAX FORMAT syntax to reference the date column to re-format the dates as Year-Month- date. At least this way you can ensure that the column sorts correctly. The DAX formula would look something like this:

=FORMAT([DateColumn], "yyyy-mm-dd")

However, after all of this you still might run into issues – something that looks like a collation issues, but is not. For example, PowerPivot always imports data from an SSAS cube as text (this is a restriction of the tabular data formats with the current Analysis Services OLE DB provider). The most common issue is that numeric sort differently in text. Fortunately the column data types can easily be overridden in the PowerPivot modeling window. Just select the whole column and pick the type from the dropdown list in the ribbon.

Enjoy.