Globalization scenarios for Analysis Services Multidimensional
Analysis Services stores and manipulates multilingual data and metadata in 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, 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.
For multidimensional models only, captions and attribute members can be expressed as translations. You can define one or more 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 from 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 - Business Intelligence, are translated into these languages: Chinese Simplified, Chinese Traditional, French, German, Italian, Japanese, Korean, Portuguese (Brazil), Russian, and Spanish. To use a language-specific version of the tools, install a language-specific version of SQL Server (for example, install the German version of SQL Server to get Management Studio in German) or run the standalone setup in the target language for SQL Server Data Tools - Business Intelligence.
Analysis Services lets you set language, collation, and translations independently throughout the object hierarchy.
Scenarios enabled through language, collations, and translations 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.
Applies to: Multidimensional data models only
At the client level, globalized applications that consume or manipulate Analysis Services multidimensional data can use the multilingual and multicultural features in Analysis Services:
Translations (Analysis Services) are used to embed multiple captions for a single object, where each translated string can exist alongside other translations. You can use the SQL Server Data Tools (SSDT) to define the translations for the caption, description, and account types for cubes and measures, dimension and attributes. 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.
A lesson on how to use this feature can be found in Lesson 9: Defining Perspectives and Translations of the Analysis Services tutorial.
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.