Designing and Building Currency Conversion Applications

In This Section:

About Currency Conversion

About the Sample Currency Application

Structure of Currency Applications

Conversion Methods

Building Currency Conversion Applications and Performing Conversions

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

About Currency Conversion

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.

The Essbase installation includes the option to install the Sample currency application, which consists of two databases, Interntl and Xchgrate. If you do not have access to these databases, contact your Essbase administrator. See Setting Up Sample Applications.

About the Sample Currency Application

The Sample currency application builds on the business scenario introduced in Case Study: Designing a Single-Server, Multidimensional Database, as the Beverage Company (TBC) expands its business outside the U.S. TBC adds the following markets:

  • Three locations in Canada: Toronto, Vancouver, and Montreal

  • Four locations in Europe: the UK, Germany, Switzerland, and Sweden

In addition, TBC adds a new member, U.S., a consolidation of data from the U.S. regions: East, West, South, and Central.

Data for each TBC market location is captured in local currency. U.S. dollar values are derived by applying exchange rates to local values.

TBC must analyze actual data in two ways:

  • Actuals are converted at actual exchange rates.

  • Actuals are converted at budget exchange rates to analyze variances due to exchange rates.

After all actuals are processed, budget data is converted with budget exchange rates.

The TBC currency application consists of the main database (Interntl) and the currency database (Xchgrate). On Essbase Server, the databases are in the Sample application. If you do not have access to the databases, contact your Essbase administrator.

Structure of Currency Applications

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, 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 like any other Essbase database.

The relationship between the main database and the currency database is illustrated in Figure 44, Currency Application Databases.

Figure 44. Currency Application Databases

The image illustrates the relationship between the main database, shown on the left, and the currency database, shown on the right, as described in the text preceding the image.

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 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 also can 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 31, 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 uses a different currency and, therefore, must be assigned an individual currency name. However, the U.S. 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 currency, you must define a currency name for only the parent member.

    Table 31. Interntl Database Currency Names

    Dimensions and Members

    Currency Name

    Market - Country

    U.S.

    East

    West

    South

    Central

    USD (U.S. dollar)

    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 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 Oracle Essbase Spreadsheet Add-in 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 usually are 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. Table 32 lists the currency names in the CurName dimension:

    Table 32. Xchgrate Database Currency Names

    Dimension and Members

    Alias Name

    CurName - Country

    USD

    CND

    GBP

    EUR

    CHF

    SEK

    U.S. dollar

    Canadian dollar

    British pound

    Euro

    Swiss franc

    Swedish krona

  • 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, 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 need not 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.

Conversion Methods

Different currency applications have different conversion requirements. Essbase supports two conversion methods:

  • Overwriting local values with converted values.

    Some applications require that only converted values be stored in the main database. Local values are entered, and the conversion operation overwrites local values with common currency values. This method assumes that there is no requirement for reporting or analyzing local currencies.

    Because this operation overwrites data, load local values and recalculate the data each time you perform a conversion. This method is useful only for single (not ongoing) conversions.

  • Keeping local and converted values.

    Most applications require that data be stored in both local and common currency (converted) values. This method permits reporting and analyzing local data, and data modifications and recalculations are easier to control. To use this method, define a currency partition (see Main Database).

Either of these methods may require a currency conversion at report time. Report time conversion enables analysis of various exchange rate scenarios without actually storing data in the database. The currency conversion module enables performance of ad hoc conversions. You perform ad hoc conversions by using Spreadsheet Add-in, as discussed in the Oracle Essbase Spreadsheet Add-in User's Guide, or by using a report script, as discussed under Converting Currencies in Report Scripts.

Building Currency Conversion Applications and Performing Conversions

To build a currency conversion application and perform conversions, use the following process:

  1. Create or open the main database outline.

    See Creating Main Database Outlines.

  2. Prepare the main database outline for currency conversion.

    See Preparing Main Database Outlines.

  3. Generate the currency database outline.

    See Generating Currency Database Outlines.

  4. Link the main and currency databases.

    See Linking Main and Currency Databases.

  5. Convert currency values.

    See Converting Currency Values.

  6. Track currency conversions.

    See Tracking Currency Conversions.

  7. If necessary, troubleshoot currency conversion.

    See Troubleshooting Currency Conversion.

Creating Main Database Outlines

To create a main database outline, create or open an Essbase database outline, modify the outline as needed, and save the outline for use in the currency conversion application.

*  To create an outline or open an existing outline, use a tool:

Tool

Topic

Location

Administration Services

Opening and Editing Outlines

Oracle Essbase Administration Services Online Help

MaxL

create database

Oracle Essbase Technical Reference

ESSCMD

CREATEDB

Oracle Essbase Technical Reference

    Preparing Main Database Outlines

    After you create or open the main database outline, modify dimensions and members to enable Essbase to generate the currency database outline automatically. See Main Database.

    *  To prepare a main database outline, see “Preparing the Main Database Outline for Currency Conversion” in the Oracle Essbase Administration Services Online Help.

      Generating Currency Database Outlines

      After you verify and save the main database outline, you can generate the currency outline. The currency outline contains dimensions, members, currency names, and currency categories previously defined in the main database outline. The currency database outline is basically structured and ready to use after being generated but may require additions to make it complete.

      *  To generate a currency database outline, see “Generating a Currency Database Outline” in the Oracle Essbase Administration Services Online Help.

        Linking Main and Currency Databases

        To perform a currency conversion calculation, Essbase must recognize a link between the main and currency databases. Generating a currency outline does not automatically link a main database with a currency database. When you link the databases, you specify the conversion calculation method and the default currency type member.

        *  To link main and currency databases, see “Linking a Database to a Currency Database” in the Oracle Essbase Administration Services Online Help.

          Converting Currency Values

          After you create a currency conversion application, you convert data values from a local currency to a common, converted currency by using the CCONV command in calculation scripts. For example, you might convert data from a variety of currencies into USD (U.S. dollars). You can convert the data values back to the original, local currencies by using the CCONV TOLOCALRATE command.

          You can convert all or part of the main database using the rates defined in the currency database. You can overwrite local values with converted values, or you can keep both local and converted values in the main database, depending on your tracking and reporting needs.

          Note:

          When running a currency conversion, ensure that the data being converted is not simultaneously being updated by other user activities (for example, a calculation, data load, or currency conversion against the same currency partition). Concurrent activity on the data being converted may produce incorrect results. Essbase does not display a warning message in this situation.

          Note:

          When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty for the purposes of Intelligent Calculation. Thus, Essbase recalculates all converted blocks when you recalculate the database.

          To see sample currency conversion calculation scripts, see the Oracle Essbase Technical Reference.

          Overwriting Local Values with Converted Values

          To overwrite local values, you need not create a currency partition dimension in the main database. Use the CCONV command in a calculation script to convert all data in the database:

          The following calculation script converts the values in the database to USD:

          CCONV USD; 
          CALC ALL;

          If required, you can specify a currency name that contains the required exchange rate. The following calculation script converts the values in the database to USD, using the exchange rate for Jan as defined in the currency database:

          CCONV Jan->USD;
          CALC ALL;

          The CALC ALL command is required in the examples shown because the CCONV command converts only currencies. It does not consolidate or calculate members in the database.

          The following calculation script uses the “Act xchg” rate to convert the converted values back to their original local currency values:

          CCONV TOLOCALRATE "Act xchg";
          CALC ALL;

          Note:

          You cannot use the FIX command unless you are using a currency partition dimension and the CCTRACK setting is TRUE in the essbase.cfg file.

          Keeping Local and Converted Values

          You can keep local and converted values in a database. In the main database, you must define the members that store the local and converted values by creating a currency partition dimension (see Main Database). The currency partition dimension has one partition for local values and one for converted values.

          *  To create a calculation script that copies local data to a converted partition and calculates the data:

          1. Use the DATACOPY command to copy data from the local to the converted partition.

          2. Use the FIX command to calculate only the converted partition and use the CCONV command to convert the data.

            Note:

            When using a currency partition dimension, you must FIX on a member of the dimension to use the CCONV command.

          3. Use the CALC command to recalculate the database.

          The following example is based on the Sample.Interntl database and the corresponding Sample.Xchgrate currency database. Figure 45, Calculating Local and Converted Currency Conversions shows the currency partition from the Sample.Interntl database.

          Figure 45. Calculating Local and Converted Currency Conversions

          The image shows the currency partition from the Sample.Interntl database.

          The following calculation script performs three currency conversions for Actual, Budget, and Actual @ Bud Xchg data values:

          /* Copy data from the local partition to the master partition (for converted values) */
          DATACOPY Act TO Actual;
          DATACOPY Bud TO Budget;
          /* Convert the Actual data values using the "Act xchg" rate */
          FIX(Actual)
             CCONV "Act xchg"->US$;
          ENDFIX
          
          * Convert the Budget data values using the "Bud xchg" rate */
          FIX(Budget)
             CCONV "Bud xchg"->US$;
          ENDFIX
          
          /* Convert the "Actual @ Bud XChg" data values using the
          "Bud xchg" rate */
          FIX("Actual @ Bud XChg")
             CCONV "Bud xchg"->US$;
          ENDFIX
          
          /* Recalculate the database */
          CALC ALL;
          CALC TWOPASS;
          

          The following calculation script converts the Actual and Budget values back to their original local currency values:

          FIX(Actual)
             CCONV TOLOCALRATE "Act xchg";
          ENDFIX
          FIX(Budget)
             CCONV TOLOCALRATE "Bud xchg";
          ENDFIX
          CALC ALL;

          Note:

          When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty for the purposes of Intelligent Calculation. Thus, Essbase recalculates all converted blocks when you recalculate the database.

          Calculating Databases

          If you execute a CALC ALL command to consolidate the database after running a conversion, meaningful total-level data is generated in the converted base rate partition, but the local rate partition contains a meaningless consolidation of local currency values. To prevent meaningless consolidation, use the calculation command SET UPTOLOCAL, which restricts consolidations to parents with the same defined currency. For example, all cities in the U.S. use dollars as the unit of currency. Therefore, all children of U.S. consolidate to U.S. Consolidation stops at the country level, however, because North America contains countries that use other currencies.

          Converting Currencies in Report Scripts

          You can convert currencies in report scripts, using the CURRENCY command to set the output currency and the currency type. For the syntax and definitions of Report Writer commands, see the Oracle Essbase Technical Reference.

          Note:

          Essbase cannot perform ”on the fly” currency conversions across transparent databases. If two transparent partition databases are calculated using different conversions, you cannot perform currency conversions in reports.

          The following Sample report contains first-quarter Budget Sales for colas, using the January exchange rate for the Peseta currency.

                                       Illinois Sales Budget 
          
                                     Jan        Feb        Mar 
                                     ========   ========   ======== 
               100-10                   3          3          3 
               100-20                   2          2          2 
               100-30                #Missing   #Missing   #Missing 
               100                      5          5          5 
                           Currency: Jan->Peseta->Act xchg 
          
                           Currency: Jan->Peseta->Act xchg 
          
                                       Illinois Sales Budget 
          
                                     Jan        Feb        Mar 
                                     ========   ========   ======== 
               100-10                  3           3          3 
               100-20                  2           2          2 
               100-30                #Missing   #Missing   #Missing 
               100                     5           5          5

          Use the following script to create the Sample currency conversion report:

          <Page (Market, Measures, Scenario)
          {SupCurHeading}
          Illinois Sales Budget
                 <Column (Year)
                 <children Qtr1
          <Currency "Jan->Peseta->Act xchg"
          <Ichildren Colas
             !
          {CurHeading}
          Illinois Sales Budget
                 <Column (Year)
                 <children Qtr1
             !

          Tracking Currency Conversions

          You can use the CCTRACK setting in the essbase.cfg file to control whether Essbase tracks the currency partitions that have been converted and the exchange rates that have been used for the conversions. Tracking currency conversions has the following advantages:

          • Enables conversion to occur at report time through Spreadsheet Add-in or Report Writer

          • Enables conversion of a converted currency back to its original, local rate using the CONV TOLOCALRATE command

          • Prevents data inaccuracies due to accidental reconversion of data during a currency calculation

          By default, CCTRACK is turned on. Essbase tracks which currency partitions have been converted and which have not. The tracking is done at the currency partition level: a database with two partitions has two flags, each of which can be “converted” or “unconverted.” Essbase does not store a flag for member combinations within a partition.

          When using a currency partition, and when CCTRACK is set to TRUE (the default) in essbase.cfg, you must FIX on a single currency partition member. You cannot FIX on multiple members, because CCTRACK works at the currency partition member level and marks as converted or unconverted all data associated with the currency partition member. For example, in the Sample.Basic database, the following example is valid:

          FIX(Actual)
          CCONV "Act xchg"->US$;
          ENDFIX

          In the Sample.Basic database, if you were able to use a FIX command to convert the actual values for only the members Jan and Feb, the database would have converted and unconverted data in the same currency partition, causing a data consistency issue.

          Reasons to Turn Off CCTRACK

          For increased efficiency when converting currency data between currency partitions, consider turning off CCTRACK. For example, if you load data for the current month into the local partition, use the DATACOPY command to copy the entire currency partition that contains the updated data, and then run the conversion on the currency partition.

          Note:

          Always do a partial data load to the local partition and use the DATACOPY command to copy the entire currency partition to the converted partition before running the currency conversion. Updating data directly into the converted partition causes incorrect results.

          Methods for Turning Off CCTRACK

          You can turn off CCTRACK in three ways:

          • Use the SET CCTRACKCALC ON|OFF command in a calculation script to turn off CCTRACK temporarily. You can use this command at calculation time to increase flexibility and efficiency during currency conversion.

          • Use the CLEARCCTRACK calculation command to clear the internal exchange rate tables created by CCTRACK. You can use the command inside a FIX statement to clear the exchange rates for a currency partition. Use the command after a data load to reset the exchange rate tables so that they are ready for future currency conversion calculations.

          • Set CCTRACK to FALSE in the essbase.cfg file. Setting CCTRACK to False turns off the tracking system and has the following results:

            • The CCONV command assumes that data is unconverted (is in local currency). If you accidentally run the CCONV command multiple times on the same data, the resulting data is inaccurate.

            • Similarly, the currency report options assume that the data is unconverted (is in local currency). If the data already has been converted in the database, it is reconverted at report time, resulting in inaccurate data.

            • The restrictions on using the FIX and DATACOPY commands in currency conversions do not apply.

              Note:

              When running a currency conversion, ensure that the data being converted is not simultaneously being updated by other user activities (for example, a calculation, data load, or currency conversion against the same currency partition). Concurrent activity on the data being converted may produce incorrect results. Essbase does not display a warning message in this situation.

          Troubleshooting Currency Conversion

          See “Troubleshooting Currency Conversion” in the Oracle Essbase Administration Services Online Help.