Analysis Services Translations

SQL Server Analysis Services (SSAS) provides an easy translation mechanism for a richer end-user experience. The mechanism enables the translation of captions from dimensions and measures as well as the translation of the content in dimension columns.

To easily show the cube in his or her preferred language the end-user has to use a client application such as MS-Excel that can make use of SSAS Translations. When the translation mechanism has been set up in a cube with the same language as the display language in MS-Excel, the measures and dimensions will be translated automatically in the worksheet.

MS-Office language packs

Some countries have more than one official language. In Belgium, for instance, there are 3 official languages: Dutch, French and German. Many reports need to be distributed in all 3 languages. Companies and institutions quite often have to install MS-Office in the native language of all of their employees. With multiple language packs installed for MS-Office it is possible to change the display language. By changing the display language a user can generate the same MS-Excel report in different languages, automatically translated by the SSAS cube.

Providing translations in SSAS

Dimension tables have a key and name column. Additional name columns have to be added for every language that we need to generate reports in.

Set up dimension tables with different languages in SQL-Server

Every dimension table that has name columns to display the dimension members, should have additional name columns for every language end users request. There is always a default name column.

Example

Let's take a very basic dimension table called DimBlocking, with an integer id as key and a name column of type nvarchar.

CREATE TABLE [dbo].[DimBlocking](
[BlockingId] [int] NOT NULL,
[BlockingName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_DimBlocking] PRIMARY KEY CLUSTERED
(
[BlockingId] ASC
)

The name column ‘BlockingName’, will serve as the default name column in the SSAS dimension specification. The table has 2 rows of data:

To provide for other languages like Dutch and French, just add name columns of the same type and size as the default name column, e.g. BlockingNameNL for Dutch and BlockingNameFR for French.

CREATE TABLE [dbo].[DimBlocking](
    [BlockingId] [int] NOT NULL,
    [BlockingName] [nvarchar](50) NOT NULL,
    [BlockingNameNL] [nvarchar](50) NOT NULL,
    [BlockingNameFR] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_DimBlocking] PRIMARY KEY CLUSTERED([BlockingId] ASC)

The new version of the table after adding data to the translation columns:

Set up dimension translations for different languages in SSAS

I have used Visual Studio to create an Analysis Services multidimensional project. One data source is for the database with the dimension table DimBlocking.

Data Source View

Make sure that the Data Source View is set up to read the dimension metadata with every translated name column. If your dimension table is based on a query, then adapt the query to read the new name columns. In the example the table DimBlocking has been given the Friendly name ‘Blocking’. It contains the standard name column (BlockingName) and 2 translated name columns (NL for Dutch and FR for French)

Dimension Designer – Structure

When we add a new dimension the Dimension Wizard will ask for a key column and name column.

In the dimension ‘Blocking’ the Key Column is 'BlockingId' and the Name Column is 'BlockingName'.

Once the wizard has created the dimension, the key column and name column can be seen in the DimensionAttribute properties.

Dimension Designer – Translations

Select the Translations tab to go to the translation design.

In the Translations design, we can add translations for the Captions, the Attributes as well as for the [All] and [Unknown] members.

Preparation to translate [All] and [Unknown] members

The captions of these 2 members appear by default when browsing a dimension: 

To make [All] and [Unknown] translatable we must explicitly specify them in the Dimension properties.

In the Dimension Structure design, select the Dimension in the Attributes Pane, and look for the following 2 Dimension properties:

• AttributeAllMemberName: set this to ‘All’ to enable translation. (Or use another word, e.g. Everything)

• UnknownMemberName: set this to ‘Unknown’ to enable translation (Or use any other word, like ‘Unidentified’)

Adding Translations

In the Dimension Designer select the Translations tab again. The Dimension properties that have translatable captions:

• Caption
• AttributeAllMemberName
• UnknownMemberName.

And for the Attributes:

• Caption

Click on the ‘Add new translation’ button. 

The Select language dialog box opens: 

Select the language needed and make sure you have an exact match with the display language installed in MS-Office. E.g. choose ‘Dutch (Netherlands)’ when the Office 2013 language pack for ‘Nederlands (Nederland)’ is installed. This may sound confusing, but because I am using a Dutch Office 2013 installation, the display language (Dutch (Netherlands)) is shown translated as well by MS-Office.

For the Dimension Caption and Member names, type text in the Translation column. For the Dimension caption, that will be ‘Bloquant’ in French and ‘Blokkerend’ in Dutch.

Translating the data in the name column

In the Attributes section, there is not only option to  translate the caption, but also the option to select another column name from the dimension table. Here you can select a column name to use as translation for the attribute data. Click inside the translation cell in the attribute row ‘Blocking’ and the French column and you see an ellipsis button. Click on the button to open the Attribute Data Translation dialog window. Enter the Translated caption and select the Translation column from the dimension table.

Select BlockingNameFR, click OK and repeat this step for the Dutch translation.

Save and deploy this dimension, then process it to make it ready for use by MS-Excel.

Setup MS-Excel to use the different languages in the cube

By default MS-Excel is installed with one Display language. This can be verified in the Options dialog window of MS-Excel, under the Language tab. In the screenshot below, Office 2013 was installed with the English Display language.  Look under ‘Choose Display and Help Languages’.

Install additional language packs from an Office language pack as needed. There are a few considerations when making translations for a country like Belgium where ‘Dutch (Belgium)’ is installed as Windows language, but the language pack for Office 2013 only offers Dutch from The Netherlands: ‘Nederlands (Nederland)’. Take care to setup SSAS translations to match the name of the Display language and not of the Editing language.

Connect MS-Excel to the SSAS cube

The Dutch version of MS-Excel is setup to use ‘Nederlands’ as display language:

Show the cube in the display language.

In the spreadsheet, connect to the Analysis Services Cube and create a new pivot table. The Pivot table fields shows the Blocking dimension translated. 

After selection of this dimension in the Rows section, the pivot table shows the Attribute data translated in Dutch.

Excel row 2, 3 and 4 show the translated members of the ‘Blocking’ attribute. MS-Excel automatically translates ‘Row-labels’ and ‘Grand total’ as ‘Rijlabels’ and ‘Eindtotaal’. This is not a function of the SSAS translation.

Select another display language

Change the Display language to French. Close and restart MS-Excel. Open the same spreadsheet. In the Data section, click on the Refresh button. The pivot table is now shown with French captions and values.

Conclusion

Once translations are setup in the Database tables and in SSAS, it is easy to generate reports in different languages with a client application that is able to use SSAS translations, such as MS-Excel 2013.