Oracle Business Intelligence Enterprise Edition 11.1.1.6.0 – Configuring Logical Columns for Multicurrency Support with STATIC List Currency Option

Oracle Business Intelligence Enterprise Edition 11.1.1.6.0 allows the configuration of the logical column for currency so that Oracle BI users can select the currency in which they prefer to view currency in reports (analyses) and dashboards.

There are two options to set up this feature, one by which all users can see the same static list of currency options or other is to provide a dynamic list of currency options that changes based on the a logical SQL statement.

This blog is an attempt to explain how we can define user-preferred currency options by using a static list of currency settings.

Multicurrency Support Configuration Steps

The following are the steps need to carryout for multicurrency support for OBIEE 11.1.1.6.0

·         Step 1 – Modify the user preferences currency file

·         Step 2 – Create session variable PREFERRED_CURRENCY

·         Step 3 – Create logical column for currency conversions

·         Step 4 – Verification of multicurrency support configuration

Step 1: User Preferences Currency Files

In the Currency box on the Preferences tab of the My Account dialog box as shown below, Oracle Business Intelligence users can select the currency in which they prefer to view currency columns in analyses and dashboards.

The currency options shown within the Currency box above uses userpref_currencies.xml file for getting the list of available currencies.

Navigate <ORACLE_INSTANCE>\config\OracleBIPresentationServicesComponent\coreapplication_obips1 and open userpref_currencies.xml file for editing.

e.g.  /home/oracle/obiee/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1

Then locate the UserCurrencyPreferences element and remove the comment markers < ! – and -- > to enable currency preferences for Oracle preferences. In this specific example I have included three currency preferences as ‘USD’, ‘EUR’ and ‘AUD’.

<Config>

<UserCurrencyPreferences currencyTagMappingType="static">

<UserCurrencyPreference sessionVarValue="USD" displayText="Global Currency 1" currencyTag="int:USD"/>

<UserCurrencyPreference sessionVarValue="EUR" displayText="Global Currency 2" currencyTag="int:euro-l"/>

<UserCurrencyPreference sessionVarValue="AUD" displayText="Global Currency 3" currencyTag="int:AUD"/>

<!--UserCurrencyPreference sessionVarValue="lc1" displayText="Local Currency1" currencyTagSourceType="column" />

  <UserCurrencyPreference sessionVarValue="lc2" displayText="Local Currency2" currencyTagSourceType="column" /-->

</UserCurrencyPreferences>

</Config>

Step 2: Create session variable PREFERRED_CURRENCY

Next step is to create a session variable named PREFERRED_CURRENCY along with an initialization block to use the variable. Make sure to select “Enable any user” to set the value when the session variable is created.

Step 3: Create Logical Column with Currency Conversion

Typically currency conversion is calculated as part of an ETL process, there are options to calculate this conversion on the fly which I would try to cover in another blog entry in future.

A logical column ‘120 Converted Rev (Indexcol)’ has been defined which uses PREFERRED_CURRENCY session variable that display currency values to use appropriate conversion factor.

The logical column expression uses the value of NQ_SESSION.PREFERRED_CURRENCY variable to switch between the different currencies columns. The currency columns are expected to have pre-calculated converted values.

INDEXCOL( CASE  VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") WHEN 'USD' THEN 0 WHEN 'EUR' THEN 1 WHEN 'AUD' THEN 2 END , "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud")

The column with preferred currency expression needs to be added to the Presentation layer as shown below.

Step 4: Verification of Multicurrency support configuration

The first step is to verify My Account page, it should display list of all available options set for currencies, for this example the preferred currency is chosen as ‘Euro’ as shown below.

The second step for verification is to verify Analysis result. For this create a analysis with newly added column ‘120 Converted Rev (Indexcol)’ as below.

Select Column Properties > Data Format, overrides the default data format, and set Currency Symbol to User’s Preferred Currency.

The result displays the user’s preferred currency revenue that is been selected on the My Account page ( i.e. Euro in this example)