Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
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:
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.
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
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:
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.
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, 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.
Dimensions and Members |
Currency Name |
---|---|
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.
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:
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).
In the Sample Xchgrate database, the country dimension is CurName. CurName contains the following currency names:
Dimension and Members |
Alias Name |
---|---|
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).
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.
Different currency applications have different conversion requirements. Analytic Services supports two conversion methods:
Some applications require only converted values to 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, 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 an ongoing) conversion.
Most applications require data to be stored in both local and common currency (converted) values. This method permits reporting and analyzing local data. In addition, data modifications and recalculations are easier to control. To use this method, you must define a currency partition (see Main Database).
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.
To build a currency conversion application and perform conversions, use the following process:
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 |
---|---|---|
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.
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.
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.
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.
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.
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:
Note: When using a currency partition dimension, you must FIX on a member of the dimension to use the CCONV command.
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.
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.
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.
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 !
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:
FIX(Actual) CCONV "Act xchg"->US$; ENDFIX]
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.
You can turn off CCTRACK in three ways:
essbase.cfg
file. Setting CCTRACK to False turns off the tracking system and has the following results: 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.
For information about how to troubleshoot currency conversions, see "Troubleshooting Currency Conversion" in Essbase Administration Services Online Help.
![]() |