Languages and Collations (Analysis Services - Multidimensional Data)
Microsoft SQL Server Analysis Services supports all languages that are supported by Microsoft Windows operating systems. For more information about language support in SQL Server Analysis Services, see Collation and Unicode Support. Besides specifying the default language and collation used by an Analysis Services instance, you can also provide multilanguage support for individual Analysis Services objects, including cubes, measure groups, dimensions, hierarchies, and attributes, by defining a translation associated with an Analysis Services object. The default language and collation settings for an Analysis Services instance specify the settings used for data and metadata if a translation for a specific language identifier is not provided for an Analysis Services object, or if a client application does not specify a language identifier when connecting to an Analysis Services instance.
Language Identifiers
Analysis Services uses Windows language identifiers to specify the selected language for Analysis Services instances and objects. A Windows language identifier corresponds to a combination of Windows primary language and sublanguage identifiers. For example, if you select English (United States) in the Language drop-down list on the Collation Settings page of the Microsoft SQL Server Installation Wizard, the corresponding Windows language identifier, 0x0409 (or 1033), is specified in the Language element of the configuration settings file for the Analysis Services instance. For more information about available Windows language identifiers, see "Table of Language Identifiers" in the MSDN documentation.
Collations
Analysis Services uses Windows collations to specify the selected collation for Analysis Services instances and objects. A Windows collation identifier corresponds to a combination of code page and sort order information. For example, if you select Latin1_General in the Windows collations drop-down list on the Collation Settings page of the Microsoft SQL Server Installation Wizard, and select the Binary sort order option, the corresponding Windows collation identifier, Latin1_General_BIN, is specified in the Collation element of the configuration settings file for the Analysis Services instance.
Windows Collations
Windows collations define rules for storing character data based on an associated Windows locale. The base Windows collation rules specify which alphabet or language is used when dictionary sorting is applied, and also the code page that is used to store non-Unicode character data. Binary collations sort data based on the sequence of coded values that are defined by the locale and data type. A binary collation in Analysis Services defines the language locale and the ANSI code page to be used, enforcing a binary sort order. Because of their relative simplicity, binary collations are useful in achieving improved application performance. For non-Unicode data types, data comparisons are based on the code points defined in the ANSI code page. For Unicode data types, data comparisons are based on the Unicode code points. For binary collations on Unicode data types, the locale is not considered in data sorts. For example, Latin1_General_BIN and Japanese_BIN yield identical sorting results when used on Unicode data. For more information about Windows collations in SQL Server, see Working with Collations.
By specifying a Windows collation for Analysis Services, the Analysis Services instance uses the same code pages and sorting and comparison rules as an application that is running on a computer for which you have specified the associated Windows locale. For example, the French Windows collation for Analysis Services matches the collation attributes of the French locale for Windows.
There are more Windows locales than there are Windows collations defined for Analysis Services. The names of Windows locales are based on a language identifier, such as English, and a sublanguage identifier, such as United States or Australia. However, many languages share common alphabets and rules for sorting and comparing characters. For example, 33 Windows locales, including all the Portuguese and English Windows locales, use the Latin1 code page (1252) and follow a common set of rules for sorting and comparing characters. The SQL Server Windows collation Latin1_General, based on this code page and associated sorting rules, supports all 33 of these Windows locales. Also, Windows locales specify attributes that are not covered by Analysis Services Windows collations, such as currency, date, and time formats. Because countries and regions such as Australia and the United States have different currency, date, and time formats, they require different Windows collations. They do not require different Analysis Services Windows collations, however, because they have the same alphabet and rules for sorting and comparing characters.
Note
While multiple language identifiers can be specified for Analysis Services objects, the same Analysis Services Windows collation is used for all Analysis Services objects, with a single exception, regardless of language identifier. The single exception to this functionality is the CaptionColumn property of an attribute in a database dimension, for which you can specify an Analysis Services Windows collation to collate the members of the specified attribute. For more information about defining attribute translations, see Translations (Analysis Services - Multidimensional Data). If the same language is used by all of the users for your Analysis Services instance, select the collation that supports the specified default language for your instance. If multiple languages are used, choose a collation that best supports the requirements of the various languages. For example, if the users of your instance generally speak western European languages, select the Latin1_General collation.
Sort Order Options
Several sort order options can be applied to the specified Analysis Services Windows collation to additionally define sorting and comparison rules based on case, accent, kana, and width sensitivity. The following table describes Windows collation sort order options and associated suffixes for Analysis Services.
Sort order (suffix) |
Sort order description |
|---|---|
Binary (_BIN)1 |
Sorts and compares data in Analysis Services based on the bit patterns defined for each character. Binary sort order is case sensitive and accent sensitive. Binary is also the fastest sorting order. For more information, see Guidelines for Using BIN and BIN2 Collations. If this option is not selected, Analysis Services follows sorting and comparison rules as defined in dictionaries for the associated language or alphabet. This option corresponds to the Binary option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
BIN2 (_BIN2)1 |
Sorts and compares data in Analysis Services based on Unicode code points for Unicode data. For non-Unicode data, BIN2 will use comparisons identical to binary sorts. The advantage of using a BIN2 sort order is that no data resorting is required in applications that compare sorted data. As a result, BIN2 provides simpler application development and possible performance increases. For more information, see Guidelines for Using BIN and BIN2 Collations. This option corresponds to the Binary 2 option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
Case-sensitive (_CS) |
Distinguishes between uppercase and lowercase letters. If selected, lowercase letters sort ahead of their uppercase versions. This option is set by selecting the Case-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
Case-insensitive (_CI) |
Does not distinguish between uppercase and lowercase letters. Analysis Services considers the uppercase and lowercase letters to be identical for sorting purposes. This option is set by clearing the Case-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
Accent-sensitive (_AS) |
Distinguishes between accented and unaccented characters. For example, 'a' is not equal to 'ấ'. If this option is not selected, Analysis Services considers the accented and unaccented versions of letters to be identical for sorting purposes. This option corresponds to the Accent-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
Accent-insensitive (_AI) |
Does not distinguish between accented and unaccented characters. Analysis Services considers the accented and unaccented versions of letters to be identical for sorting purposes. This option is set by clearing the Accent-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
Kana-sensitive (_KS) |
Distinguishes between the two types of Japanese kana characters: hiragana and katakana. If this option is not selected, Analysis Services considers hiragana and katakana characters to be equal for sorting purposes. .gif "Note") Note
There is no sort order suffix for kana-insensitive sorting.
This option corresponds to the Kana-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
Width-sensitive (_WS) |
Distinguishes between a single-byte character and the same character when represented as a double-byte character. If this option is not selected, Analysis Services considers the single-byte and double-byte representation of the same character to be identical for sorting purposes. Note
There is no sort order suffix for width-insensitive sorting.
This option corresponds to the Width-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio. |
1 If BIN2 is selected, the case-sensitive, case-insensitive, accent-sensitive, accent-insensitive, kana-sensitive, and width-sensitive options are not available.
Each Windows collation is combined with sort order suffixes to define case, accent, width, or kana sensitivity. For example, the default value of the Collation configuration property for Analysis Services is Latin1_General_AS_CS, specifying that the Latin1_General collation is used, with an accent-sensitive, case-sensitive sort order.
Specifying the Default Language and Collation
You can specify the default language and collation settings for an Analysis Services instance during installation, in the Collation Settings page of the Microsoft SQL Server Installation Wizard.
After installation, you can change the default language and collation settings for an Analysis Services instance in SQL Server Management Studio using the Language/Collation page of the Analysis Server Properties dialog box. For more information about how to use the Analysis Server Properties dialog box to change language and collation settings, see Language/Collation (Analysis Server Properties Dialog Box) (Analysis Services - Multidimensional Data).
Using EnableFast1033Locale
If you use the English (United States) language identifier (0x0409, or 1033) as the default language for the Analysis Services instance, you can get additional performance benefits by setting the EnableFast1033Locale configuration property, an advanced configuration property available only for that language identifier. Setting the value of this property to true enables Analysis Services to use a faster algorithm for string hashing and comparison. For more information about setting configuration properties, see Analysis Services Configuration Properties.