OBIEE – Localisation – Externalize Metadata Strings

There are couple of benefits working across different projects and give the consulatncy all way around to gather ad-hoc knowledge and get to learn new features , explore it and establish the idea . Pretty Cool huh !  

One of the foremost important feature in OBIEE is  Localisation i.e Multi country implementation .It’s a robust feature indeed ! to make the application reusable across several country in multi country roll-out deployment and thus Internationalizing the application .

The Oracle Business Intelligence Presentation layer supports multiple language translations for any presentation column name and column description. When working with Oracle BI Answers or rendering a dashboard, users should see their local language strings in their reports. For example, English-speaking and French-speaking users would see their local language strings in their reports. There are two kinds of application strings required for the translation in Oracle Business Intelligence :

Metadata Strings
Metadata strings are Analytics-created objects in the Oracle Business Intelligence repository objects such as Subject Area, Metrics, Dimensions etc. Read Oracle Ref .

Presentation Services (Localizing Presentation Catalog Captions)
Presentation Services objects are end-user created objects such as Reports, Dashboards, and Pages. Translations for Presentation Services strings are stored in the captions.xml file.How this could be accessed can be found from Oracle BI Presentation Service Admin Guide .

So there are 2 aspects of this localisation . One from RPD perspective and other from BI Catalog perspective .

In RPD perspective changes user can visualize the change in column name and description due to localisation . However in Catalog perspective change, user can see the localised country specific language translated string for Reports / Dashboards / Pages .

So lets prepare a sample data bed and then configure both first in Repository area(Externalizing Metadata Strings) and then from Catalog (Web Catalog Strings) .

The sample data I have created as below :

Now I have imported those tables and done and simple RPD configuration .Note that there are a table ‘W_MSG’ will be used to store  metadata translation string and will be described later .

Lets externalize both Name and Descriptions in RPD and then export that string into a CSV file .

I have exported the CSV to a file called “Sample_Sales_Externalised_Strng.csv” .Lets see it :

Now lets modify the csv with the translated strings ( taking reference of Google Translator) with different languages and perform the loading from csv into Language Translation table called “W_MSG” as mentioned earlier .

After loading the table looks like :

So for same MSG_NUM value there will be different MSG_TEXT for each individual language .MSG_NUM represents session variable of the metadata string , MSG_TEXT represents the translation for the metadata string and LANG_ID represents the language of translation.

N.B :- Make sure that none of the MSG_TEXT will be NULL otherwise loading of the Metadata Strings will simply return void while logging in using different Web Languages other than English .I have encounter this problem and it seems to be a Bug in 10.1.3.4 as there are no documented product bug fix available either .

Now the above metadata strings will be loaded by the BI Server from the database table .Lets see how this will be loaded from the repository configuration .Create the below Session Init Block and assign the target variable as shown below :

LOCALE and External Metadata Strings are the initialization blocks which are need to be created for the localization of the metadata strings. N.B :- In first snapshot LOCALE system session variable has been assigned with the value of the Weblanguages using below :

select ‘VALUEOF(NQ_SESSION.WEBLANGUAGE)’ from DUAL

Hence the value of this would be populated as ‘de’ (while you logging in OBIEE after switching  the language as Deutsh) , ‘en’ (while you logging in OBIEE after switching the language as English) etc .

Now in second snapshot the entire W_MSG table will be scanned through and the rows will be returned by Row-wise Initialisation Block and set the execution precedence as LOCALE as this variable has been used in below block :

select MSG_NUM, MSG_TEXT
from W_MSG
where LANG_ID = decode( ‘VALUEOF(NQ_SESSION.LOCALE)’ ,
‘en’ , ‘ENU’,
‘fr’, ‘FRA’,
‘de’ , ‘DEU’,
‘es’, ‘ESN’,
‘ja’, ‘JPN’,
‘pt-br’, ‘PTB’,
‘it’, ‘ITA’,
‘sv’, ‘SVE’,
‘da’, ‘DAN’,
‘nl’, ‘NLD’,
‘zh’ , ‘CHS’,
‘zh-tw’, ‘CHT’,
‘iw’, ‘HEB’,
‘ko’, ‘KOR’,
‘cs’, ‘CSY’,
‘fi’ , ‘FIN’,
‘el’, ‘ELL’,
‘pt’, ‘PTG’,
‘pl’, ‘POL’,
‘el’,'GRC’,
‘hu’,'HUN’,
‘ro’,'ROM’,
‘sk’,'SKY’,
‘fr-ca’,'FRC’,
‘en-ca’,'ENC’,
‘en-gb’,'ENG’,
‘en-nl’,'ENN’,
‘en-ie’,'ENI’,
‘fr-ch’,'FRS’,
‘de-ch’,'DES’,
‘de-at’,'DEA’,
‘fr-be’,'FRB’,
‘nl-be’,'NLB’,
‘tr’,'TRK’,
‘ENU’)

The above block will decode the language say for e.g : ‘de’ to ‘DEU’ and query through the translation table to search the session variables ‘MSG_TEXT’ .

Now simply log-in after setting language ‘English’  you will get the metadata string descriptions in tooltip as below :

Log out and log-in after setting language ‘Deutsch”  .

You will get the translated name for Presentation column and translated metadata description in tooltip as below :

Now you can see the Catalog description for the subject area has been translated as well in language ‘German’ .

Same in language ‘French’  as well .

This is how the localisation has been done in Repository perspective . However there will be some more area in catalog need to have the translated strings like ‘Dashboard Name’ , ‘ Page Name’ , ‘ Narratives’ , ‘HTML’ , ‘Link Caption’ which need to be done from the XML change in catalog extract .

See you next time in this thread continuing the part of Catalog translation .    to be continued …..

So … here I am right back to explain how of  Localizing Presentation Catalog Captions can be done . This is a very important part to understand as localised country will definitely not only will be interested for the Metadata translation ..rather they like to see the translated strings for Dashboard , Pages , Links , Narratives etc. all .

To understand in details how this could be done … refer to Oracle documentation . Lets follow the below steps :

1) Open the Catalog Manager . Shutdown all BI services .

2) Open the Catalog using the catalog manager offline .

3) Select the catalog folder which we would be interested for Translation .In my case I have exported the Shared folder .So all its subfolders have been exported .

4) Save the files under some location and copy Salescaptions.xml to

“..\OracleBIDataHome\web\msgdb\l_de\Captions” .

However oracle documentation talks about the export into different location i.e “SADATADIR\web\res\l_xx\Captions” which is a proven bug and explained under “2.2.8 Issue Handling Captions in Presentation Services” topic of  :

Oracle® Business Intelligence Suite Enterprise
Edition
Release Notes
Version 10.1.3.4
Part No. E10404-17
N.B : – Since I would like to see the translation in language German .Hence place the Captions file under “l_de”  where de=Deutsch . Similarly for French it would be under “l_fr” .

Now since I have created a mapping document for all the catalog strings like below and according to that I have modified my Captions XML file .

————————————————————————————-

After modifying the XML as above note that you have to ensure that , there  should not be any XML error thrown by running the XML file directly in your browser i.e we have to avoid error shown as below(if any) :

As error like this will cause the translation failure and the entire translation will be go into hell . Be cautious about the characters like ‘&’ ,’ ä ‘,’ ü ‘ . In my case I avoid this characters from German strings and simply use ‘a’ ,’u’ etc .I am not German expert and don’t know how to avoid this or make this character supportable in my XML  Anybody could help !

4) Restart the BI presentation Services and Log in by setting language Deutsch and see the translation .

Strange enough with  the Red marked links . Rest of them has been translated good …. ! Its true that I have editing all possible strings in my XML file but I have not find the strings for Page translation ,Links etc . So that could possibly in some different files . Yes , Got it !

Perform the Export Captions by selecting the root ‘/’ and you will get 3 captions again :

userscaptions.xml  – This is the culprit . Translation in this area would reflect into page and links .

systemcaptions.xml — Not bothers about .

sharedcaptions.xml — This is a superset of Salescaptions.xml here and typically the superset if more than one catalog under the same “/” i.e root folder .

So  modified the userscaptions.xml file and restart the presentation services . N.B : - sharedcaptions.xml should not be there inside “l_de” folder as this will overrride the Salescaptions.xml translation strings .

Logging in once again in Deutsch and Voila … all the problems resolved … So Happy Localising