Designing and Building Currency Conversion Applications Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Designing and Building Currency Conversion Applications


You use the Analytic Services currency conversion feature to translate financial data from one currency into another currency. Currency conversion facilitates comparisons among countries, and enables consolidation of financial data from locations that use different currencies. This feature can be licensed as an "add-on" to Analytic Server.

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 side-by-side in a spreadsheet is meaningless. To understand the relative contribution of each country, you need to convert pounds into yen, yen into pounds, or both figures 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 United States, 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 prior to consolidation.

The Analytic Services 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 Analytic Services administrator. For information about installing Sample currency applications, see the Essbase Analytic Services Installation Guide.

Note: The information in this chapter is not relevant to aggregate storage databases. For detailed information on the differences between aggregate and block storage, see Comparison of Aggregate and Block Storage.

This chapter contains the following topics:

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 United States. TBC adds the following markets:

In addition, TBC adds a new member, US, which is a consolidation of data from the United States 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 needs to analyze actual data in two ways:

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 Analytic Server, the databases are in the Sample application. If you do not have access to the databases, contact your Analytic Services administrator. For information about installing Sample currency applications, see the Essbase Analytic Services Installation Guide.

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.

Analytic Services holds the exchange rates required for currency conversion in a separate currency database. The currency database outline, which is automatically generated by Analytic Services 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 Analytic Services database.

The relationship between the main database and the currency database is illustrated in Figure 66.

Figure 66: Currency Application Databases

Main Database

To enable Analytic Services 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:

When preparing a main database outline for currency conversion, you can create an optional currency partition to tell Analytic Services 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 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 Analytic Services 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:

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. Analytic Services supports two conversion methods:

Either of these two methods may require a currency conversion to be applied 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 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, you need to create or open an Analytic Services database outline, modify the outline as needed, and then save the outline for use in the currency conversion application.

To create a new outline or open an existing outline, use any of the following methods:


Tool
Topic
Location

Administration Services

Opening and Editing Outlines

Essbase Administration Services Online Help

MaxL

create database

Technical Reference

ESSCMD

CREATEDB

Technical Reference



Preparing Main Database Outlines

After you create or open the main database outline, you need to modify dimensions and members to enable Analytic Services to generate the currency database outline automatically. For more information, see Main Database.

To prepare a main database outline, see "Preparing the Main Database Outline for Currency Conversion" in 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 Essbase Administration Services Online Help.

Linking Main and Currency Databases

To perform a currency conversion calculation, Analytic Services 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 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. Analytic Services 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, Analytic Services recalculates all converted blocks when you recalculate the database.

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

Overwriting Local Values with Converted Values

If you want to overwrite local values, you do not need to 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 only converts 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 both local and converted values in a database. In the main database you need to define the members that store the local and the converted values. You define the members by creating a currency partition dimension (see Main Database). The currency partition dimension has two partitions, one 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, use the following process:

  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 67 shows the currency partition from the Sample Interntl database.

Figure 67: Calculating Local and Converted Currency Conversions

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, Analytic Services 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 US use dollars as the unit of currency. Therefore, all children of US consolidate to US. 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 Technical Reference.

Note: Analytic Services cannot perform "on the fly" currency conversions across transparent databases. If you have two transparent partition databases that 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 Analytic Services 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:

By default CCTRACK is turned on. Analytic Services 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 either "converted" or "unconverted." Analytic Services does not store a flag for member combinations within a partition. When CCTRACK is turned on, the following restrictions apply:

Reasons to Turn Off CCTRACK

For increased efficiency when converting currency data between currency partitions, you may want to turn off CCTRACK. For example, 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:

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. Analytic Services does not display a warning message in this situation.

Troubleshooting Currency Conversion

For information about how to troubleshoot currency conversions, see "Troubleshooting Currency Conversion" in Essbase Administration Services Online Help.



Hyperion Solutions Corporation link