| Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-01 |
|
 Previous |
 Next |
| Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-01 |
|
Previous |
Next |
Users can easily and dynamically change their language and locale preferences. When users select a language, they see many elements in that language. These elements include user interface components, metadata, messages, and Help files.
This chapter provides information on how to configure for deployment in one or more language environments other than English. This chapter contains the following sections:
In this chapter, 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 in this chapter to adapt your deployment to support multiple languages.
For information on supported languages, see Section 1.8, "System Requirements and Certification".
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:
nqserver.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 the components of Oracle Business Intelligence, as described in the following sections:
As the administrator, you perform various tasks to localize Oracle BI Presentation Services, as described in the following sections:
Section 16.2.1, "Localizing the User Interface for Oracle BI Presentation Services"
Section 16.2.2, "Localizing Oracle BI Presentation Catalog Captions"
Section 16.2.3, "Tip for Arabic and Hebrew in Mozilla Firefox Browsers"
Section 16.2.4, "Handling the Translation of Layers in Maps"
Chapter 22 describes how to customize the user interface for Oracle BI Presentation Services. Part of the process of customizing is localizing the interface, 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:
Section 16.2.1.2, "Localizing Messages for Users' Preferred Currency"
Section 16.2.1.3, "Specifying the Default Language for the Sign-in Page"
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\bifoundation\web\MS-DOS directory. To localize these elements and messages, you copy those files to the l_xx subdirectories in the ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\msgdb subdirectories, where xx indicates the language extension. 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.
Section 17.2.1, "Defining User-Preferred Currency Options Using a Static Mapping" provides a procedure for working with users' preferred currencies. Use the following procedure to localize the messages that are associated with a preferred currency.
To localize the messages that are associated with each users' preferred currency:
Go to the ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\msgdb\l_xx directory, where xx is the language extension for the language in which you are specifying preferred currencies.
In the directory, create a subdirectory called custommessages.
In the directory, create a file in XML format, with the name of usercurrencymessages.xml.
Add entries such as the following one to this file for the language that corresponds to the directory in which you are working. The following example includes two messages: kmsgMyCurrency1 and kmsgMyCurrency2
<?xml version="1.0" encoding="utf-8"?> <WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"> <WebMessageTable system="CurrencyDisplay" table="Messages" code="false"> <WebMessage name="kmsgMyCurrency1"><TEXT>My Currency Text 1</TEXT></WebMessage> <WebMessage name="kmsgMyCurrency2"><TEXT>My Currency Text 2</TEXT></WebMessage> </WebMessageTable> </WebMessageTables>
Edit the userpref_currencies.xml file to specify displayMessage="kmsgMyCurrency1" to use this message.
Repeat Steps 1 through 5 for each language for which you must localize these messages.
Restart the service for Oracle BI Presentation Services.
In Oracle BI EE, the appropriate localized text is displayed to the user. In this example, the text is My Currency Text 1.
For more information, see Section 22.5, "Customizing the User Interface Using XML Message Files."
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. |
To change the default language on a user's login screen in Internet Explorer:
In Internet Explorer, from the Tools menu, select Internet Options.
The Internet Options dialog is displayed.
Click Languages.
The Language Preference dialog is displayed.
Installed languages are displayed in the Language list. The language at the top of the list is used as the default language.
If the desired language is not installed on the browser, then add it.
Use the Move Up and Move Down buttons to position the desired language at the top of the list.
Restart the browser and sign into Presentation Services.
The default language should match the language in the browser's Language list.
|
Note: If a user does not select a different language from the list on the sign-in page, then the setting for the User Interface Language in the user's My Account dialog determines the language in which the user interface is displayed. |
When users start Oracle BI EE by displaying the sign-in page, they can select the language as part of the sign-in process. They 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 run time, the value is set as the profile attribute LanguageCode. Table 16-1 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
Table 16-1 Example of Settings in the Symbolic URL Parameters Applet
| 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.
The export process creates one XML file for every first-level subfolder in the shared folder, in the format foldernamecaptions.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.
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. Keep in mind that the contents of the catalog are updated whenever objects are added, deleted, or modified.
For example, if the shared folder in the Oracle BI 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 place these folders in their corresponding location in the following directory:
ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\msgdb\l_xx\captions
Their content loads when the service for Oracle BI Presentation Services starts.
|
Note: The ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\msgdb\l_xx\captions directory exists only if Oracle Business Intelligence Applications have been installed. If it does not exist, then you must create it. |
To export catalog text strings:
Back up the catalog before exporting from it.
Ensure that you run the export utility against the actual catalog, not against a copy of the catalog, because the export utility changes the properties of the objects in the catalog against which it runs.
In Catalog Manager, open the catalog in either online or offline mode.
Select the folder that contains the strings to export. The utility runs against the files in that folder and all its subfolders.
For example, the title (Another Report) in the following analysis can be exported for translation.
From the Tools menu, select Export Captions.
Click Browse to select the location in which to write the output file, then click OK.
To export only new text strings and those that have been changed since the last export, select Only export new or changed strings.
To exclude the Description properties from the export, select Exclude Descriptions.
Click OK.
The export process might take several minutes.
When the export process is complete, deliver the output file to the localization team.
You can make a copy of every output file for each language to be translated.
The following shows an extract from an exported caption XML file before translation (for example, myfoldercaptions.xml) and after translation (for example, myfoldercaptions_fr.xml).
To expose catalog text strings:
Place the translated XML files into their corresponding location in the following directory and restart Presentation Services:
ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\msgdb\l_xx\captions
where xx is the language extension.
For example:
D:\R11\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\msgdb\l_fr\captions\myfoldercaptions_fr.xml
Other examples of language extensions include cs for Czech and de for German.
Sign into Oracle Business Intelligence and select the appropriate language, such as French.
Display the translated content.
For example, display the following translated title.
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.
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 on changing the layout.scrollbar.side setting, see the Firefox documentation.
You can use the functionality of MapViewer to label the features of a theme (called a layer for maps in Oracle BI EE) using a specific language or locale. To configure these translated labels for maps, use the information that is provided in Oracle Fusion Middleware User's Guide for Oracle MapViewer.
The following sections provide information about setting the locale in Oracle BI Server:
Section 16.3.1, "Setting Locale Parameters on Oracle BI Server"
Section 16.3.2, "Understanding How the Error Message Language is Determined"
Section 16.3.3, "Troubleshooting the Current Locale in the 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 Appendix A, "NQSConfig.INI File Configuration Settings" for complete information on these parameters.
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-run-time 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 as shown in Table 16-2.
Table 16-2 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).
Table 16-2 LOCALE Settings for UNIX Platforms
| 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 Server.
See Oracle Fusion Middleware 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 opmn.xml. You can view the character codes for supported languages in the ORACLE_HOME/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. Because of this, these clients follow the precedence model starting with the ODBC DSN error message setting.
This section provides the following information on troubleshooting the current locale in the Oracle BI Server:
Section 16.3.3.1, "Handling the NLS Locale Not Supported Error Message"
Section 16.3.3.2, "Setting the Japanese Locale on AIX Systems"
If you do not have the appropriate locale installed, then the Oracle BI Server does not start, and the NQSServer.log file contains the following error:
[47013] 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 Table 16-2 for the requested language.
Windows. Add the corresponding language pack using the Regional Settings dialog box.
When using a Japanese localization on an AIX platform, you might discover that the Oracle BI Server does not start. If you encounter this issue, then use the following procedure.
To set the Japanese locale on an AIX system:
Ensure that the JA_JP.UTF-8 locale is installed. If it is not, then install it.
Open the NQSConfig.INI file for editing, as described in Section 3.4, "Using a Text Editor to Update Oracle Business Intelligence Configuration Settings."
In the General section, set the following parameters, being aware that the settings are case-sensitive:
LOCALE = "Japanese";
SORT_ORDER_LOCALE = "Japanese";
Save and close the NQSConfig.INI file.
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 an external file with ANSI, Unicode, and UTF-8 encoding options.
To externalize strings for localization:
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.
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 a subject area in the left pane.
You can select all the subject areas at once, or select them individually and create a separate string file for each one.
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, select a type of file and an encoding value and click Save.
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 file using the Externalize Strings utility, you can use it to translate the strings for the metadata objects, as described in the following procedure.
To translate strings for metadata from the exported string file:
Open the 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.
In the third column of the file, ask the translation team to provide the translation of 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 the 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 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.
|
Tip: 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 Appendix F, "Propagating Labels and Tooltips from Oracle ADF Data Sources" for more information. |
This section describes how you can configure the Oracle BI Server to display field information in multiple languages, and contains the following topics:
Section 16.5.4, "Designing Translation Lookup Tables in a Multilingual Schema"
Section 16.5.5, "Creating Logical Lookup Tables and Logical Lookup Columns"
Section 16.5.6, "Creating Physical Lookup Tables and Physical Lookup Columns"
Section 16.5.7, "Supporting Multilingual Data in Essbase Through Alias Tables"
For information about using the Administration Tool, see Oracle Fusion Middleware 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 Figure 16-1 shows each record in the lookup table matching only one row in the base table.
Figure 16-1 A Lookup Table For Each Base Table
The alternative approach to having one lookup table for each base table involves a separate lookup table for each translated field. 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.
Figure 16-2 A Lookup Table For Each Translated Field
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 Section 16.5.5.2, "Designating a Logical Table as 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 Figure 16-3, 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 Figure 16-3, 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 box.
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 should 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 should 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:
INDEXCOL( VALUEOF(NQ_SESSION."LAN_INT"), "Translated Lookup Tables"."Product". "ProductName", LOOKUP( DENSE "Translated Lookup Tables"."Product Translations". "ProductName", "Translated Lookup Tables"."Product"."ProductID", VALUEOF(NQ_SESSION."WEBLANGUAGE")))
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 example above, 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")))
|
Note: You cannot use a derived logical column that is the result of aLOOKUP 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 |
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 pre-existing 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. Table 16-3 provides a mapping table for non-ISO language codes. LANGUAGE is a non-ISO language code.
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 box, 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.
To configure a translation column that is derived from a physical lookup table:
Open the repository in the Administration Tool.
In the Business Model and Mapping layer, create a new logical column by right-clicking the appropriate logical table (for example, Categories) and selecting New Object, then Logical Column.
Provide a name for the logical column (for example, categoryname_p).
Select the Column Source tab.
In the Logical Table Source box under Derived from physical mappings, double-click the logical table source object that contains the base table column. The Column Mapping tab of the Logical Table Source dialog box is displayed.
Ensure that Show unmapped columns is selected.
In the Expression column for the new logical column (for example, categoryname_p), enter an expression such as the following:
INDEXCOL(VALUEOF(NQ_SESSION."LAN_INT"), "DB_Name"."My_Category"."My_Schema"."Categories"."CategoryName", LOOKUP(SPARSE "DB_Name"."My_Category"."My_Schema"."CATEGORIES_TRANS"."CATEGORYNAME", "DB_Name"."My_Category"."My_Schema"."Categories"."CategoryName", "DB_Name"."My_Category"."My_Schema"."Categories"."CategoryID", VALUEOF(NQ_SESSION."LANGUAGE")))
You can also use Expression Builder to create the expression.
Click OK in the Logical Table Source dialog box.
Click OK in the Logical Column dialog box.
Save your changes.
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 exactly 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 box 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 box for the physical translation table. For example, on the Keys tab of the Physical Table dialog box 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 Oracle Fusion Middleware 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 do this, ensure that ORDERBY_SUPPORTED is not selected in the Features tab of the Database dialog box in the Administration Tool. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information on 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 should still be enabled in the data source, regardless of the impact on the lexicographical sorting functionality.
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|