Multiple Language Support for Cubes

Overview

OLAP cube data is often generated in one language and queried in other languages. For example, a company's OLAP cube data might be stored in English, but users who speak Spanish and Turkish need access to it. So, the member values as well as the captions that are assigned to dimensions, hierarchies, levels, and so on, need to be translated. Multiple language support is available only for cubes that are loaded from star schemas.

Multilingual cubes are created with tables of translated dimension data, with one table for each locale. Translated captions are listed in another set of translated tables, again one for each locale. In the OLAP procedure, the multilingual caption tables can be defined for the cube, or for individual dimensions. In SAS OLAP Cube Studio, you create multilingual cubes in the Cube Designer wizard.

SAS OLAP Cube Studio and Dimension Table Translations

In the Cube Designer – General page, select the Advanced button. If you selected Star Schema as the input type, you will see the Dimension Table Translations tab. From the Available list, select the needed languages for the translation tables and move it to the Selected list. The first language in the Selected list is the default language.

Once you have selected languages for your cube, an additional page will be displayed in the Cube Designer, the Cube Designer – Translated Caption Tables page. On this page, you can indicate the data tables that contain translated captions. Specifically, you can select a single table for the cube or separate tables for separate dimensions. MLSID values connect translated captions to their respective cube elements, such as dimensions and measures.

PROC OLAP Syntax for Multilingual Cubes

The OLAP procedure uses the following language elements to implement multilingual cubes:

USER_DEFINED_TRANSLATIONS statement

specifies a list of the locales that are available for translated dimension tables and caption tables.

Muiltilingual cube options on the PROC OLAP statement:

DTLIBREF specifies a library for translated drill-through tables.

DTMEMPREF specifies the prefix of the translated drill-through table names.

DTMEMPREFOPTS asserts data set options that are applied when you open translated drill-through tables.

CUBETABLELIBREF identifies a library of translated caption tables.

CUBETALBECAPPREF identifies the prefix of the translated caption table names.

MLSID identifies the row in the caption tables that contain the translated cube description. (Cubes do not have captions.)

Note: If captions and descriptions for measures are to be translated, the MLSIDs and related captions must appear in the cube’s caption table. In this circumstance, the CUBETABLECAPPREF option is required.

Multilingual options on the DIMENSION statement enable different translations in different dimensions of the cube:

DIMTABLELIBREF specifies a library for translated dimension and/or caption tables.

DIMTABLEMEMPREF specifies the prefix of the translated dimension table names.

DIMTABLECAPPREF identifies the prefix of the translated caption table names.

MLSID identifies the row in the caption tables that contain the translated dimension caption.

Note: The MLSID option is valid in any statement that provides a caption, including the HIERARCHY, LEVEL, PROPERTY, and MEASURE statements.

For more information on PROC OLAP syntax, see Overview: OLAP Procedure.

MLSIDs specified on the PROC OLAP statement, the MEASURE statement and the DEFINE statement require the use of CUBETABLECAPPREF. Other MLSIDs are associated with the DIMTABLECAPPREF if there is one for that specific dimension. Any MLSIDs that are not associated with a dimension require the use of CUBETABLECAPPREF.

Structure and Naming of Translated Dimension Tables

The languages that are supported by a multilingual cube are specified in the USER_DEFINED_TRANSLATIONS statement. Locale names are specified in 5-character Posix notation. Your multilingual cube can support any of the locales that are valid for the SAS system option LOCALE=, as listed in the SAS 9.3 National Language Support (NLS): Reference Guide.

The Posix naming convention for locales is also used in the names of the translated dimension tables. Dimension tables share a common base name. The base name is specified by DIMTABLEMEMPREF. The base name, without a suffix, identifies the dimension table for the default language. The dimension tables for the other languages are named with the base name plus the Posix locale name. For example, if you have a dimension table named CUSTOMERS_, and if English is the default language, then the translated copies of that dimension table could be named CUSTOMERS_de_DE (German), CUSTOMERS_es_AR (Spanish as used in Argentina), and CUSTOMERS_ja_JP (Japanese).

For each translated dimension, you need to create one translated dimension table for each locale that you specify in the USER_DEFINED_TRANSLATIONS statement.

Structure and Naming of Translated Caption Tables

Translated caption tables share the same naming requirements as translated dimension tables. You need at least one translated caption table for each language in your USER_DEFINED_TRANSLATIONS statement.

Within your translated caption tables, captions are referenced by a numeric MLSID. as shown in the following example.

The SAS System                                                                                  1
 
Obs    mlsid           caption          description
 
  1    1               Customer         CustomerDescription
  2    2               Customer         CustomerHierDescription
  3    3               custRegion       CustomerRegionLevelDescription
  4    4               custState        CustomerStateLevelDescription
  5    5               custCity         CustomercityLevelDescription
  6    6               custName         CustomerNamePropertyDescription
  7    7               custGender       CustomerGenderPropertyDescription
  8    8               custEducation    customerEducationPropertyDescription
  9    9               custmvhhcod      customerMVHHCODEPropertyDescription
 10    10              custNkids        customerNKIDSPropertyDescription
 11    11              custMarried      customerMaritalStatusPropertyDescription

The preceding example shows the columns that are required in translated caption tables. Any translated copies of the preceding table would apply the same MLSID to the same translated caption. MLSID values can be assigned to any cube object that can receive a caption.

You can structure your caption tables in three ways:

Cube

For the entire cube, you can use a single set of translated caption tables for all translated dimensions. To implement this scenario, specify a value for the PROC OLAP statement option CUBETABLECAPPREF. Do not specify values for DIMTABLECAPPREF in your dimension statements. Any caption that is not identified by an MLSID will appear in the default language.

Dimension

To apply translated caption tables within a single dimension, create translated caption tables that apply to that dimension only. Specify a value for DIMTABLECAPPREF and do not specify a value for CUBETABLECAPPREF.

Cube and Dimension

To combine generic captions with dimension-specific captions, specify both CUBETALBECAPPREF and DIMTABLECAPPREF. MLSIDs on dimensions that have not specified a DIMTABLECAPPREF will refer to observations in the tables specified by CUBETABLECAPPREF.

The following table illustrates the how translated caption tables are defined and named at the cube level (PROC OLAP statement using CUBETABLECAPPREF) and at the dimension level (DIMENSION statement using DIMTABLECAPPREF).

Naming Convention for Translated Caption Tables

Locale	Default	Dimension Data Sets
English	No	ctimedim_en_US	cardim_en_US	dealdim_en_US
Japanese	No	ctimedim_ja_JP	cardim_ja_JP	dealdim_ja_JP
Polish	Yes	ctimedim_	cardim_	dealdim_

SAS Servers and Character Encoding

If your server metadata contains characters other than those typically found in English, then you must be careful to start your server with an ENCODING= or LOCALE= system option that accommodates those characters. For example, a SAS server started with the default US English locale cannot read metadata that contains Japanese characters. SAS will fail to start and log a message indicating a transcoding failure.

In general, different SAS jobs or servers can run different encodings (such as ASCII/EBCDIC or various Asian DBCS encodings) as long as the encoding that is used by the particular job or server can represent all the characters of the data being processed. In the context of server start up, this requires that you review the characters used in the metadata describing your server (as indicated by the SERVER= object server parameter) to ensure that SAS runs under an encoding that supports those characters.

Single Encoding Required for Cube and All Translated Tables

All of the translated dimension and caption tables for a given multilingual cube must be created with an encoding that applies to all of the translated languages. For example, if a cube supports the English, French and German locales, then the tables can use the Latin1 encoding. A more generic encoding is necessary for a more divergent set of supported languages. For example, a cube that supports the English (Latin1), Polish (Latin2), and Japanese (Shift JIS) locales, the cube and all tables need to use the encoding UTF-8, which is common to all three languages.

If table encodings differ when the cube is built, then all drill-through options are ignored. The cube will be built without drill-through capability.