Support Multilingual Data
Oracle Analytics supports several language translations.
This section describes how you configure field information to display in multiple languages, and contains the following topics:
For information about using the Model Administration Tool, see Managing Metadata Repositories for Oracle Analytics Server.
What Is Multilingual Data Support?
Multilingual data support is the ability to display data from database schemas in multiple languages.
Oracle Analytics 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 Model 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 don't allow LOB
columns to be used in the GROUP BY
clause. So, instead of adding the
LOB column to the GROUP BY
, it's 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.
Design 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.
Create Logical Lookup Tables and Logical Lookup Columns
This section describes creating logical lookup tables and lookup columns and contains the following topics:
Create 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 Designate 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 Model 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.
Designate a Logical Table as a Lookup Table
A logical table must be designated as a lookup table (using the Model 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 Model 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 Model 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
LOOKUP
function is either dense or sparse, and is specified using the keywordDENSE
orSPARSE
. The default behavior is dense lookup, if neitherDENSE
orSPARSE
is specified. ForDENSE
lookup, the translation table is joined to the base table through an inner join, while forSPARSE
lookup, a left outer join is performed. -
The first parameter (the parameter after the
DENSE
orSPARSE
keyword) must be a valid value column from a valid lookup table that's defined in the logical layer. -
If the
SPARSE
keyword is given, then the second parameter must be a column that provides the base value of the value_column. ForDENSE
lookup, this base column isn't 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's 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")
-
Create Logical Lookup Columns
You use the Expression Builder in the Model 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's 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 isn't 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's passed in the
WEBLANGUAGE
session variable.
When you use the DENSE
function (shown in the previous example), if
there's 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 can't use a derived logical column that's the result of a
LOOKUP
function as part of a primary logical level key. This limitation exists because theLOOKUP
operation 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's the result of a
LOOKUP
function 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 shouldn't have their associated levels below the level of the derived logical column itself.
-
Configuring a descriptor ID column is the recommended approach.
-
Handle 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 |
Create 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 can't 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's 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've 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've 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 isn't 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 doesn't 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
LOOKUP
function works the same as a logicalLOOKUP
function. 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
LOOKUP
function 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's implied by the value column of theLOOKUP
function.
Because you can't 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's 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 Create 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.
Support 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 Managing Metadata Repositories for Oracle Analytics Server for information about Essbase alias tables and how to use them with session variables.
Enable 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 isn't 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 isn't selected in the Features tab of the Database dialog in the Model Administration Tool. See Managing Metadata Repositories for Oracle Analytics Server 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 aren't 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.