This chapter includes the following sections:
Localization refers to the process of adapting the Oracle Business Intelligence deployment to a particular language.
If your users speak languages other than English, then use the information about localization to adapt your deployment to support multiple languages.
For information about supported languages, see System Requirements and Certification.
Several areas of the interface are translated into a user’s native language.
The following list outlines which components of Oracle Business Intelligence are translated into languages other than English:
Installer
Web user interface
Job Manager interface of the Oracle BI Scheduler
Catalog Manager
Oracle BI Presentation Services messages:
error
warning
information
Oracle BI Server functions:
DayName
MonthName
Note:
If a query is issued using the DayName or MonthName function, but the function is not shipped to a back-end database, then the day or month name is returned in the localized language but the column name remains in English (or might be affected by other localization controls). As an example of this situation, if the LOCALE parameter is set for German, the MonthName query returns the string "Mai" but the column header remains "Name of Month."Oracle BI Server and Oracle BI Scheduler messages:
error
warning
information
Log files:
obis1-diagnostic.log for Oracle BI Server
nqquery.log for Oracle BI Server
If Clustering is enabled, nQCluster.log for Oracle BI Server Cluster
Metadata:
Dashboards and analyses (Oracle BI Presentation Catalog)
Presentation table and column names (.rpd file)
Oracle BI Administration Tool interface
ODBC setup
The following list outlines which components of Oracle Business Intelligence are not localized:
ODBC client tools:
nqcmd (UNIX)
nqcmd.exe (Windows)
nqclient.exe (Windows)
Numerous Oracle Fusion Middleware components, such as Oracle WebLogic Server Administration Console and Fusion Middleware Control, are translated. See Oracle Fusion Middleware documentation for information.
As the administrator, you perform various tasks to localize Oracle BI Presentation Services.
Topics include:
You can localize the user interface for Oracle BI Presentation Services, if your users speak languages other than English.
Users can select a language on the sign-in page for Oracle BI EE, and many elements of the interface are automatically displayed in the appropriate language. After signing in, users can change the language setting on the Preferences tab of the My Account dialog.
The user's setting is stored in the WEBLANGUAGE session variable. For the Oracle BI Presentation Services user interface, WEBLANGUAGE is set when a user selects a language on the sign-in page.
Note:
For Oracle BI Applications, WEBLANGUAGE is set to the language of the user's browser when a user logs in for the first time. For example, if a user with a browser language set to French logs in to Oracle BI Applications for the first time, then the value for WEBLANGUAGE is French, and the metadata is translated to French.
As the administrator, you perform various tasks to localize other elements of the user interface for Oracle BI Presentation Services, as described in the following sections:
Oracle BI EE is installed with many files that control elements in the user interface and messages.
These files are installed in the messages and pages subdirectories of the ORACLE_HOME/bi/bifoundation/web/msgdb
directory. To localize these elements and messages, you copy those files to the l_xx subdirectories in SDD/service_instances/service1/metadata/content/msgdb/l_xx
where SDD is the Singleton Data Directory, and _xx indicates the language extension. See Key Directories in Oracle Business Intelligence. After you have copied the files, you can modify their contents as appropriate for the language that corresponds to the subdirectory in which you have copied them.
You can localize the messages that are associated with a preferred currency.
See Defining User-Preferred Currency Options Using a Static Mapping for information on working with users' preferred currencies.
In Oracle BI EE, the appropriate localized text is displayed to the user. In this example, the text is My Currency Text 1.
You can change the language the user sees by overriding the language specified by their browser.
The default language in which the Presentation Services sign-in page is displayed is obtained from the user's client browser settings. The following procedure explains how to change the language.
Note:
The following procedure uses Internet Explorer 7.0 as an example. If you are using a different browser, then make the necessary substitutions.You can configure the languages and locales that are available to users on the sign-in page.
This ability is helpful for limiting the number of languages and locales that users can access. You use the AllowedLanguages and AllowedLocales elements in the instanceconfig.xml file to specify the available languages and locales.
Ensure that you are familiar with the information in Configuration Files before you start.
In Presentation Services, you can use performance tiles to focus attention on a single piece of high-level aggregate data.
The tile can include a number such 1,000,000 or you can specify to "compress" or "scale" the value with an indicator such as 1M, for example.
To scale the number, Presentation Services searches for the scaling factors that the current locale allows, processes the number, and appends the indicator value. If no scaling factors are defined for the current locale, then no scaling is applied. Because the scaling of numbers differs by language, you can manually edit the localedefinitions.xml file to control the scaling, as described in the following procedure.
See Editing Performance Tile Views in User's Guide for Oracle Business Intelligence Enterprise Edition.
When users start Oracle BI EE by displaying the sign-in page, they can select the language as part of the sign-in process.
Users can also select a language on the Preferences tab of the My Account dialog.
If you provide users with a URL with which they can display a dashboard or other page of the application, then you can define a URL parameter as a profile attribute. Doing so dynamically sets the language of the dashboards and analyses to be consistent with the application's language setting.
For operational applications, symbolic URLs embed dashboards and analyses in the integrated environment. For Oracle BI Presentation Services, the URL parameter Lang designates the language that the web page renders.
The Lang parameter can be included in the symbolic URL that is defined in the operational application to connect to Oracle Business Intelligence. The Lang parameter is defined as a profile attribute, but when the symbolic URL is constructed at runtime, the value is set as the profile attribute LanguageCode. The next table provides examples of the parameter settings in the Symbolic URL parameters applet, including Lang.
For example, the following URL displays the sign-in page in French.
http://Server_Name:port_number/analytics/saw.dll?Dashboard&Lang=fr
Name | Type | Path Argument Value | Append | Sequence # |
---|---|---|---|---|
Cmd |
Constant |
Go |
Y |
1 |
Path |
Constant |
/shared/Sales/Pipeline/Overview/Top 10 Deals |
Y |
2 |
nQUser |
Command |
UseLoginId |
Y |
3 |
nQPassword |
Command |
UseLoginPassword |
Y |
4 |
PostRequest |
Command |
PostRequest |
Y |
5 |
Lang |
Profile Attribute |
LanguageCode |
Y |
6 |
The Oracle BI Presentation Catalog stores objects that users create, such as analyses and dashboards. Text strings hold the names and descriptions of these objects.
If you must localize text strings for the objects, then you can export the text strings from the catalog so that they can be translated. You then expose the strings when translation is complete.
Using the Export Captions and Import Captions wizards. These wizards simplify the process of localizing catalog captions, but they require that your user ID has access to all Business Intelligence files.
Using the Export Captions utility and importing the captions manually.
This section describes the steps in the process of localizing captions:
Step 4: Handling Duplicate Exported Text Strings Note: This task is not required if you used the Export Captions wizard.
The export process requires a series of steps that must be followed in order.
The export process creates one XML file for every first-level subfolder in the shared folder, in the format foldername captions.xml, where foldername is the name of the subfolder in the shared folder. Each XML file contains the text strings for all content in the corresponding first-level folder and its subfolders.
For example, if the shared folder in the Presentation Catalog contains the first-level folders Marketing, Service, and Sales, then the export process creates three XML files:
marketingcaptions.xml
salescaptions.xml
servicecaptions.xml
After the content is translated, you can use the Import Captions wizard, or can you place these folders in their corresponding location in the following directory:
SDD/service_instances/service1/metadata/content/msgdb/l_xx/captions
Where SDD is the Singleton Data Directory for example, DOMAIN_HOME/bidata
(see Key Directories in Oracle Business Intelligence).
The export process not only generates new XML files, but the process also modifies the catalog, inserting the appropriate message ID for each object. Presentation Services uses those message IDs to locate the newly translated text.
Note that an error may occur when you export a folder whose name includes supplementary (extended Unicode) characters.
The following procedures describe how to export text strings in the catalog.
Exporting Text Strings Using the Export Captions Wizard
In the Administration page, select Export Captions from the Manage Catalog Captions area.
Browse to the catalog that contains the strings to export and click OK. Selecting Exclude Description will exclude captions for catalog object descriptions.
A captions XML file is generated.
Exporting Text Strings Directly from Catalog Manager Using the Export Captions Utility
When the export process is complete, deliver the output XML file to the localization team.
When editing XML files, use an editor that is designed for XML files. Ensure that you follow the encoding that is specified at the top of the XML file and that you escape special characters as appropriate. You and the localization team are responsible for resolving any errors in the translated text strings. Consider that the contents of the catalog are updated whenever objects are added, deleted, or modified.
You can make a copy of every output file for each language to be translated.
The first illustration shows an extract from an exported caption XML file before translation. The file is named myfoldercaptions.xml. The second illustration shows an extract from the file after translation. The file is named myfoldercaptions_fr.xml.
You might encounter an issue of having duplicate exported text strings from the catalog.
Note:
The steps in this section do not apply when you have used the Export Captions wizard.Duplicated exported text strings from the catalog happen when the Export Captions utility is run simultaneously by multiple users or if the same user runs the utility twice in less than one minute. The following procedure describes how to address duplicate captions.
The Export Captions utility is described in Step 2: Exporting Text Strings in the Catalog
Consider the following webmessages.xml file, which contains duplicate captions:
<WebMessageTable system="catalog" type="folder" path="/shared/example/A"> <WebMessage name="kcap12790830_5" use="Caption" status="new"> <TEXT>A Really Good Report</TEXT> </WebMessage> </WebMessageTable> <WebMessageTable system="catalog" type="folder" path="/shared/example/B"> <WebMessage name="kcap12790830_5" use="Caption" status="new"> <TEXT>I like this report</TEXT> </WebMessage> </WebMessageTable> <WebMessageTable system="catalog" type="folder" path="/shared/example/Copy of A"> <WebMessage name="kcap12790830_5" use="Caption" status="new"> <TEXT>A Really Good Report</TEXT> </WebMessage> </WebMessageTable>
In this example file, Object B has an invalid duplicate message ID. Object Copy of A has a valid but duplicate message ID. You can make the following selections in the Export Captions dialog:
Selecting Leave alone makes no changes to the contents of the file.
Selecting Remove IDs generates new and unique IDs for both Object B and Object Copy of A.
Selecting Remove texts generates a new and unique ID for Object B and deletes the WebMessage element for Object Copy of A. While this option generally ensures fewer messages to translate, keep in mind that you now see two objects with the same name in a directory listing of the catalog in Presentation Services and in Catalog Manager.
After you have exported the text strings for the catalog, you must expose them for users.
Importing the Translated Captions using the Import Captions Wizard
In the Administration page, select Import Captions from the Manage Catalog Captions area.
Browse to your translated captions file, select the language, and click OK.
Importing the Translated Captions manually
To move translated captions from a development environment to a production environment:
If the caption file:
Does not exist in the production environment, then simply copy it from the development environment to the production environment.
Does exist in the production environment, first make a backup copy of the existing file. Then open the caption file in the production environment in a text editor or XML editing tool and manually (and very carefully) insert the changes that were made in the development environment.
After you have imported the text strings for the catalog, you can download, review, or delete them as needed.
Right-to-left languages are displayed slightly differently in Mozilla Firefox browsers.
By default, scroll bars are displayed on the right side of the Mozilla Firefox browser. If you are using the Arabic or Hebrew languages, then it is not appropriate to have the scroll bars on the right side. You can change the browser settings in Firefox such that the scroll bars are displayed on the left side.
For information about changing the layout.scrollbar.side setting, see the Firefox documentation.
When you use Catalog Manager, you can specify the locale to use for its user interface elements and for objects in the catalog.
Catalog Manager locales can be the same or different. The user interface elements are available in 10 locales, and the catalog content for certain applications is available in 28 locales.
You can see the user interface elements of Catalog Manager (dialogs, menus, and so on) in any of the 10 locales in which it is available. Certain areas of Catalog Manager, such as data handling, are not yet translated or localized. Catalog Manager uses the following process to decide which locale to display.
When you start Catalog Manager and open a catalog in online mode, you can select the locale for viewing the contents of the catalog. The locales that are available for selection depend on the following criteria. Catalog Manager uses this selection for subsequent online connections.
Whether that locale was selected for Presentation Services during the installation process.
Whether the contents of the catalog have been translated for a specified locale.
If translated files are not available for that locale, then the contents are displayed in the default locale of English (specifically, en_US).
Note that:
Session errors (such as login failed or session timed out) are displayed in the default locale, not necessarily the locale of the user trying to login or whose session timed out.
Some strings in the Catalog Manager user interface (such as the string Maximize) are not translated.
Learn more about the various ways to customize localizations in the application.
The following sections provide information about setting the locale in Oracle BI Server:
To support multiple languages, the Oracle BI Server must be configured properly.
The General section of the NQSConfig.INI file contains parameters that are required for localization and internationalization. It also contains default parameters that determine how data is sent from the Oracle BI Server to a client application. See Configuration File Settings.
The following parameters in the NQSConfig.INI file affect localization:
LOCALE
SORT_ORDER_LOCALE
SORT_TYPE
CASE_SENSITIVE_CHARACTER_COMPARISON
To successfully run Oracle Business Intelligence, ensure that you configure the appropriate locales on your operating system for the language in which users run the applications. Some locale- and language-related settings are interrelated and help determine how the Oracle BI Server sorts data.
The value to use for the C-runtime locale during server startup is specified in the SORT_ORDER_LOCALE parameter in the NQSConfig.INI file. This parameter is set normally by the Oracle BI Server.
The locale is used for functions such as displaying dates and currencies and sorting data.
If you must adjust the setting, then in the General section of the NQSConfig.INI
file, set the LOCALE and SORT_ORDER_LOCALE parameters, entering a platform-independent name.
The following table shows language mappings from the platform-independent name to the specific name for each of the supported UNIX platforms. For example, Chinese uses the setting zh_CN.utf8 on HP-UX or Linux operating systems.
Name strings such as zh_CN.utf8 and fr-FR-UTF-8 are the platform-specific names of the locale components, which must be installed by a system administrator. The NQSConfig.INI file uses the platform-independent names, such as Chinese or French (the names are case-insensitive).
Locale (Platform-Independent Name) | Name on Solaris | Name on AIX | Name on HP-UX/Linux |
---|---|---|---|
Arabic |
ar_SA.UTF-8 |
AR_AA.UTF-8 |
ar_SA.utf8 |
Chinese |
zh_CN.UTF-8 |
ZH_CN.UTF-8 |
zh_CN.utf8 |
Chinese-traditional |
zh_TW.UTF-8 |
ZH_TW.UTF-8 |
zh_TW.utf8 |
Croatian |
hr_HR.UTF-8 |
HR_HR.UTF-8 |
hr_HR.utf8 |
Czech |
cs_CZ.UTF-8 |
CS_CZ.UTF-8 |
cs_CZ.utf8 |
Danish |
da_DK.UTF-8 |
DA_DK.UTF-8 |
da_DK.utf8 |
Dutch |
nl_NL.UTF-8 |
NL_NL.UTF-8 |
nl_NL.utf8 |
English-USA |
en_US.UTF-8 |
EN_US.UTF-8 |
en_US.utf8 |
Finnish |
fi_FI.UTF-8 |
FI_FI.UTF-8 |
fi_FI.utf8 |
French |
fr_FR.UTF-8 |
FR_FR.UTF-8 |
fr_FR.utf8 |
German |
de_DE.UTF-8 |
DE_DE.UTF-8 |
de_DE.utf8 |
Greek |
el_GR.UTF-8 |
EL_GR.UTF-8 |
el_GR.utf8 |
Hebrew |
he_IL.UTF-8 |
HE_IL.UTF-8 |
iw_IL.utf8 |
Hungarian |
hu_HU.UTF-8 |
HU_HU.UTF-8 |
hu_HU.utf8 |
Italian |
it_IT.UTF-8 |
IT_IT.UTF-8 |
it_IT.utf8 |
Japanese |
ja_JP.UTF-8 |
JA_JP.UTF-8 |
ja_JP.utf8 |
Korean |
ko_KR.UTF-8 |
KO_KR.UTF-8 |
ko_KR.utf8 |
Norwegian |
no_NO.UTF-8 |
NO_NO.UTF-8 |
no_NO.utf8 |
Polish |
pl_PL.UTF-8 |
PL_PL.UTF-8 |
pl_PL.utf8 |
Portuguese |
pt_PT.UTF-8 |
PT_PT.UTF-8 |
pt_PT.utf8 |
Portuguese-Brazilian |
pt_BR.UTF-8 |
PT_BR.UTF-8 |
pt_BR.utf8 |
Romanian |
ro_RO.UTF-8 |
RO_RO.UTF-8 |
ro_RO.utf8 |
Russian |
ru_RU.UTF-8 |
RU_RU.UTF-8 |
ru_RU.utf8 |
Slovak |
sk_SK.UTF-8 |
SK_SK.UTF-8 |
sk_SK.utf8 |
Spanish |
es_ES.UTF-8 |
ES_ES.UTF-8 |
es_ES.utf8 |
Swedish |
sv_SE.UTF-8 |
SV_SE.UTF-8 |
sv_SE.utf8 |
Thai |
th_TH.UTF-8 |
TH_TH.UTF-8 |
th_TH.utf8 |
Turkish |
tr_TR.UTF-8 |
TR_TR.UTF-8 |
tr_TR.utf8 |
For Oracle BI Presentation Services, the error message language is set based on the NQ_SESSION.WEBLANGUAGE session variable.
Presentation Services provides a default value for this variable upon installation. The value is updated when a user selects a language on the Oracle BI EE sign-in page.
For other clients, including third-party clients, the error message language is determined by the following precedence model:
The error message language is set based on the WEBLANGUAGE session variable.
If the WEBLANGUAGE session variable is not set, then the error message language is based on the error language that is specified in the ODBC Data Source Name (DSN) that is used to access the Oracle BI Presentation Services.
See Integrator's Guide for Oracle Business Intelligence Enterprise Edition for information about setting the error message language in the ODBC DSN.
If an error message language has not been set in the ODBC DSN, then the language that is specified in the ORACLE_BI_LANG environment variable is used for error messages.
To change the value of ORACLE_BI_LANG, update the character code for this variable in NQSConfig.INI. You can view the character codes for supported languages in the ORACLE_HOME/bi/bifoundation/server/locale
directory (for example, "en" for English, or "pt-BR" for Portuguese/Brazilian).
If the ORACLE_BI_LANG environment variable is not set, then error messages are displayed in English.
Note that clients for the Administration Tool and Job Manager do not set the WEBLANGUAGE session variable. Therefore, these clients follow the precedence model starting with the ODBC DSN error message setting.
The ORACLE_BI_LANG variable controls which language is used to present components of the application to the user.
To display the correct language for components in the Oracle BI Server, including the contents of the obis1-diagnostic.log file, you must set the ORACLE_BI_LANG variable, as described in the following procedure.
The user interface of the Oracle BI Administration Tool inherits the language that is specified for the operating system.
For example, if the Windows operating system is set to use the French language, then all user interface elements such as menus and buttons are displayed in French in all Windows-based applications such as Notepad and the Administration Tool. The locale that you set in the Windows Control Panel affects items such as currency, dates and times, units displayed, and keyboard layout, which differ from user interface elements such as menus and buttons.
The recommended approach is to allow the Administration Tool to inherit the language from the operating system. If you must change the language for the user interface of the Administration Tool without changing the operating system language, then you can use the ORACLE_BI_LANG environment variable for this purpose. For information on setting that variable, see Understanding How the Error Message Language Is Determined.
You can also localize the names of subject areas, tables, hierarchies, columns, and their descriptions in the Presentation layer, as described in Localizing Metadata Names in the Repository.
Some locations require specific troubleshooting procedures.
This section provides the following information about troubleshooting the current locale in the Oracle BI Server:
If you do not have the appropriate locale installed, then the Oracle BI Server does not start, and the obis1-query.log file contains the following error:
NLS locale xxx is not supported by the operating system.
where xxx is the locale that is specified in the NQSConfig.INI file for the SORT_ORDER_LOCALE parameter. Take the following actions to resolve this error:
UNIX. Install the locale that is indicated in the table displayed in Setting the Locale on UNIX Systems for the requested language.
Windows. Add the corresponding language pack using the Regional Settings dialog.
Ensure that usage information and error messages for the Oracle BI Server utilities are displayed in the correct language.
For Linux environments, set the terminal encoding to UTF-8 to display multibyte characters. To do this, select the Terminal menu, then select Set Character encoding, then select utf8.
For native Windows environments, set the font of the console to Lucida Console. If this option is not displayed in the list, then first change the code page to 65001, which supports UTF-8, using the command chcp 65001
.
When the NLS_CHARACTERSET in the Oracle Database is set to a non-unicode derived code page, you must configure an additional metadata repository setting to make character sorting consistent between the Oracle Database and the Oracle BI Server.
You can use the Externalize Strings utility in the Administration Tool to localize the names of subject areas, tables, hierarchies, columns, and their descriptions in the Presentation layer.
You can save these text strings to external files with ANSI, Unicode, and UTF-8 encoding options.
Open the repository in the Administration Tool.
Right-click any Presentation layer object, such as a subject area, presentation table, or presentation column, and select either Externalize Display Names then Generate Custom Names, or Externalize Descriptions then Generate Custom Descriptions to externalize strings. Note that when you select Generate Custom Names and then run the Externalize Strings utility, the translation key will also appear in the Externalize Strings dialog.
Selecting one of these right-click externalization options automatically selects the Custom display name or Custom description options in the Properties dialog for the selected object and all of its child objects.
For example, if you right-click a subject area and select one of the externalization options, then the externalization flag is set on all presentation tables, columns, hierarchies, and levels within that subject area.
Select Tools, then select Utilities.
Select Externalize Strings and click Execute.
In the Externalize Strings dialog, select one or more subject areas in the left pane.
In the right pane, the translated values and the original strings (names and descriptions) are displayed. These are placed in session variables for use by Presentation Services.
Only those objects with the externalization flag set in the Presentation layer are displayed in the right pane.
Click Save.
In the Save As dialog, do one of the following:
If you selected a single subject area, then select a type of file and an encoding value and click Save.
If you selected multiple subject areas and want each one externalized in its own XML file, then select a directory name and press SHIFT while clicking Save. Each subject area is saved to an XML file with Unicode encryption.
In the Externalized Strings dialog, click Close.
(Optional) To disable externalization, right-click a Presentation layer object and select Externalize Display Names, then Disable Externalization, or Externalize Descriptions then Disable Externalization.
Selecting one of these options automatically deselects the Custom display name or Custom description options in the Properties dialog for the selected object and all of its child objects.
When you have created the string files using the Externalize Strings utility, you can use the files to translate the strings for the metadata objects, as described in the following procedure.
Open each string file and examine the columns:
The first column contains the actual repository object names, which have a prefix of their type.
The second column contains the session variables that correspond to the name of each object or description, with a default prefix of CN_ for custom names and CD_ for custom descriptions.
The third column contains the translation keys that correspond to the name of each object.
Add a fourth column called Language. In this column, specify the code for the language in which the name was translated, such as de.
Load each string file into a database table.
In the Administration Tool, import the table into the physical layer.
Load the translated strings using row-wise initialization blocks. Ensure that you set the target of the initialization block to Row-wise initialization and that the execution precedence is set correctly.
For example, you could do the following:
Create a session initialization block that has the data source from a database, using a SQL statement such as the following one:
SELECT 'VALUEOF(NQ_SESSION.WEBLANGUAGE)' FROM DUAL
In the Session Variable Initialization Block dialog for SET Language, specify the LOCALE session variable for the Variable Target.
This specification ensures that whenever a user signs in, the WEBLANGUAGE session variable is set. Then this variable sets the LOCALE variable using the initialization block.
Create another session initialization block that creates a set of session variables using a database-specific SQL statement such as the following one in the Session Variable Initialization Block Data Source dialog:
select SESSION_VARIABLE, TRANSLATION from external where LANGUAGE = 'VALUEOF(NQ_SESSION.LOCALE)'
This block creates all the variables whose language matches the language that the user specified during sign-in.
In the Session Variable Initialization Block Variable Target dialog, set the target of the initialization block to Row-wise initialization.
In the Execution Precedence area of the Session Variable Initialization Block dialog, specify the previously created initialization block, so that the first block that you created earlier is executed first.
Save your changes.
Tips:
For information on the language for the Administration Tool, see Modifying the Language of the User Interface for the Administration Tool.
If you have an Oracle Application Development Framework data source, then you can propagate labels and tooltips from that data source, instead of using the Externalize Strings utility. See Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
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.
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.
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.
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.
There are two common techniques of designing translation lookup tables in a multilingual schema as follows:
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.
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.
This section describes creating logical lookup tables and lookup columns and contains the following topics:
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.
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.
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 keyword DENSE
or SPARSE
. The default behavior is dense lookup, if neither DENSE
or SPARSE
is specified. For DENSE
lookup, the translation table is joined to the base table through an inner join, while for SPARSE
lookup, a left outer join is performed.
The first parameter (the parameter after the DENSE
or SPARSE
keyword) must be a valid value column from a valid lookup table that is 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. For DENSE
lookup, 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")
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")))
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 LOOKUP
function as part of a primary logical level key. This limitation exists because the LOOKUP
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 is 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 should not have their associated levels below the level of the derived logical column itself.
Configuring a descriptor ID column is the recommended approach.
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 |
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 LOOKUP
function works the same as a logical LOOKUP
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 is implied by the value column of the LOOKUP
function.
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.
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.
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.