Skip to Main Content
Return to Navigation

Understanding Currency Conversion

Companies spanning national boundaries often experience problems handling multiple currencies, as well as problems providing a unified view of their 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 documentation 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:

Carrying multiple source amounts into the MDW fact table

Image: Carrying multiple source and base amounts into the MDW fact table

Carrying multiple source and base amounts into the MDW fact table

 Carrying multiple source and base amounts into the MDW fact table