This article is an excerpt of a larger section regarding Translations in the upcoming book Tabular Modeling in Microsoft SQL Server Analysis Services

The entities of the semantic model defined by Analysis Services Tabular can be translated in other languages and cultures. Such a feature is available in models created at the 1200 compatibility level, and is not supported in previous compatibility levels. The feature available for Tabular models only translates object metadata, such as column, table, and measure names. However, this does not support loading translated data, such as translation of product names or descriptions. Such a feature is available in a Multidimensional model, even in previous versions of Analysis Services.

Upgrades from BIDS Helper

Even if translations were not supported in previous versions of Analysis Services Tabular, the model compatibility level 110x allows the creation of translations for metadata using the same technique used for Multidimensional models. Such a translation is only available for MDX clients, such as a pivot table in Excel, and for this reason it was used by some developer even if not supported by Microsoft. In order to make it easier to insert the translations in the XMLA content, the free community tool BIDS Helper provides a user interface to insert translated names in the data model. If you used such a feature, you will lose all the translations migrating the model to the 1200 compatibility level, and you have to create translations from scratch using the procedure described in this section, or you can migrate the existing translations using the procedure described by Daniel Otykier.

Creating and editing translation files

The translated metadata are included in the JSON file that contains the definition of the Tabular data model. These translations are in a specific section of the data model named cultures. Every object has a reference to the original element in the data model, and properties such as translatedCaption, translatedDescription, and translatedDisplayFolder.

SQL Server Data Tools (SSDT) does not provide a user interface to directly manipulate the translations in the Tabular model. Instead, you can export a file containing only the translations in one or more languages, and then you can import these files back to the model. The idea is that you initially export an “empty” translation file, which you pass to someone who is able to insert the translated names in the proper places. Once the translation file contains the translated names, then you import this file in the data model. SSDT provides tools to export and import translation files, requiring you the following steps:

  1. Create a translation file
  2. Edit translated names in translation file
  3. Import a translation file
  4. Test translations using a client tool

MSDN provides a detailed guide of the procedures in SSDT.

You can provide any text editor for translation files, you do not have to use Visual Studio for that. For example, an alternative to Visual Studio is Notepad++, which correctly manages the encoding of the files required for translations. Since several languages use non-ANSI characters, you have to make sure that the file is saved in Unicode UTF-8. This is the file type generated by the export language feature you have seen previously, and it is the encoding expected by the import feature described in the next section. If you use Visual Studio, you have to be aware that, by default, if a JSON file does not contain any ANSI character higher than 127, the file is then saved as a standard ANSI one, without using Unicode. Visual Studio controls this behavior in the Save with Encoding option of the Save File As dialog box, which displays the Advanced Save Options dialog box you see in the following screenshot.

F08xx26

Make sure you have the same Encoding settings for your translation file when you save it from Visual Studio, otherwise you will have a wrong conversion of many special characters used in specific languages.

Probably the JSON format used for translations files is not very user-friendly, but at least it is a text file and you can edit it with any text editor. However, it is relatively easy to load a JSON file in a program that provides a better user experience. You might want to write your own script to transform the JSON format in a simpler file to edit, or you might want to use some specific editor to manipulate such a file format. Kasper De Jonge created an initial release of a project named SSAS Tabular Translator, and I joined as a contributor, too. The user interface of this tool visible in the following screenshot.

F08xx27

Such a system makes it easier to see the original and translated names of each entity in a single row, increasing the productivity of managing the translation files. SSAS Tabular Translator also manages more languages included in the same translation file.

Testing translations

In order to test the translations, you have to use a client tool that is able to display translated metadata. For example, the PivotTable in Excel supports translations of the data model, and you can connect the PivotTable by enforcing a particular culture setting in the connection string, overriding the default culture that is inherited by default user settings. The easiest way to do that is to use the Analyze in Excel button in Visual Studio, selecting the Culture setting from the list of the available translations.

F08xx29

Please note that the translation file contains all the objects, regardless of their visibility state. When you navigate in the PivotTable, you can only see the visible objects, and not the hidden ones. While it is a good idea to translate also invisible objects, it is not strictly required for the user interface, because they should be never displayed to the user.

At the moment of writing, the only Microsoft client supporting translations in a Tabular model is Microsoft Excel. Power BI will probably support the translations of a Tabular model in a live connection in future release, but this is still not available at the moment of writing (July 2016 release).

Best practices using translations

When you manage translations, you export and import parts of the Tabular model in JSON format. Because of that, there are a few best practices you should follow in order to avoid problems in the project development and maintenance.

  • Export only one language in one file. Managing different languages in the same file can be complex, especially if you do not use specific editors.
  • Once you export the translation file, do not rename any object in the Tabular model. If the translation references an object that later you rename in the model, the following import operation will fail, and you have to apply the same rename to the translation file before the import, or you should restore the original name of the object in order to complete the import operation.
  • If you create new objects in the data model after you exported a translation file, these objects will not be translated once you import the translation. You have to export a new file, but the translation file contains all the objects, so you cannot merge partial translations and/or export only the new strings that require a translation. Because of these limitations, you should start the translation at the very end of the project lifecycle.
  • If you use the JSON editor in Visual Studio to edit the translation files, remember to save the translation file using the Unicode UTF-8 encoding.
  • Consider using specific editors for translation files, such as the SSAS Tabular Translator.