Oracle Business Intelligence Enterprise Edition 11.1.1.6.0 Multilingual Support – Part A –Localization of Oracle BI Repository Metadata

Global deployment of Oracle Business Intelligence requires multilingual support. It is very common to have reporting instance to support end users for to make decisions based on application and data presented in their own language. Oracle Business Intelligence Enterprise Edition 11.1.1.6.0 can be configured to support multilingual reporting requirement. Oracle BI provides the ability to localize data, repository metadata and reports/dashboard.

Oracle BI Multilingual Support

Oracle BI multilingual support requires following three types of configurations

·         Localization of Oracle BI Repository Metadata

·         Localization of Oracle BI Data

·         Localization of Reports and Dashboards

This blog is an attempt to focus on localization of Oracle BI repository metadata. I will try to cover the localization of Oracle BI data and localization of reports and dashboards in future blogs.

Localization of Oracle BI Repository Metadata

For localization of Oracle BI repository metadata uses translation table which stores language-specific names and descriptions, in conjunction with the initialization blocks and variables. Initialization blocks/variables and translation table configuration allows the user’s preferred language to display the correct metadata names and descriptions.

Session Variables – WEBLANGUGE

For WEBLANGUAGE session variable is populated with a two-character, lowercase language code when a user selects a language during login as shown below.

E.g. en for English

Configuration Setting for LOCALE

LOCALE –specified how data is returned from the server, it localizes message strings, such as names of days and months. The default LOCALE is specified in the NQSConfig.ini file.

[GENERAL]

# Localization/Internationalization parameters.

LOCALE = "english-usa"; 

SORT_ORDER_LOCALE = "english-usa"; 

SORT_TYPE = "binary";

Steps to Localize Metadata

For demonstration purpose for localization of Oracle BI metadata, I have chosen ‘Swedish’ language.

The steps to localize metadata are as follows.

Externalize metadata objects

The first step is to set display variables. For to set the display variable for each presentation objects (presentation table, presentation column or subject area) which requires translation, right click the object then select either Externalize Display Names > Generate Custom Names or Externalize Description > Generate Custom Description to externalize strings.

The externalization option automatically selects the custom display name or custom description options in the properties dialog box for the selected objects and its entire child objects as shown below. The object display name/description values change to variables, which are populated by an initialization block and displayed in a query user interface.

In this example the A-Sample Sales subject area display name is now populated by the session variable CN_A_-_Sample_Sales while description is now populated by the session variable CD_A_-_Sample_Sales.

Externalize String Utility

The next step is to run the Externalize String Utility. The utility exports the names of the presentation layer objects to a file in comma-separated value, tab-delimited, or XML format. The Externalize Strings utility is primarily for use by translators to translate presentation layer catalogs, table, column, and their descriptions into other languages.

Modifying the translation File

After running the Externalize Strings utility to generate a file, the next step is to modify the file to include language translations. You must add a row for each language and display variable. If there are 50 display variables and 10 languages, 500 rows are required.

In this example there are one English row and one Swedish row for each display variable.

The translation table has three required columns:

LANG_ID: Identifies the language. It is typically a two-character, lowercase code.

MSG_NUM: Identifies the display variable to translate. One MSG_NUM is required for each presentation catalog name, presentation catalog description table name, table description, column name and column description.  In this example the A-Sample Sales subject area display name is CN_A_-_Sample_Sales while description is CD_A_-_Sample_Sales.

 MSG_TXT : Contains the translated text

Load the translation table

Load SQL Loader to load the translation file into an existing table.

Load data

infile ‘A-Sample Sales.csv’

badfile ‘A-Sample Sales.bad’

discardfile ‘A-Sample Sales.dsc’

into table TRANSLATIONS

FIELDS TERMINATED BY ‘,’

(METADATA_OBJECT, MSG_NUM, MSG_TEXT, LANG_ID)

The METADATA_OBJECT column is used for identifying the object.

Import the translation table

Import TRANSLATIONS table into physical layer, this step is optional.

Create a dedicated connection pool

It is recommended that to create a dedicated connection pool for initialization blocks. This connection pool should not be used for queries. Additionally, it is recommended that you should isolate the connections pools for different types of initialization blocks. This is to make sure that authentication and login-specific initialization blocks do not slow down the login process.

The following types should have separate connection pools:

·         All authentication and login-specific initialization blocks such as language, externalized string and group assignments

·         All initialization blocks that set session variables

·         All initialization blocks that set repository variables

These initialization blocks should always be run using credentials with administrator privileges.

Create an initialization block

Create a row wise initialization block that populates session variables with values from the translation table based on the value of WEBLANGUAGE. The WEBLANGUAGE is a session variable that is initialized automatically, based on the language selected when a user logs in. WEBLANGUAGE is set to the language of the user’s browser when a user first logs in to an integrated Oracle BI application.

The row wise initialization block populates the MSG_NUM and MSG_TEXT session variables by using data from the TRANSLATIONS.

Verification of Implemented Translation

For verification of the translation, change the localization preference on My Account Page.