[OBIEE11g] Multilanguage-Support in use - Variables over variables

1. But let's start at the beginning:
- first you have to switch on the external strings for your presentation-model in your Oracle BI Admin (which I will call OBIA in short from now on). Right click on it and choose either Column Names (prefixed CN_) or Column Descriptions (prefixed CD_) or both.

- afterwards select from the menu Tools / Utility and Externalize Strings

- choose your business model from presentation layer and hit the save-button, answer the questions for a file-name as you prefer. OBIA creates a CSV-file with 3 columns in it.

- the 1st one with the actual name of the presentation column
- the 2nd one contains the session-variable which will be created within Answers or Dashboard after you have implemented it sucessfully
- the 3d one holds the translation of the column
- for the purpose of having more flexibility let's add another one with the language-abbreviation

- create a table in whatever database (some call MS Access one, I do not !), in which you have the four of them columns, which you can name as you prefer.
For example ACTUAL, SESSION_VARIABLE, TRANSLATION, LANG_ID.

- add a connection-pool, the best with a speaking name, to your repository and test the connection with a short Data Preview (or simply do Update Row Count)

- afterwards the only thing left is to add an initialization-block which determines for you the language of the user logging in and another one which provides the functionality for OBIEE to select the correct columns from the created table.

Here the first one (you do not necessarily need this one, because in the second one you are using the same session-variable ! I did for making it more clear.). This one you can also use if you want for example a language driven filter or prompt.

select ‘VALUEOF(NQ_SESSION.WEBLANGUAGE)' from dual

  

and the second one:

select SESSION_VARIABLE, TRANSLATION from sales.bi_msg
where LANG_ID = 'VALUEOF(NQ_SESSION.WEBLANGUAGE)'

  

Now restart the presentation services and everything should be fine for every language, at least for the ones you already translated and inserted the column-names and -descriptions in your table.

But there is all this other stuff hanging around in the reports from which I initially thought is also implemented with the external string-solution. All this names of dashboard-pages, titles and subtitles, legends, report-based calculated columns, narrative views, chart-scale-markers, pivot-table-ingredients, gauge-range settings etc.pp.

Never the less, it came out some of them do not run with session-variables (Subtitles, Legends, views etc., see more details in the Answers, Delivers, and Interactive Dashboards-doc on page 45 or - it is the exception-join ) :

"Where can you reference variables?
You can reference variables in the following areas:
■ Title Views
■ Narrative Views
■ Column Filters
■ Column Formulas
■ Conditional Formatting conditions (presentation variables only)
■ Direct Database Requests
■ Dashboard prompts
■ Chart scale markers.
■ Gauge range settings.
■ Static text.
■ iBot Headlines and text"

2. Although it makes sense to have different places for translations in some cases, it is not exactly what Oracle is propagating when stating that they have one single and central place for holding metadata. Of  course, this is the case for the complete model, but the reporting-(better frontend) side is not included.
So to have this also translated you have to shut down your presentation services, start the Catalog Manager, open the desired catalog-files offline and export the captions (see pictures below). In this case you do not need to state an exact name (only the directory) for your file, your catalog manager exports every presentation catalog in one xml-file for you.

 

If you do this the second time you can force the software to export only new or changed strings into your captions. Also you see the option to exclude the column-descriptions.

After this step you should find a bunch of xml-files in the directory you defined in the export captions-dialog.

Wow, a lot of effort to handle translation, and not the comfortable way. Unfortunately it is a lot of manual work and you have in addition to do every keystroke very carefully, because all changes can only be done directly in a XML-file, where the format is really the crux of matter. And do not even think of versioning, handling external translation tables from different translators, changed calculated columns or titles. In the end you have to manually translate the whole report-catalog into all needed languages for all the reports, dashboards, graphs, legends, subtitles a.s.o and also for all metadata defined in the repository.

So, for the first time the very wide and broad (tend to use the word sophisticated, so I do) functionalities of OBIEE are a burden. To be honest I was not happy with this conclusion. In the end a very lucky coincidence happened: a software partner developed a tool for this special needs. Yeah! Its name is BI Localizer (more info onhttp://www.bi-localizer.com/). It is not the fancy-looking Client-Tool from the layout-perspective, but it handles all multilanguage-related tasks in one and therefore is Admin's darling.

You can
- parse & fix the repository
- import external translation tables
- translate the different fields in the XML-data
- do the translation of CN_ (column-names) & CD_-(column-descriptions) values for the repository-columns

Let's go in detail. After you have installed the demo-version you will be emailed after providing your information on the BI Localizer-page you start the setup and follow the instructions in the "Users manual BI Localizer.pdf". The setup will install some .NET-packages and finally also the BI Localizer-stuff. Please be aware that because the installer makes a automatic download from Microsoft you have to be connected with the internet. It could happen that you have to restart your computer (because of .NET).

There are some prerequisites (see the "User Manual" under post-install activities on page 7 ) to take care for so that you are ready for starting:
- copy all BI Localizer-related files in one working-directory (e.g. BILocalizer), pay especially attention for thelocale_default_template.udml (in it the initialization-block is defined) and the corresponding MS Access-file BI_Localizer.mdb.
- firstly you need the earlier defined externalization strings from your rpd-file (see point 1.Also described on page 8 and 9 in the "User Manual" )
- secondly of course the XML-definitions of your reports, dashboards, etc. are required. For obtaining them shut down your presentation services (see point 2.Also described on page 10 in the "User Manual" ))
- as a third step create a table with the provided within the SQL-script CreateLangTable.sql
- also you need a ODBC-connection to the new created Oracle-table for the interchange of data between MS Access and Oracle
- define an OCI-connection to your Oracle-table in your tnsnames.ora in order to give the prd-file access to it
- the external translation table has to be in a special format, for more information please refer to "User Manual"on page 13 under External Translation catalog)

Ready to go ? Start the BI-Localizer-application from your programs-folder from the BI Localizer-menu. After clicking Start on page 1 you have to define all parameters for either the repository- and the Oracle-DB-side. The values you have to define are on the left side:
- the Output directory
- the Username (for the repository) and also the password
- the file-name of the repository (by default NF_OBIEE_LANGUAGE_TABLE, you can of course change) and
- the MS Access-File, where the all data is stored

on the right side:
- username and password for the Oracle-DB-connection (actually only Oracle is supported as database)
- the SID of your database (you can see in your TNS-Names-file under Oracle\network\)
- the table-name (by default NF_OBIEE_LANGUAGE_TABLE, you can also change that)

Click on Step 2 for the next page, where the repository-informations are given. First the Captions you created in step 2, then the external strings you created from within your repository. At the bottom of this screen you can set the language you want work on right now (in Demo-Version only 4 languages are available).

Click on Step 3 for page 3, where the general options are defined. For the initial time of usage please check theclose match, every time later you check exact match to be sure that exact pattern-matches should be handled for translation by the tool. You want your repository parsed and fixed and the initialization block included ? Check the boxes on the right hand of the screen for. Also, if you already have an external translation table

Depending on the parameters you defined you see the Go-Buttons executable on the next page
1. Repository-clean-up (creates also the initialization-block for the Oracle-database needed for Localization)
2. Translate new / changed web-catalog metadata (the xml-files) - only shown if there is new untranslated metadata
3. Translate new / changed repository metadata (the rpd-file) - only shown if there is new untranslated metadata
4. Upload localization database to Oracle (NF_OBIEE_LANGUAGE_TABLE,if you have not changed the name)
5. Import external translation catalog

So, you can now do as and what you prefer. If you choose one of the two translation points the number of values to be translated displays and afterwards the translation is started.

Unfortunately in this version of BI Localizer you have to do this initially. If finished you can work with the underlying MS Access table and copy and paste the values for each language, integrating the translations from your external translation provider or translating it yourself, if your familiar with the languages needed.

Do not forget to change the settings on page 3 to exact match after your initial load, otherwise he will prompt you again for the translation of all unmatched data.

So, you see there are some things to do in this tool, but for the beginning it is quite useful.