Warehouse Structure for Your Logical Data Model » Supporting data internationalization » Internationalization through tables and columns or databases

Internationalization through tables and columns or databases

MicroStrategy supports data internationalization through two different techniques. You can either provide translated data through the use of extra tables and columns, or you can provide separate databases to store your translated data. These techniques are described below:

Using tables and columns for internationalization
Using separate databases for internationalization

Using tables and columns for internationalization

You can support data internationalization in your database by using separate tables and columns to store your translated data. You can use various combinations of tables and columns to support and identify the translated data in your database.

For example, the MicroStrategy Tutorial project includes a Month of Year attribute which retrieves its primary information in English from the LU_MONTH_OF_YEAR table shown below:

To support displaying the name of each month in multiple languages, you can include the translated names in a separate column, one for each required language, within the same table. Each column can use a suffix to identify that the column contains translated data for a certain language. The same LU_MONTH_OF_YEAR table with translated data for the Spanish and German languages is shown below:

The data for Spanish is included in a MONTH_OF_YEAR_NAME column with the suffix _ES, and the data for German is included in a MONTH_OF_YEAR_NAME column with the suffix _DE.

As an alternative to supplying translations by using separate columns in the same table, you can create separate tables for your translations. Each table can share the same column name for the same data in different languages. In the tables below, the Spanish and German data is provided in separate Spanish and German tables:

The data for Spanish is included in a LU_MONTH_OF_YEAR table with the suffix _ES, but the MONTH_OF_YEAR column shares the same column name as in the English LU_MONTH_OF_YEAR table. The data for German uses the same technique and is stored in a LU_MONTH_OF_YEAR table with the suffix _DE.

You can also use both techniques (separate tables and extra columns in one table) to store and identify your translated data. This can be helpful to distinguish the language used for each table and column. It can also be helpful if you have a primary language stored in one table, and you store all internationalizations in an internationalization table. For example, you can store the Spanish and German data in the same internationalization table, as shown below:

In this scenario, the LU_MONTH_OF_YEAR_LANG table includes all translations in all languages other than the primary language, for the MONTH_OF_YEAR_NAME column. Each column is assigned a suffix to identify the language of the translated data.

In the examples above, suffixes on tables and columns are used to identify the language of the translated data. While it is not a requirement to use suffixes for these identification purposes, it is the easiest method to define and support in MicroStrategy. Using prefixes or other naming conventions requires you to use some functions to recognize the location of the translated data.
If your project supports data internationalization, you cannot use logical views as lookup tables for attributes that use translated data. For information on logical views, see Logical Tables.

For information on defining your project to use tables and/or columns to enable data internationalization, see Enabling data internationalization through SQL queries.

Using separate databases for internationalization

You can support data internationalization in your database by using separate databases for each supported language. A user can then be granted access, through connection mappings, to the database that contains their preferred language.

For example, the MicroStrategy Tutorial project includes a Month of Year attribute which retrieves its primary information in English from the LU_MONTH_OF_YEAR table shown below:

For the purposes of this example, you can assume this data is stored in a database named Tutorial (English). You also provide your projects in Spanish and German, which means you must have a database for Spanish and a database for German. Each database contains the same table structure, column structure, and naming conventions, but includes translated data, as shown below:

This method of data internationalization requires that the same data is available in each internationalized database.

If your project supports data internationalization, you cannot use logical views as lookup tables for attributes that use translated data. For information on logical views, see Logical Tables.

For information on defining your project to use separate databases to enable data internationalization, see Enabling data internationalization through SQL queries.