Translations (Analysis Services)

Article
02/03/2015

Applies to: Multidimensional only

In an Analysis Services multidimensional data model, you can embed multiple translations of a caption to provide locale-specific strings based on the LCID. Translations can be added for the database name, cube objects, and database dimension objects.

Defining a translation creates the metadata and translated caption inside the model, but to render localized strings in a client application, you must either set the Language property on the object, or pass a Locale Identifier parameter on the connection string (for example, by setting LocaleIdentifier=1036 to return French strings). Plan on using Locale Identifier if you want to support multiple, simultaneous translations of the same object in different languages. Setting the Language property works, but it also impacts processing and queries, which could have unintended consequences. Setting Locale Identifier is the better choice because it's only used to return translated strings.

A translation consists of a locale identifier (LCID), a translated caption for the object (for example, the dimension or attribute name), and optionally, a binding to a column that provides data values in the target language. You can have multiple translations, but you can only use one for any given connection. There is no theoretical limit on the number of translations you can embed in model, but each translation adds complexity to testing, and all translations must share the same collation, so when designing your solution keep these natural constraints in mind.

Tip

You can use client applications such as Excel, Management Studio, and SQL Server Profiler to return translated strings. See Globalization Tips and Best Practices (Analysis Services) for details.

Setting up a model to support translated members

A data model used in a multi-lingual solution needs more than translated labels (field names and descriptions). It also needs to provide data values that are articulated in various language scripts. Achieving a multi-lingual solution requires that you have individual attributes, bound to columns in an external database that return the data.

Adventure Works sample databases (multidimensional as well as the relational data warehouse) demonstrate the translation feature. The sample model includes translated captions and descriptions. The sample relational data warehouse contains columns of translated values that provide localized attribute members in the model.

To view translated data values available to the model:

Open the Adventure Works multidimensional model in the designer.
In Solution Explorer, open Data Source Views and double-click Adventure Works DW<version>.dsv.
Find dimDate, dimProduct, dimProductCategory, or dimProductSubcateogry. All of these dimensions contain attributes for translated members for month, day of week, product name, category name, and so on.
Right-click any field and select Explore Data. You will see English, Spanish, and French translations of each member.

Formats for date, time, and currency are not implemented through translations. To dynamically provide culturally specific formats based on the client's locale, use the Currency Conversion Wizard and FormatString property. See Currency Conversions (Analysis Services) and FormatString Element (ASSL) for details.

Lesson 9: Defining Perspectives and Translations in the Analysis Services Tutorial will walk you through the steps for creating and testing translations.

Defining Translations

Defining a translation creates a Translation object as a child of the Analysis Services database, dimension, or cube object. Use SQL Server Data Tools - Business Intelligence to open the solution and define translations.

Add translations to a cube

You can add translations to the cube, measure groups, measures, cube dimension, perspectives, KPIs, actions, named sets, and calculated members.

In Solution Explorer, double-click the cube name to open cube designer.
Click the Translations tab. All objects that support translations are listed in this page.
For each object, specify the target language (resolves internally to an LCID), translated caption, and translated description. The language list is consistent throughout Analysis Services, whether you are setting the server language in Management Studio, or adding a translation override on a single attribute.

Remember that you cannot change the collation. A cube essentially uses one collation, even if you're supporting multiple languages through translated captions (there is an exception for dimension attributes, discussed below). If the languages won't sort properly under the shared collation, you will need to make copies of the cube just to accommodate your collation requirements.
Build and deploy the project.
Connect to the database using a client application, such as Excel, modifying the connection string to use the locale identifier. See Globalization Tips and Best Practices (Analysis Services) for details.

Add translations to a dimension and attributes

You can add translations to database dimensions, attributes, hierarchies, and levels within a hierarchy.

Translated captions are added to the model manually using your keyboard or copy-paste, but for dimension attribute members, you can obtain translated values from an external database. Specifically, the CaptionColumn property of an attribute can be bound to a column in a data source view.

At the attribute level, you can override collation settings, for example you might want to adjust width-sensitivity or use a binary sort for a specific attribute. In Analysis Services, collation is exposed where data bindings are defined. Because you are binding a dimension attribute translation to a different source column in the DSV, a collation setting is available so that you can specify the collation used by the source column. See Set or Change the Column Collation for details about column collation in the relational database.

In Solution Explorer, double-click the dimension name to open dimension designer.
Click the Translations tab. All dimension objects that support translations are listed in this page.

For each object, specify target language (resolves to an LCID), translated caption, and translated description. The language list is consistent throughout Analysis Services, whether you are setting the server language in Management Studio, or adding a translation override on a single attribute.
To bind an attribute to a column providing translated values:
1. Still in Dimension Designer | Translations, add a new translation. Choose the language. A new column appears on the page to accept the translated values.
2. Place the cursor in an empty cell adjacent to one of the attributes. The attribute cannot be the key, but all other attributes are viable choices. You should see a small button with a dot in it. Click the button to open the Attribute Data Translation Dialog Box.
3. Enter a translation for the caption. This is used as a data label in the target language, for example as a field name in a PivotTable field list.
4. Choose the source column that provides the translated values of attribute members. Only pre-existing columns in the table or query bound to the dimension, are available. If the column does not exist, you need to modify the data source view, dimension, and cube to pick up the column.
5. Choose the collation and sort order, if applicable.
Build and deploy the project.
Connect to the database using a client application, such as Excel, modifying the connection string to use the locale identifier. See Globalization Tips and Best Practices (Analysis Services) for details.

Add a translation of the database name

At the database level, you can add translations for the database name and description. The translated database name might be visible on client connections that specify the LCID of the language, but that depends on the tool. For example, viewing the database in Management Studio will not show the translated name, even if you specify the locale identifier on the connection. The API used by Management Studio to connect to Analysis Services does not read the Language property.

In Solution Explorer, right-click project name | Edit Database to open the database designer.
In Translations, specify target language (resolves to an LCID), translated caption, and translated description. The language list is consistent throughout Analysis Services, whether you are setting the server language in Management Studio, or adding a translation override on a single attribute.
In the Properties page of the database, set Language to the same LCID you specified for the translation. Optionally, set the Collation as well if the default no longer makes sense.
Build and deploy the database.

Resolving Translations

If a client application requests a locale identifier, the Analysis Services instance attempts to resolve data and metadata for Analysis Services objects to the closest matching LCID. If the client application does not specify a default language, or specifies the neutral locale identifier (0) or process default language identifier (1024), then Analysis Services uses the default language for the instance to return data and metadata for Analysis Services objects.