Designing Translations in Multidimensional Models

This topic provides a comprehensive guide to designing and implementing translations for multidimensional models in SQL Server Analysis Services (SSAS). Translating your model's metadata enables users to interact with the data in their preferred language, significantly enhancing usability and global reach.

Why Use Translations?

Multilingual support is crucial for applications and solutions used by a global audience. By providing translations for object names, attribute members, and other metadata, you ensure that users can understand and navigate the data intuitively, regardless of their native language.

Objects That Can Be Translated

In Analysis Services, you can translate the following metadata elements:

• Cubes
• Dimensions
• Hierarchies
• Attributes
• Measures and Measure Groups
• Perspectives
• Translations (of other objects)

Implementing Translations

Translations are managed within the SSAS project using the Multidimensional Model Designer.

Step 1: Enable Translations

Before you can add translations, you must enable the translation feature for your project:

1. Open your SSAS project in SQL Server Data Tools (SSDT).
2. In Solution Explorer, right-click the SSAS project and select Properties.
3. In the Project Properties dialog box, navigate to the Translations tab.
4. Click the Add button to add a new language.
5. Select the desired language from the dropdown list (e.g., French, German, Spanish).
6. Click OK. You can add multiple languages.
7. Click OK again to close the Project Properties dialog box.

Step 2: Add Translations for Objects

Once translations are enabled, you can add translated names and captions to specific objects:

For Cubes and Dimensions:

1. Open the Cube Designer or Dimension Designer.
2. In the designer's toolbar, locate the Translations pane (usually a dropdown or a button).
3. Select the language you want to translate into from the list.
4. The designer will now display text fields where you can enter the translated names for the selected object and its components (e.g., Cube Name, Dimension Name, Attribute Caption).
5. Enter the translated text for each applicable property.
6. Save your changes.

For Attribute Members:

Translating attribute members requires a slightly different approach, typically involving a separate translation table in your data source or specific configurations within the dimension designer.

You can add translations for attribute members by:

1. Opening the Dimension Designer for the relevant dimension.
2. Switching to the Translations tab within the dimension designer.
3. Here, you can select an attribute and provide translations for its members. This is often done by mapping to columns in your data source that contain the translated member names.

Step 3: Deploy and Test

After implementing your translations:

1. Deploy your SSAS project to the server.
2. Connect to the deployed database using a client tool that supports language settings (e.g., Excel PivotTables, Power BI, or a custom application).
3. Configure the client tool's language settings to the language you want to test.
4. Verify that all translated objects and member names are displayed correctly.

Considerations for Translation Management

• Consistency: Ensure consistent terminology across all translated objects.
• Localization: Consider cultural nuances, date formats, currency symbols, and number formats specific to each locale.
• Performance: While translations add flexibility, extensive translation requirements can sometimes impact processing and query performance if not managed efficiently.
• Maintenance: Establish a clear process for updating translations when the underlying model changes.

By carefully planning and implementing translations, you can create a truly global and user-friendly business intelligence solution with SQL Server Analysis Services.