In a business application requiring currency conversion, the main database is divided into at least two slices. One slice handles input of the local data, and another slice holds a copy of the input data converted to a common currency.
Essbase holds the exchange rates required for currency conversion in a separate currency database. The currency database outline, which is automatically generated by Essbase from the main database after you assign the necessary tags, typically maps a given conversion ratio onto a section of the main database. After the currency database is generated, it can be edited just like any other Essbase database.
The relationship between the main database and the currency database is illustrated in Figure 42, Currency Application Databases .
Figure 42. Currency Application Databases
Main Database
To enable Essbase to generate the currency database outline automatically, you modify dimensions and members in the main database outline. In the Sample currency
application, the main database is Interntl.
The main database outline can contain from 3 to n dimensions. At a minimum, the main database must contain the following dimensions:
• A dimension tagged as time. Tagging a dimension as time generates a dimension in the currency database that is identical to the time dimension in the main database. In the Sample Interntl database, the dimension tagged as time is Year.
• A dimension tagged as accounts. Tagging a dimension as accounts and assigning currency categories to its members creates a dimension in the currency database that contains members for each of the individual currency categories. Category assignment enables the application of different
exchange rates to various accounts or measures. In the Sample Interntl database, the dimension tagged as accounts is Measures.
Each descendant of a member inherits the currency category tag of its ancestor. A member or sub-branch of members can also have its own category.
For example, profit and loss (P&L) accounts may use exchange rates that differ from the rates used with balance sheet accounts. In addition, some accounts may not require conversion. For example, in the Sample Interntl database, members such as Margin% and Profit% require no conversion. You tag members not to be converted as No Conversion. The No Conversion tag is not inherited.
• A market-related dimension tagged as country. Tagging a dimension as country and assigning currency names to individual countries creates a member in the currency database for each currency. In the Sample Interntl database, the Market dimension is tagged as country. The currency name for this dimension is USD (U.S. dollars), because all local currencies must be converted to USD, the company’s common currency.
Because multiple members can have the same currency name, the number of currency names is typically less than the total number of members in the dimension. As shown in Table 18, Interntl Database Currency Names , the
Sample Interntl database uses only six currency names for the 15 members in the Market dimension. Each of the children of the member Europe use a different currency and, therefore, must be assigned an individual currency name. However, the US dimension and its four regional members all use the same currency. The same is true of the Canada member and its three city members. When the
children of a given member share a single currency, you need to define a currency name for only the parent member.
Table 18. Interntl Database Currency Names Dimensions and Members Currency Name Market - Country
Dimensions and Members Currency Name Canada
Toronto Vancouver Montreal
CND (Canadian dollar)
Europe UK Germany Switzerland Sweden
GBP (British pound) EUR (Euro)
CHF (Swiss franc) SEK (Swedish krona)
When preparing a main database outline for currency conversion, you can create an optional currency partition to tell Essbase which slice of the database holds local currency data and which slice of the database holds data to be converted. The dimension that you tag as currency partition contains members for both local currency values and converted values. Local currency data is converted to common currency data using currency
conversion calculation scripts. In the Sample Interntl database, the Scenario dimension is the currency partition dimension.
For instructions on how to use currency partition dimensions, see Keeping Local and Converted Values.
Note:
A currency conversion partition applies only to the Currency Conversion option. It is not related to the Partitioning option that enables data to be shared between databases by using a replicated, linked, or transparent partition.
The Essbase Spreadsheet Add-in for Excel User's Guide provides examples of ad hoc currency reporting capabilities. Report scripts enable the creation of reports that convert data when the report is displayed, as discussed under Converting Currencies in Report Scripts.
Note:
For a list of methods used to create the main database outline, see Creating Main Database Outlines.
Currency Database
By assigning currency tags to members in the main database outline, you enable Essbase to generate the currency database automatically. In the Sample currency application, the currency database is Xchgrate.
A currency database always consists of the following three dimensions, with an optional fourth dimension:
• A dimension tagged as time, which is typically the same as the dimension tagged as time in the main database. This allows the currency database to track currency fluctuations over time and to accurately convert various time slices of the main database. In the Sample Xchgrate database, the dimension tagged as time is Year.
Each member of the time dimension in the main database must be defined in the currency database. Values by time period in the main database are usually
converted to the exchange rates of their respective time period from the currency database (although you can convert data values against the exchange rate of any period).
• A dimension tagged as country, which contains the names of currencies relevant to the markets (or countries) defined in the main database. Each currency name defined in the main database must also exist in the currency database. The currency names define the country-to-exchange rate mapping when conversion occurs.
In the Sample Xchgrate database, the country dimension is CurName. CurName contains the following currency names:
Table 19. Xchgrate Database Currency Names Dimension and Members Alias Name
CurName - Country
• A dimension tagged as accounts, which enables the application of various rates to members of the dimension tagged as accounts in the main database.
The categories defined for the accounts dimension in the main database are used to form the members in the accounts dimension of the currency database. For example, it may be necessary to convert Gross Profit and Net Profit using one category of rates, while other accounts use a different set of rates.
In the Sample Xchgrate database, the dimension tagged as accounts is
CurCategory, and the account categories included are P&L (Profit & Loss) and B/S (Balance Sheet).
• A currency database, which typically includes an optional currency type dimension, which enables different scenarios for currency conversion.
Typically, an application has different exchange rates for different scenarios, such as actual, budget, and forecast. To convert data between scenarios, simply select which type of rate to use.
The currency type dimension is created when you generate the currency outline and is not directly mapped to the main database. Therefore, member names in this dimension are not required to match member names of the main database.
In the Sample Xchgrate database, the currency type dimension is CurType.
CurType includes actual and budget scenarios.
Note:
For information about creating the currency database outline, see Building Currency Conversion Applications and Performing Conversions.