Monday, 30 April 2012

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

Oracle Business Intelligence Enterprise Edition 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
·         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’.
<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" /-->

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)

1 comment:

  1. Hi Pravin,

    I know this post is old, hope you will reply anyway.

    We followed your post and enabled everything.
    But we have an additional problem to solve: decimal places.

    In MyAccount, the user wants 3 options:
    - option 1: Global Currency
    - option 2: Local Currency (2 decimals)
    - option 3: Local Currency (3 decimals)

    They don't want to show any symbol in reports (like $ or USD), just see the numbers with the proper number of decimal places.

    So we created 3 entries in userpref_currencies.xml file:

    Note that the value passed to the variable is the same for the two Local Currency entries, so the values in report will not change.

    Then we added the corresponding 3 entries in currencies.xml file:

    Then we create an analysis and we change the "Currency Symbol" to "User's Preferred Currency" as you are suggesting.

    Values in the analysis are calculated correctly and accordingly to the selection in MyAccount page, but the decimals shown are always 2, aslo if we select the 3rd option, which should be configured for 3 decimals, accordingly with the "int:cust_L_3" entry in the currency.xml file.

    Are we missing something?
    Do you have any suggestion?

    Thank you.