Organizations with offices in different countries generally do business in the currency of the host country (known as the local currency). World and regional headquarters of such organizations must convert data entered in local currencies to a common currency for consolidation and analysis. The currency conversion option is designed to meet the needs of this complex business problem. This option can be licensed as an "add-on" to OLAP Server.
The currency conversion option includes a sample currency application that contains two databases: INTERNTL and XCHGRATE. This chapter provides background information, step-by-step instructions for building the sample currency application, and procedures for calculating and reporting currency conversions after you have built your own currency application.
This chapter contains the following topics:
Note: Your Essbase installation includes the option to install the Sample databases, INTERNTL and XCHGRATE. If you don't have access to these databases, contact your Essbase administrator. For information about installing sample applications, see the Essbase Installation Guide.
This chapter focuses on solving the business problems that arise as the sample company, The Beverage Company (TBC), expands its business outside the United States. This chapter builds on the business scenario introduced in Case Study: Designing a Single-Server Application, as 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. Dollar values are derived by applying exchange rates to local values. These values must then be converted to a common currency (in this case, US$).
TBC needs to analyze actual data in two ways:
When all actuals have been processed, budget data is converted with budget exchange rates.
Currency conversion applications are defined by linking a currency database to a main application database, as illustrated in Figure 114. The main database contains the full database outline and associated data values; the currency database contains only exchange rates and other currency-related information.
In the example provided in this chapter, TBC's main database is INTERNTL and the currency database is XCHGRATE. On your server, these databases are in the Sample application.
Figure 114: Currency Application Databases
The main database can be from 3 to n dimensions in size. At a minimum, the database must contain the following dimensions:
For example, P&L accounts may use exchange rates that differ from those used with balance sheet accounts. In addition, some accounts may not require conversion. For example, members such as Units, Headcount, and Margin% require no conversion.
Because many 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 20, TBC uses only six currency names for its 15 Market dimension members. The children of the member Europe all use a different currency and, therefore, must be assigned individual currency names. However, in the case of the Country dimension and the US member and its four cities, all locations use the same currency. The same is true of the Canada member and its three cities. When the children of a given member share a single currency, you only need to define a currency name for the parent member.
Dimensions and Members |
Currency Name |
---|---|
A typical main database also contains an optional fourth dimension: "A dimension defined with a currency partition." Databases are usually partitioned in a Scenario dimension. Creating a currency partition allows Essbase to internally track currency relationships and previously-converted values of the main and exchange rates databases.
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 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 also let you define reports that convert data when the report is displayed, as discussed under Converting Data to a Different Currency in Reports.
The currency database requires the following three dimensions:
A currency database typically includes an optional fourth dimension: "A Currency Type dimension" which contains members that identify various currency scenario types. Typically an application has different exchange rates for scenarios, such as actual, budget, and forecast. Members of the currency-type dimension are not directly mapped to members of the main database. Therefore, member names in this dimension are not required to match member names of the main database.
Different currency applications have different conversion requirements. Essbase supports two currency database types, each with a different conversion method:
Because this operation overwrites data, you must load local values and recalculate the data each time you perform a conversion. This method is useful only when you want to perform a single (not ongoing) conversion.
Either of these two methods may also require a currency conversion to be applied at report time. This allows you to analyze various exchange rate scenarios without actually storing data in the database. The currency conversion module allows you to perform ad hoc conversions with the Spreadsheet Add-in, which is discussed in the Essbase Spreadsheet Add-in User's Guide, or with a report script, as discussed under Converting Data to a Different Currency in Reports.
To accommodate the new markets TBC has added and to provide Essbase with required currency-related information, TBC has modified their existing Essbase database outline and created a currency conversion application. You can use the TBC model to create your own currency conversion application, revising the steps as needed to fit your specific requirements.
The TBC currency conversion application was created using the following steps:
To create the main database outline (INTERNTL), you need to open your existing Essbase database outline, make changes to its contents, and then save the outline for use in your currency conversion application.
Begin creating the main database outline by opening TBC's existing outline, as follows:
TBC has modified the Measures, Market, and Scenario dimensions. The Year and Product dimensions require no changes because they have no information specifically related to currencies.
Note: The Year dimension must be tagged as the Time dimension.
To create their currency conversion main database, TBC modified the Measures dimension using the following steps. You can follow along and revise the procedures as necessary to create your own currency application.
Note: 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 defined.
You must have a dimension tagged as Accounts in your main database. To meet varying conversion and exchange rate requirements, individual members within the Accounts dimension can then be tagged with different categories of exchange rates.
All descendants of the Profit member, for example, use a special currency rate that applies to Profit and Loss accounts. Therefore, they are defined with a currency category of P&L.
Note: The children of a member defined with No Conversion do not inherit the No Conversion definition. Each member must be defined individually.
Figure 116 shows the resulting Measures dimension outline after making the modifications necessary for the TBC currency conversion application.
Figure 116: TBC Measures Main Database Outline
TBC needed to make the following modifications to the Market dimension to accommodate their newly added markets and create their currency conversion application main database. Procedures for making these changes follow the bullet list.
Modify the Market dimension as follows:
The existing members (East, West, South, and Central) belong in TBC's newly added US market. To make this outline change, TBC added the US member with the existing members as descendants.
All descendants of the Market dimension inherit the US$ currency name (unless a child branch has already been defined with another currency). Because members in the US branch use US$ as their local currency, there is no need to define a currency name for these members.
The children of Europe all use different local currencies. Unlike the Market dimension and the Canada member, you must define each Europe member with an individual currency name.
Figure 117 shows the resulting Market dimension outline after making the modifications necessary for the TBC currency conversion application.
Figure 117: TBC Market Dimension Database Outline
In the TBC main database (INTERNTL), the Scenario dimension contains members for both local and converted values. TBC needed to make the following modifications to the Scenario dimension to create their currency conversion application main database:
Modify the Scenario dimension as follows:
Values for the Act member consolidate to Local, but values for the Bud member do not.
Figure 118 shows the resulting Scenario dimension outline after making the modifications necessary for the TBC currency conversion application.
Save the outline changes by clicking the Save button. If your database contains data values, Essbase restructures the database to reflect changes to the outline. When you click Save, the Restructure Database dialog box shown in Figure 119 is displayed.
Figure 119: Restructure Database Dialog Box
When an existing outline is updated, you have several choices that let you restructure data values. Because no data has been loaded into the model, click OK to continue the outline update.
Once you have verified and saved the main database outline, you can generate the currency outline. The currency outline contains dimensions, members, currency names, and currency categories previously defined in your main database outline. It is basically structured and ready to use after being generated but may require additions to make it complete.
Follow these steps to generate the currency outline:
If the main database outline is missing any required dimension tags (such as Time, Accounts, or Country), Essbase displays an error box like the one shown in Figure 120.
Figure 120: Error Dialog Box Displayed When No Country Dimension Is Defined
Figure 121: Generate Currency Outline Dialog Box
The Generate Currency Outline dialog box lets you define the database for which the outline is generated. The dialog box contains the following controls:
If you need to log in to a different server, click the Connect button. The dialog box shown in Figure 122 is displayed. If you do not need to log in to a different server, go to Step 5c.
Figure 123: Create Currency Database Dialog Box
The outline for the currency database XCHGRATE is displayed in the Outline Editor as shown in Figure 124.
After generating your currency database outline, review its contents and note the following items that are automatically created based on the contents of the main database that you defined previously:
After you have generated the currency database, you can add members to any dimension. Because the TBC currency database (XCHGRATE) contained different exchange rates for actual and budget, they added two new members to the CurType dimension to apply to different scenarios in the main database:
Exchange rates from these two members are applied to different scenarios from the main database, as follows:
Note: For details about the currency conversion calculation process, see Calculating Currency Conversions.
When you have reviewed the newly generated currency database outline and have made any necessary additions, you need to save your changes.
Figure 125: Using the Latest Version on the Server Warning
Because the newly-generated outline contains all new members, you are given a choice of continuing or canceling the operation.
Figure 126: Restructure Database Dialog Box
When an existing outline is updated, you have several choices that let you restructure data values.
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.
To link a currency database to the main database in Application Manager:
Figure 127: Database Settings Dialog Box General Page
Figure 128: Database Settings Dialog Box Currency Page
The Multiply and Divide options are enabled. Use these option buttons to define the conversion calculation method to be used. The Multiply option button multiplies each local data value by the exchange rate. The Divide option button divides each local data value by the exchange rate.
After the databases are defined and linked, the next step is to load data into the main database and exchange rates into the currency database. After values have been entered, a currency conversion is calculated. This is accomplished by running a calculation script (see Calculating Currency Conversions) or by using the Database Properties window in Administration Services.
You convert data values from a local currency to a common, converted currency using the CCONV currExchMbr command in a calculation script. For example, you might convert data from a variety of European currencies into US$.
Note: You cannot use the CCONV command to convert data in a transparent partition.
You convert the data values back to the original, local currencies using the CCONV TOLOCALRATE CurType command.
You can convert all or part of your main database using the rates defined in your currency database. You can overwrite the local values with the converted values, or you can keep both the local and converted values in your main database, depending on your tracking and reporting needs.
If you want to overwrite the local values, you do not need to create a CURPARTITION dimension in your main database. Use the CCONV command in a calculation script to convert all the data in your database.
The following calculation script converts the values in the database to US$.
CCONV US$; 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 US$, using the exchange rate for Jan as defined in the currency database.
CCONV Jan->US$; 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 your database.
The following calculation script converts the values back to their original values in their local currencies using the "Act xchg" rate:
CCONV TOLOCALRATE "Act xchg"; CALC ALL;
For more information on the CCONV command, see the Technical Reference in the docs directory.
Note: You cannot use the FIX command unless you are using a CURPARTITION dimension and the CCTRACK setting is TRUE in the ESSBASE.CFG file.
You can keep both local and converted values in your database. In your main database you need to define the members that store the local and converted values. You do this by creating a CurPartition dimension (see Modify the Scenario Dimension). The CurPartition dimension has two partitions, one for local values and one for converted values.
To create a calculation script that copies local data to the converted partition and calculates it, use this procedure:
Note: When using a CurPartition dimension, you must FIX on a member of this dimension to use the CCONV command.
The following example is based on the Sample INTERNTL database and corresponding Sample XCHGRATE currency database. Figure 129 shows the Currency Partition from the Sample INTERNTL database.
Figure 129: 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 values in their local currencies:
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. This means that Essbase recalculates all the converted blocks when you recalculate your database. For more information on Intelligent Calculation, see Optimizing with Intelligent Calculation.
You can calculate currency conversions in report scripts, using the CURRENCY target currency command to set the output currency and currency type. For the syntax and definitions of Report Writer commands, see the Technical Reference in the docs directory.
Note: Essbase 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 calculate currency conversions in reports.
The following Sample report contains first quarter Budget Sales for colas, using the January exchange rate for the Peseta currency.
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 !
The CCTRACK setting in your ESSBASE.CFG file controls whether exchange rates are tracked while Essbase calculates currency conversions.
When CCTRACK is True, Essbase tracks exchange rates that are applied to data as conversions are calculated, allowing conversion to occur at report time through the Spreadsheet Add-in or the Report Writer.
Setting CCTRACK to False turns off the tracking system and has the following results:
For more information, refer to Technical Reference in the docs directory.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |