Understanding Currency Conversion
Companies spanning national boundaries often experience problems handling multiple currencies, as well as problems providing a unified view of their organization's data. This can occur because transactional data can be recorded in any currency in which the company transacts business.
To overcome this disparity, information in MDW fact tables is kept in more than one currency: the source currency and up to two additional currencies. To process data in more than one currency, you must potentially convert transactions from one currency to another currency. You do this using the PeopleSoft MDW Currency Conversion utility, an ETL process that you run after you populate the MDW.
For data analysis and simulation in EPM, for proper engine processing to occur, you must convert monetary amounts to a single currency for each business unit. For reporting in EPM, you must convert the amounts to a single currency, sometimes regardless of the business unit, to provide a unified view of data. For these reasons, the MDW currency conversion utility has been created.
Note: The MDW currency conversion process discussed in this topic populates MDW tables only. Do not confuse this process with the currency conversion application engine process that populates the OWE and is used with the Analytic Applications.
MDW Table Structure Used to Support Currency Conversion
Every source amount that is stored in an MDW fact table must have a corresponding source currency code field in that fact table. Additionally, because each fact table can carry the source currency code and up to two additional currencies, each fact table can have up to two additional currency codes. Therefore, each source amount in a fact table has a corresponding reporting1 amount and a reporting2 amount. Because all currency amount columns must have a corresponding currency code, each reporting1 and reporting2 amount must have a respective reporting currency code1 and reporting currency code2. Following is an example of currency and currency code fields in a fact table showing the transaction currency (AMOUNT column), its currency code (AMT_CD) and reporting1 and 2 amount columns, and their respective currency code columns:
AMOUNT |
AMT_CD |
RPT_AMT1 |
RPT_AMT1_CD |
RPT_AMT2 |
RPT_AMT2_CD |
---|---|---|---|---|---|
100 |
USD |
517 |
FFR |
79 |
EUR |
Base amount and base currency code fields can also exist in the MDW fact table. However, they exist only if the source table has the corresponding base amount and base currency code fields, and only if a currency conversion process was run on that database. The base amount and transaction amount are considered source amounts.
The ETL process that populates the MDW fact tables does not populate the reporting1 and reporting2 amount fields, nor their corresponding reporting currency code fields The reporting amounts (RPT_AMT1 and RPT_AMT2 in the fact table example) are populated as a result of the ETL currency conversion process that you run after populating the MDW. Their values do not exist in the source system. The report amounts can represent amounts in any currency that you choose.
Assuming the source currency amount is <ABC>_AMT, where "<ABC>" represents the name of the field, this table lists the currency field naming convention for MDW fact tables:
Field Type |
Field Name |
---|---|
Source Amount |
<ABC__AMT |
Source Currency Code |
CURRENCY_CD |
Base Amount |
<ABC>_BCE_AMT |
Base Currency Code |
CURRENCY_BCE_CD |
Reporting1 Amount |
<ABC>_R1_AMT |
Reporting1 Currency Code |
CURRENCY_R1_CD |
Reporting2 Amount |
<ABC>_R2_AMT |
Reporting2 Currency Code |
CURRENCY_R2_CD |
In summary, MDW table structures use the following rules to support currency conversion in the MDW:
Each source amount that is stored in an MDW table must have a corresponding source currency code field in the MDW fact table.
If multiple source amounts from the same source tables are stored in the MDW tables, they may share the same source currency code field.
Base amount and base currency code fields can exist in the MDW table only if the source table has the corresponding base amount and base currency code fields.
That is, base amount and base currency code fields are source database fields. If multiple base amounts from the same source tables are stored in the MDW tables, they may share the same base currency code field.
Source amounts in MDW tables must have corresponding reporting1 amount and reporting2 amount fields, if that amount requires currency conversion.
MDW tables must have only one reporting1 currency code and reporting2 currency code fields that serve as the currency codes for all reporting1 amounts and reporting2 amounts in that MDW table.
Note: The target columns for the MDW Currency Conversion utility are the reporting1 and reporting2 columns. The columns are named as reporting amount or currency code because the converted amount and currency code are usually used for trend or analysis reporting in the MDW.
Image: Carrying multiple source amounts into the MDW fact table
The following examples describe the rules for MDW fact table structures.
Image: Carrying multiple source and base amounts into the MDW fact table
Carrying multiple source and base amounts into the MDW fact table.