Designing and Building currency conversion application in Essbase - Hyperion

The Essbase currency conversion feature enables you to translate financial data from one currency into another. Currency conversion facilitates comparisons among countries and enables consolidation of financial data from locations that use different currencies.

For example, consider an organization that analyzes profitability data from the UK, reported in pounds, and from Japan, reported in yen. Comparing local currency profitability figures would be meaningless. To understand the relative contribution of each country, you must convert pounds into yen, yen into pounds, or both into another currency.

As another example, reporting total profitability for North America requires standardization of the local currency values that constitute the North America total. Assuming that the U.S., Mexico, and Canada consolidate into Total North America, the profitability total is meaningless if data is kept in local currencies. The Total North America sum is meaningful only if local currencies are converted to a common currency before consolidation.

To get a detail knowledge about currency application check the following Oracle docs.Click Here!!!

Currency :-

• Shown if the selected item is a dimension, and if the dimension is tagged as country.
• Enter the currency name, for example USD (for US dollars). When preparing a main database outline for currency conversion, you need to assign currency names to the country dimension and its members (for example, assigning dollars to the U.S., Euro to Germany, and so forth).
• Note: This setting only applies to Essbase (BSO) applications.

Currency Conversion :-

If the current member is a member of the accounts dimension and within a currency application, complete the following options.

Note:This setting only applies to Essbase (BSO) applications.

Currency conversion drop-down list box. Select one of the following currency conversion options to define categories of currency exchange rates.

• None: The member has no relationship to currency conversion. This is the default setting.
• No Conversion: The member is not converted because it is not a currency value. It can be a value such as a quantity or percentage.
• Category: The member requires currency conversion to the Category you specify.

Currency Category :-

If the current member is a member of the accounts dimension and the member requires currency conversion, enter the type of currency conversion required.

Currency category types are the names of members of the accounts dimension of the currency database.

Before proceeding you should be clear with all the above mentioned details.

Now we will be linking our sample application with the currency database.Go through the sample application before proceeding.

Now we have to make some changes in the sample application.

1. Change the dimension tag as Time from Period to Year just for our simplicity.

2. Make changes in the dimension tag as Account(Measures in our case) as shown below. Add Currency category to it.

3. Change the dimension type of market to country. Add more members to it as shown below. Also, add currency name.

3. Change the dimension type of Scenario to Currency Partition. Update the members to it as shown below. Local will have local currency values as per the market while Dollars will store converted values.

4. Now create a new database of currency type.

5. Update the outline including all the properties as shown below.(Add members to Year dimension same as you have added in your sample applications)

6. Open the Data preview grid and expand it in the following manner

• Year(Keep FY17 and FY18 members only for our simplicity.
• Currency Category
• Currency Type
• Currency Name

Now enter the data as shown below.

Note:- This random values are just for our understanding

Focus on P&L Currency category , USD and CAD. In FY17 1USD = 20CAD while in FY18 1USD = 2 CAD.

Now We will link our sample database with currency database. Follow the procedure shown below.

• Select database -> edit -> properties.
• In that go to currency tab.
• Select your currency database that you have created earlier.
• Select type of conversion method.
• Add default currency type member.

Now We will write the calculation script for converting local values into dollars.

Explanation :- Local data is available in Act(Scenario->Local). We will be copying the same data into Actual(Scenario->Dollar). We will fix the Actual and operate on it. 'CCONV "Act xchg" -> USD' will take the converted value from currency database and divide(As we are performing divide operation as mentioned above) with the values in Actual to get the converted values.

Enter the local data in your sample application as shown below

Run the calculation script.

So now you can see that all the local values are converted into Dollars and stored in Actual

Explanation :-

• FY17 -> Act -> Canada = 1000(while is a local value)
• FY17 -> Actual -> Canada = 50(which is a converted value)
• This converted value was obtained by dividing 1000 by 20(since in our currency database for P&L Currency category, In FY17 1USD = 20CAD)
• FY18 -> Act -> Canada = 1000(while is a local value)
• FY18 -> Actual -> Canada = 500(which is a converted value)
• This converted value was obtained by dividing 1000 by 2(since in our currency database for P&L Currency category, In FY17 1USD = 2CAD)

SHARE

Shubu

Hi. I’I hope this post was helpful for you.