Globalization scenarios for Analysis Services
Analysis Services stores and manipulates multilingual data and metadata for both tabular and multidimensional data models. Data storage is Unicode (UTF-16), in character sets that use Unicode encoding. If you load ANSI data into a data model, characters are stored using Unicode equivalent code points.
The implications of Unicode support means that Analysis Services can store data in any of the languages supported by the Windows client and server operating systems, allowing read, write, sort, and comparison of data in any character set used on a Windows computer. BI client applications consuming Analysis Services data can represent data in the user's language of choice, assuming the data exists in that language in the model.
Language support can mean different things to different people. The following list addresses a few common questions related to how Analysis Services supports languages.
Data, as already noted, is stored in any Unicode-encoded character set found on a Windows client operating system.
Metadata, such as object names, can be translated. Although support varies by model type, both multidimensional and tabular models support the addition of translated strings inside the model. You can define multiple translations, and then use a locale identifier to determine which translation is returned to the client. See Features below for more details
Error, warning, and informational messages returned by the Analysis Services engine (msmdsrv) are localized into the 43 languages supported by Office and Office 365. No configuration is required to get messages in a specific language. The locale of the client application determines which strings are returned.
Configuration file (msmdsrv.ini) and AMO PowerShell are in English only.
Log files will contain a mix of English and localized messages, assuming you have installed a language pack on the Windows server that Analysis Services runs on.
Documentation and tools, such as Management Studio and SQL Server Data Tools, are translated into these languages: Chinese Simplified, Chinese Traditional, French, German, Italian, Japanese, Korean, Portuguese (Brazil), Russian, and Spanish. Culture is specified during installation.
For multidimensional models, Analysis Services lets you set language, collation, and translations independently throughout the object hierarchy. For tabular models, you can only add translations: language and collation are inherited by the host operating system.
Scenarios enabled through Analysis Services globalization features include:
One data model provides multiple translated captions so that field names and values appear in the user's language of choice. For companies operating in bi-lingual countries such as Canada, Belgium, or Switzerland, supporting multiple languages across client and server applications is a standard coding requirement. This scenario is enabled through translations and currency conversions. See Features below for details and links.
Development and production environments are geo-located in different countries. It's increasingly common to develop a solution in one country and then deploy it another. Knowing how to set language and collation properties is essential if you are tasked with preparing a solution developed in one language, for deployment on a server that uses a different language pack. Setting these properties allows you to override the inherited defaults that you get from the original host system. See Languages and Collations (Analysis Services) for details about setting properties.
At the client level, globalized applications that consume or manipulate Analysis Services multidimensional data can use the multilingual and multicultural features in Analysis Services:
Translation support in Analysis Services are used to embed multiple captions for a single object, where each translated string can exist alongside other translations.
You can retrieve data and metadata from Analysis Services objects on which translations have been defined automatically by providing a locale identifier when connecting to an Analysis Services instance.
Create using SQL Server Data Tools (SSDT) to generate translation files that you fill in and then import back into the model.
Create usingSQL Server Data Tools (SSDT) to define the translations for the caption, description, and account types for cubes and measures, dimension and attributes.
Translations can be created for captions and descriptions.
Translations can be created for object names, captions, identifiers, and descriptions, can also be in any Unicode language and script. This is true even when the tools and environment are in another language. For example, in a development environment that uses English language and a Latin collation throughout the stack, you can include in your model an object that uses Cyrillic characters in its name.
See Translations in Tabular models (Analysis Services) for details.
See Translations in Multidimensional Models (Analysis Services) for more information. A lesson on how to use this feature can be found in Lesson 9: Defining Perspectives and Translations of the Analysis Services tutorial.
Applies to multidimensional only. Currency Conversions (Analysis Services) is through specialized MDX scripts that convert measures containing currency data. You can use the Business Intelligence Wizard in SQL Server Data Tools - Business Intelligence to generate an MDX script that uses a combination of data and metadata from dimensions, attributes, and measure groups to convert measures containing currency data.
Specify default language and Windows collation for an Analysis Services instance. Your choices affect data and metadata managed by Analysis Services.
Define translations for an Analysis Services database and objects contained by the database. This topic explains how Analysis Services resolves requests for translated data and metadata from client applications.
Define a currency conversion using the Business Intelligence Wizard.
Reviews several design and coding practices that can help you avoid problems related to multi-language data.