Supporting Multilingual Data
Oracle BI Server supports several language translations.
This section describes how you can configure the Oracle BI Server to display field information in multiple languages, and contains the following topics:
-
Designing Translation Lookup Tables in a Multilingual Schema
-
Supporting Multilingual Data in Essbase Through Alias Tables
For information about using the Administration Tool, see Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
What is Multilingual Data Support?
Multilingual data support is the ability to display data from database schemas in multiple languages.
Oracle BI Server supports multilingual schemas by simplifying the administration and improving query performance for translations. Multilingual schemas typically store translated fields in separate tables called lookup tables. Lookup tables contain translations for descriptor columns in several languages, while the base tables contain the data in the base language. Descriptor columns provide a textual description for a key column where there is a logical one-to-one relationship between the descriptor column and the key column. An example of a descriptor column might be Product_Name, which provides textual descriptions for a Product_Key column.
What is Lookup?
Lookup is when a query joins the base table and lookup table to obtain the translated values for each row in the base table.
Lookup tables might be dense and sparse in nature. A dense lookup table contains translations in all languages for every record in the base table. A sparse lookup table contains translations for only for some records in the base tables. Sometimes it is also possible that lookup tables are both dense and sparse. For example, a lookup table might contain complete translation for the Product Description field but only partial translation for the Product Category field. Dense and Sparse are types of lookup operation rather than being a table property. You configure lookup tables using the Administration Tool.
What is Double Column Support?
Double column support is the ability to associate two columns (a descriptor ID column and a descriptor column) in the logical layer, and can help you to define language independent filters.
When the user creates a filter based on a descriptor column, the query tool displays a list of values to the user that are selected from the descriptor column.
This descriptor column technique is also useful when dealing with queries that involve LOB data types such as CLOBs and BLOBs and aggregate functions such as COUNT or SUM. Some data sources do not allow LOB columns to be used in the GROUP BY clause. So, instead of adding the LOB column to the GROUP BY, it is necessary to group by some other column that has a one-to-one relationship with the LOB column and then in join the LOB column after the aggregates have been computed.
Designing Translation Lookup Tables in a Multilingual Schema
There are two common techniques of designing translation lookup tables in a multilingual schema as follows:
A Lookup Table for Each Base Table
There is often a separate lookup table for each base table. The lookup table contains a foreign key reference to records in the base table, and contains the values for each translated field in a separate column.
Assuming a completely dense lookup table, the number of rows in the lookup table for a particular language equals the number of rows in the base table.
The example in the figure below shows each record in the lookup table matching only one row in the base table.
A Lookup Table for Each Translated Field
The alternative approach to having one lookup table for each base table involves a separate lookup table for each translated field, as shown in the figure below.
Getting the translated value of each field requires a separate join to a lookup table. In practice there is often just one physical table that contains translations for multiple fields. When a single table contains translations for multiple fields, you must place a filter on the lookup table to restrict the data to only those values that are relevant to a particular column in the base table.
Creating Logical Lookup Tables and Logical Lookup Columns
This section describes creating logical lookup tables and lookup columns and contains the following topics:
Creating Logical Lookup Tables
You create a logical lookup table object in the business model to define the necessary metadata for a translation lookup table.
A lookup table is a logical table with a property that designates it as a lookup table, as described in Designating a Logical Table as a Lookup Table. The figure below provides an example of a lookup table.
-
Each of the lookup table's primary keys are considered together as a Lookup Key and perform the lookup. The lookup can be performed only when the values for all lookup key columns are provided. For example, in the figure above, the combined Product_Code and Language_Key form the primary key of this lookup table.
-
A lookup key is different from a logical table key because lookup key columns are order sensitive. For example, Product_Code and Language_Key are considered a different lookup key to Language_Key and Product_Code. You can specify the order of lookup key columns in the Administration Tool. All columns of the lookup key must be joined in the lookup function.
-
A lookup table has only one lookup key.
-
A lookup table has at least one value column. In the figure above, the value column is Description, and it contains the translated value for the product description.
-
There must be a functional dependency from a lookup key to each value column. In other words, the lookup key can identify the value column. The lookup key and value column should both belong to the same physical table.
-
A lookup table is standalone without joining to any other logical tables.
Consistency checking rules are relaxed for lookup tables, such that if a table is designated as a lookup table, it need not be joined with any other table in the subject area (logical tables would normally be joined to at least one table in the subject area).
-
The aggregation results when using lookup columns should match the results from the base column. For example, the following code
SELECT product.productname_trans, sales.revenue FROM snowflakesales;
should return the same results as
SELECT product.productname, sales.revenue FROM snowflakesales;
If the lookup table productname_trans in this example uses the lookup key ProductID and LANGUAGE, then both queries return the same aggregation results.
If the lookup key contains a column with a different aggregation level to productname, then the query grain changes and this affects the aggregation.
Designating a Logical Table as a Lookup Table
A logical table must be designated as a lookup table (using the Administration Tool) before you can use it as a lookup table.
To designate a logical table as a lookup table, you must first import the lookup table into the physical layer and drop it into the Business Model and Mapping layer using the Administration Tool. Then, for each logical lookup table, you must select the Lookup table option in the Logical Table dialog.
The order in which the columns are specified in the lookup table primary key determines the order of the corresponding arguments in the LOOKUP function.
For example, if the lookup table primary key consists of the RegionKey, CityKey, and LanguageKey columns, then the matching arguments in the LOOKUP function must be specified in the same order. You use the Administration Tool to change the order of primary key columns.
About the LOOKUP Function Syntax
A LOOKUP function is typically used in the Business Model and Mapping layer, as an expression in a translated logical table column.
The syntax of the LOOKUP function is as follows:
Lookup ::= LookUp([DENSE] value_column, expression_list ) | LookUp(SPARSE value_
column, base_column, expression_list )
expression_list ::= expr {, expression_list }
expr ::= logical_column | session_variable | literal
For example:
LOOKUP( SPARSE SnowflakeSales.ProductName_TRANS.ProductName, SnowflakeSales.Product.ProductName, SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE")) LOOKUP( DENSE SnowflakeSales.ProductName_TRANS.ProductName, SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE"))
Note the following:
-
A
LOOKUPfunction is either dense or sparse, and is specified using the keywordDENSEorSPARSE. The default behavior is dense lookup, if neitherDENSEorSPARSEis specified. ForDENSElookup, the translation table is joined to the base table through an inner join, while forSPARSElookup, a left outer join is performed. -
The first parameter (the parameter after the
DENSEorSPARSEkeyword) must be a valid value column from a valid lookup table that is defined in the logical layer. -
If the
SPARSEkeyword is given, then the second parameter must be a column that provides the base value of the value_column. ForDENSElookup, this base column is not required. -
The number of expressions in the expression_list must be equal to the number of the lookup key columns that are defined in the lookup table, which is defined by the value_column. The expression that is specified in the expression list must also match the lookup key columns one by one in order.
For example:
-
The lookup key for lookup table ProductName_TRANS is both Product_code and Language_Key
-
The expressions in expression_list are SnowflakeSales.Product.ProductID and VALUEOF(NQ_SESSION."LANGUAGE")
-
The meaning of the lookup is:
return the translated value of ProductName from the translation table with the condition of Product_code = SnowflakeSales.Product.ProductID and Language_Key = VALUEOF(NQ_SESSION."LANGUAGE")
-
Creating Logical Lookup Columns
You use the Expression Builder in the Administration Tool to create a logical column that includes the lookup function.
The value of the logical column depends on the language that is associated with the current user.
You create a new logical column using a derived column expression in the Column Source tab, for example to get the translated product name:
LAN_INT is a session variable that is populated by the session initialization block MLS and represents either the base language or other languages:
-
0 for base language (for example, en - English)
-
1 for other language codes (for example, fr - French, or cn - Chinese)
WEBLANGUAGE is a session variable that is initialized automatically, based on the language selected when a user logs in.
The INDEXCOL function helps to select the appropriate column. In the preceding example, the expression returns the value of the base column (ProductName) only if the user language is the base language (that is, when the value of session variable LAN_INT is 0). If the user language is not the base language (when the value of the session variable LAN_INT is 1), then the expression returns the value from the lookup table of the language that is passed in the WEBLANGUAGE session variable.
When you use the DENSE function (shown in the previous example), if there is no value for a column in the translated language, then the lookup function displays a blank entry.
When you use the SPARSE function (shown in the following example), and there is no value for a column in the translated language, then the lookup function displays a corresponding value in the base language.
INDEXCOL( VALUEOF(NQ_SESSION."LAN_INT"), "Translated Lookup Tables"."Product". "ProductName", LOOKUP( SPARSE "Translated Lookup Tables"."Product Translations". "ProductName", "Translated Lookup Tables"."Product"."ProductName", "Translated Lookup Tables"."Product"."ProductID", VALUEOF(NQ_SESSION."WEBLANGUAGE")))
Tips for Using Derived Logical Columns
Using derived logical columns requires planning to reduce errors.
When working with logical lookup columns, keep the following tips in mind:
-
You cannot use a derived logical column that is the result of a
LOOKUPfunction as part of a primary logical level key. This limitation exists because theLOOKUPoperation is applied after aggregates are computed, but level key columns must be available before the aggregates are computed because they define the granularity at which the aggregates are calculated.You can use a derived logical column that is the result of a
LOOKUPfunction as a secondary logical level key. -
For a derived logical column that has lookup functions in its derived expression:
-
The logical columns used in the lookup function should not have their associated levels below the level of the derived logical column itself.
-
Configuring a descriptor ID column is the recommended approach.
-
Handling Non-ISO Type Language Codes
If the data has non-ISO type language codes in the tables, then there should be a table that maps ISO language codes to non-ISO language codes.
You can use the preexisting WEBLANGUAGE variable that sets the ISO language code when a user logs in. You define a separate LANGUAGE variable whose initialization block runs a query against a mapping table to fetch the non-ISO language code filtered by the value from the WEBLANGUAGE variable. The table below provides a mapping table for non-ISO language codes. LANGUAGE is a non-ISO language code.
| WEBLANGUAGE | LANGUAGE | LAN_INT |
|---|---|---|
|
en |
ENG |
0 |
|
cn |
CHI |
1 |
|
fr |
FRA |
1 |
Creating Physical Lookup Tables and Physical Lookup Columns
You can create physical lookup table objects in the business model to define the necessary metadata for translation lookup tables. Physical lookup tables are similar to logical lookup tables in both semantics and usage.
Physical lookup tables address the following scenarios that logical lookup tables cannot handle:
-
The lookup table source is fragmented. In this case, use multiple physical lookup tables to hold the values. For example, translation values for fragmented product name data can be distributed in two physical lookup tables called productname_trans_AtoM and productname_trans_NtoZ.
-
Different levels of translation tables are available. For example, translations are available in both an Essbase data source and a relational data source. It is preferable to use the same source as the base query.
Unlike logical lookup tables, which you designate by selecting an option in the Logical Table dialog, you configure physical lookup tables by constructing lookup functions in the logical table source mapping.
For example, suppose that you have the following physical tables:
-
A base table called Categories, with columns such as categoryid and categoryname.
-
A translation table called Categories_Trans, with columns such as categoryid, language_key, and categoryname. The translated value of categoryname is determined through a combination of the categoryid and language_key columns.
Suppose that you have a logical table called Categories. In that table, you add a new logical column called categoryname_p, which is a translation column that depends on the current language. The column is not derived from any other logical column (unlike logical lookup columns).
The following procedure explains how to configure a physical lookup translation column using the previous example.
The Categories_trans physical translation table does not need to be incorporated into the logical table source. The INDEXCOL function checks that if the LAN_INT session variable is 0, then the categoryname column is fetched from the base table. Note the following about the LOOKUP function:
-
The physical
LOOKUPfunction works the same as a logicalLOOKUPfunction. The only difference is that all the references to logical tables and columns are replaced by physical tables and columns. -
The first column of the
LOOKUPfunction is a value column, which is a translation value column from a translation table. The second column is the base value column, if a sparse lookup exists. The remaining columns are columns or values to be joined to the physical translation table, which is the table that is implied by the value column of theLOOKUPfunction.
Because you cannot use a dialog to configure a physical lookup table, you must ensure that the order of the join columns and values is compatible with the column sequence that is displayed in the Physical Table dialog for the physical translation table. For example, on the Keys tab of the Physical Table dialog for the Categories_trans table, the primary key is composed of the CategoryID and Language_Key columns.
The columns that are specified in the LOOKUP function correspond to these columns:
-
The following line:
"DB_Name"."My_Category"."My_Schema"."Categories"."CategoryID"
corresponds to the Categories_trans.CategoryID column.
-
The following line:
valueof(NQ_SESSION."LANGUAGE")
corresponds to the Categories_trans.Language_key column.
See Creating Logical Lookup Tables and Logical Lookup Columns for information about lookup concepts like the LAN_INT and LANGUAGE session variables and full syntax information for the LOOKUP function.
Supporting Multilingual Data in Essbase Through Alias Tables
Often, members in Essbase cubes have separate aliases for each user language to enable users to view member names in their own language.
Typically, you define a session variable to dynamically select the appropriate alias upon user login. See Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about Essbase alias tables and how to use them with session variables.
Enabling Lexicographical Sorting
Lexicographical sorting is the ability to sort data in alphabetical order.
Most data sources support lexicographical sorting. However, if you notice that lexicographical sorting is not working properly for a particular data source, then you can configure the Oracle BI Server to perform the sort rather than the back-end data source. To perform this configuration, ensure that ORDERBY_SUPPORTED is not selected in the Features tab of the Database dialog in the Administration Tool. See Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about specifying database features.
Note that disabling ORDERBY_SUPPORTED in the data source can have a very large performance impact, because consequently, many joins are not pushed down to the data source. In many cases, the performance impact is significant enough that ORDERBY_SUPPORTED can still be enabled in the data source, regardless of the impact on the lexicographical sorting functionality.