Understanding Currency Conversion Methodology

Image: Currency conversion methodology flow

The following diagram represents the currency conversion process.

The following sections provide additional technical details regarding the currency conversion process.

Identifying the Data Set from a Conversion Rule Business Unit

Although the business unit specified in the Currency Conversion Rule is a PF business unit, different MDW fact tables can occur at different business unit granularity levels. Therefore, the process of identifying the data set for the currency conversion process must be aware of the business unit granularity level of the fact table. There are three levels of business unit granularity: source business unit, composite business unit, and PF business unit.

Based on the granularity of the business unit, you use the following rules to determine the surrogate IDs used to constrain the fact data:

If the granularity level is the PF Business Unit, then SELECT PBU_SID FROM PS_D_BUSINESS_UNIT WHERE BUSINESS_UNIT = <Conversion Rule's BU>.
If the granularity level is the Composite Business Unit, then SELECT CBU_SID FROM PS_D_BUSINESS_UNIT WHERE BUSINESS_UNIT = <Conversion Rule's BU>.
If the granularity level is the Source Business Unit, then SELECT BU_SID FROM PS_D_BUSINESS_UNIT WHERE BUSINESS_UNIT = <Conversion Rule's BU>.

Identifying the Exchange Date from Date Columns

When the Currency Conversion Rule does not specify an exchange date to identify the currency conversion rate, then the Exchange Date column from the Schema Rule is used to determine the exchange date. Because an MDW fact table may have data in different date/period granularity, the Exchange Date column is used with the date/period dimension record name to determine the date.

Based on the value of date/period dimension record name, use the following rules to determine the date: If the date/period dimension record name is:

D_DAY

SELECT DAY_DT FROM PS_D_DAY WHERE DAY_SID = <Exchange Date Col Value>
D_MONTH

SELECT MAX(DAY_DT) FROM PS_D_DAY WHERE MONTH_SID = <Exchange Date Col Value>
D_WEEK

SELECT MAX(DAY_DT) FROM PS_D_DAY WHERE WEEK_SID = <Exchange Date Col Value>
D_QUARTER

SELECT MAX(DAY_DT) FROM PS_D_DAY WHERE QUARTER_SID = <Exchange Date Col Value>
D_YEAR

SELECT MAX(DAY_DT) FROM PS_D_DAY WHERE YEAR_SID = <Exchange Date Col Value>
D_PATTERN_DAY

SELECT DAY_DT FROM PS_D_CAL_DAY WHERE PDAY_SID = <Exchange Date Col Value>
D_DET_PERIOD

SELECT PPERIOD_END_DT FROM PS_D_DET_PERIOD WHERE PPERIOD_SID = <Exchange Date Col Value>
D_SUM_PERIOD

SELECT MAX(A.PPERIOD_END_DT) FROM PS_D_DET_PERIOD A, PS_R_DET_SUM_PRD B, PS_D_SUM_PERIOD C WHERE C.PPERIOD_SUM_SID = <Exchange Date Col Value> AND B.PPERIOD_SUM_SID = C.PPERIOD_SUM_SID AND B.PPERIOD_SID = A.PPERIOD_SID
D_DET_BUDGET

SELECT BPERIOD_END_DT FROM PS_D_DET_BUDGET WHERE BPERIOD_SID = <Exchange Date Col Value>
D_SUM_BUDGET SELECT MAX(A.BPERIOD_END_DT) FROM PS_D_DET_BUDGET A, PS_R_DET_SUM_BPRD B, PS_D_SUM_BUDGET C WHERE C.BPERIOD_SUM_SID = <Exchange Date Col Value> AND B.BPERIOD_SUM_SID = C.BPERIOD_SUM_SID AND B.BPERIOD_SID = A.BPERIOD_SID
D_PATTERN_YEAR

SELECT MAX(DAY_DT) FROM PS_D_PATTERN_DAY WHERE PYEAR_SID = <Exchange Date Col Value>
D_PATTERN_WEEK

SELECT MAX(DAY_DT) FROM PS_D_PATTERN_DAY WHERE PWEEK_SID = <Exchange Date Col Value>
Other tables: ETL checks to see whether the column represents a date or datetime field. If the field is not a date or datetime field, then the process terminates with error in the log.

You should never use D_DT_PATTERN or other common time dimension tables as the date/period dimension record name for an MDW fact table because they contain only period attributes, rather than a list of periods.

Currency Conversion Logic Using the Enterprise Rate Table

The Enterprise rate table that is used for currency conversion is the RT_RATE_TBL. When the triangulation method is used, CURR_QUOTE_TBL is also used to provide the triangulation rule.

Image: RT_RATE_TBL structure

RT_RATE_TBL structure.

Image: CURR_QUOTE_TBL structure

CURR_QUOTE_TBL structure.

Using the From Currency, To Currency, Rate Type, and Date from the Currency Conversion Rules:

Condition	Result
If exchange rate is found in RT_RATE_TBL	then Converted Amount = Source Amount * (RATE_MULT/RATE_DIV)
If exchange rate is not found in RT_RATE_TBL	then verify using the From and To Currencies and Exchange Date when the currency quotation method is defined in CURR_QUOTE_TBL
If Currency Quotation method is defined and RATE_TRIANGULATE = 'Y'	then get REF_CUR field from CURR_QUOTE_TBL Leg 1: Find in the RT_RATE_TBL the exchange rates (RATE_MULT and RATE_DIV) between the From Currency and REF_CUR for the given Rate Type and Date. Leg 2: Find in the RT_RATE_TBL the exchange rates (RATE_MULT and RATE_DIV) between REF_CUR and the To Currency for the given Rate Type and Date. Converted Amount = Source Amount * (RATE_MULT of leg 1 * RATE_MULT of leg 2) / (RATE_DIV of leg1 * RATE_DIV of leg 2).
If Currency Quotation method is not defined, or if RATE_TRIANGULATE = 'N'	then = error (because no conversion rate is found)

The Exchange Date is used to identify an effective-dated exchange rate. This means that if the exchange rate is not available, the most recent entry that matches all the other exchange rate selection criteria (that is, To and From Currencies and Rate Type) is used.

Error Logging

There are three situations in which error can occur in the currency conversion process:

RATE_MULT = 0, when RATE_MULT is used for currency conversion.
RATE_DIV = 0, when RATE_DIV is used for currency conversion.
No exchange rate between from and to currency codes is found for a given exchange rate type.

Errors for MDW currency conversion are written to an error table PS_E_CCU_ERROR.

The following information is made available in this table:

Currency conversion rule ID
Fact table name
From Amount column
To Amount column
From Currency column
To Currency column
Rate table name
Rate type
From currency
To currency
RATE_MULT value
RATE_DIV value