Implementing Currency Conversion in the Multidimensional Warehouse

To set up and run the currency conversion process for the Multidimensional Warehouse, use the Currency Conversion Schema Definition (CCU_SCHEMA_DEFN) and Currency Conversion Definition (CCU_CONV_DEFN) components, as well as the ETL currency conversion process.

This section provides overviews of currency conversion for the multidimensional warehouse, the methodology behind the currency conversion process, and currency conversion rules, and discusses how to:

• Set up currency conversion.

• Set up the schema definition.

• Set up currency conversion rules.

• Set up the conversion schema rule.

• Run the ETL currency conversion process.

Understanding Currency Conversion in the Multidimensional Warehouse

Companies spanning national boundaries often experience problems handling multiple currencies, as well as problems providing a unified view of their enterprise 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 for the Performance Management Warehouse, 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 enterprise data. For these reasons, the MDW currency conversion utility has been created.

Note. The MDW currency conversion process discussed in this chapter 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 analytical applications.

Enterprise One currency codes are user-defined and may not match EPM currency codes, which are system-defined.

Comparing MDW and OWE Currency Conversion Processes

Separate currency conversion processes are required in EPM, one for the OWE and one for the MDW. It is important to understand the difference between the processes, as they are quite different. The following table describes the differences between the OWE and MDW currency conversion processes:

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:

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:

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.

The following examples describe the rules for MDW fact table structures:

Carrying multiple source amounts into the MDW fact table

Carrying multiple source and base amounts into the MDW fact table

Understanding Currency Conversion Methodology

The following diagram represents the currency conversion process in the MDW:

Currency conversion methodology flow

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.

The RT_RATE_TBL has the following structure:

RT_RATE_TBL structure

The CURR_QUOTE_TBL has the following structure:

CURR_QUOTE_TBL structure

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

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.

Currency Conversion Logic Using the Enterprise One Rate Table (Enterprise One Source Only)

The JD Edwards EnterpriseOne rate table, F0015, is stored in EPM as RT_VAR_RATE_TBL. This rate table is used only when the Currency Conversion Rule indicates that the conversion is using the Address Book-specific rates. The rate type from RT_TYPE field in the RT_VAR_RATE_TBL is not currently used by the Enterprise One source database. Therefore, when currency conversion is done against the Enterprise One rate table, the rate type parameter from the Currency Conversion Rule must be ignored. Instead, use ‘-‘ (hyphen) for RT_TYPE.

The RT_VAR_RATE_TBL has the following structure:

RT_VAR_RATE_TBL structure

Using The From Currency, To Currency, Address Book, Rate Type, and Date parameters from the Currency Conversion Rules:

The date from Currency Conversion Rule identifies the effective-dated exchange rate. That is, if the exchange rate for the date is not available, then the most recent entry that matches all the other exchange rate selection criteria (To and From Currency, Rate Type, and Address Book) is used.

Enterprise One source database version 8.9 or earlier uses two tables, F0015 and F00151, to represent exchange rates. If the Enterprise One source for EPM is version 8.9 or earlier, you must map the data in these two tables to RT_VAR_RATE_TBL.

Note. The CALC_METHOD and CUR_CONV_METHOD codes are not user defined.

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

Understanding MDW Currency Conversion Rules

You must specify the following parameters for the MDW ETL currency conversion process:

• Schema Rules: Specify the structure of MDW tables upon which currency conversion process are performed.

• Conversion Rules: Specify how the currency conversion should be performed by indicating the rate type, target currency code, and the effective rate to use for the currency conversion process.

• Chunking Rules: Identify the subset of data in MDW tables that are affected by the conversion process.

Together these three rules are referred to as currency conversion rules.

Schema Rules

Schema rules specify on what table the currency conversion is performed, the source amount and currency code column, and target amount and currency code column that is populated by the conversion result. Schema rules also include the table name where the source and target columns are found. Essentially, the schema rules abstract the interrelationship between source and target columns for currency conversion. The schema rules also specify the granularity of data for the conversion process. This is done by specifying the relevant date/period dimension and business unit column for both resolving the exchange date and chunking the data set for currency conversion.

You set up the schema rules based on the schema definition,which is system data and is delivered as part of the PeopleSoft EPM product. The predefined schema definition associates source amounts and currency code fields to their target amount and currency code fields. Schema rules contain the list of all target columns for currency conversion, along with their associated information, such as the record name, Source Amount column, the Source Currency Code column, and the To Currency Code column for a particular fact table.

It is important to understand the difference between the schema definition and the schema rules. The schema definition only records the relationship of columns in delivered tables. A schema rule is customer-specific because it depends on what rate and date to use for a currency conversion. For example, the schema definition only indicates the currency code column for a particular amount column in a stated table.

Schema rules consist of:

• Table name.

• Business unit column.

  An MDW fact table may have multiple business unit surrogate ID (SID) columns, but only one is used to drive the currency conversion. Because not all MDW tables have a business unit SID, this column is optional.

• Business unit grain level.

  Although all of the data in the same MDW fact table has the same granularity level, different MDW facts can be at different business unit granularity levels, whether it is a source business unit, composite business unit, or performance business unit (PF BU.) The business unit grain level is required only if the Business Unit column exists.

• Exchange date/period column for the table.

  Only one date/period column per table is used to drive the exchange rate. Some MDW tables do not have a date/period SID column; therefore, the column is optional. When no date/period SID column is specified, the exchange date resolution always uses the current date/prespecified date in the conversion rule.

• The date/period dimension record for the exchange date/period column.

  This is required only if the exchange date/period column is specified.

• JD Edwards EnterpriseOne address book column.

  This parameter is optional. If it is specified, then the currency conversion will attempt to identify an exchange rate from the AddresBook-specific exchange rate table. Otherwise, the exchange rate is derived from the regular exchange rate table, PS_RT_RATE_TBL.

• From amount column name.

• To amount column name.

• From currency code column name.

  The utility assumes that the from currency code column is always populated with the source currency code from the source table.

• To currency code column name.

  The to currency code is populated by the To Currency Code parameter when the currency conversion has completed.

The following example shows the schema definitions for the F_AP_TRAN fact table:

Schema Definition page for F_AP_TRAN fact table

Schema Source Columns page for F_AP_TRAN fact table

Schema Target Columns page for F_AP_TRAN fact table

Table F_AP_TRAN is an MDW fact table that has data granularity at day level. It also tracks its data with AP Business Unit, one type of business unit originating from the source database. Therefore, in the schema definition page for F_AP_TRAN table, we identify the Business Unit column (AP_BU_SID), as well as the business unit granularity (that is, the source). In addition, we also specify the Date column in the table (ACCT_DAY_SID), as well as the date/period granularity, by specifying the date/period dimension table, in this case, D_DAY.

Table F_AP_TRAN has several data columns that require currency conversion. Among others, they are ADJ_BASE_AMT and DILS_BASE_AMT. Therefore, we specify these columns on the Schema Source Columns page for F_AP_TRAN. You must associate the current currency code for each of the columns upon which currency conversion can be performed.

You must associate each reporting amount and currency code column to the source amount and currency column. You do this on the Target Schema Columns page for F_AP_TRAN.

Note. Schema rules are prepackaged with EPM, but if you add a new MDW dimension or fact table upon which a currency conversion process must be performed, you must add schema rules for the new tables.

See Setting Up Parameters for Tree and Recursive Hierarchy Processing.

Conversion Rules

Conversion rules specify how the currency conversion should be carried out, such as which rate and exchange date to use, or whether to perform the conversion for a subset of data (constrained by business unit, date range, or both) or for the entire table. Conversion rules specify the rate type, the target currency code, and the data that determines the effective rate to use for the currency conversion process. The rules also specify which specific amount columns in the table to convert.

Conversion rules are user-defined data and are not delivered as part of the EPM product. Because exchange rate rules are specific to user requirements, and therefore they are treated as user data, PeopleSoft provides only sample data with your EPM product.

Exchange rate rules consist of:

• Rate Type.

• To currency code.

• Conversion date.

  This rule is optional. The conversion date can be a specified date or current date. Otherwise, the exchange rate is determined by the date of the transaction.

  You must choose either:

  • To give the specific exchange date that the currency conversion process will use to identify the exchange rate.

  • To have the currency conversion process use the processing date.

  • To have the currency conversion process use the exchange date column as specified in the schema rule.

The following provides an example of how to create a currency conversion rule:

MDW Currency Conversion Rule page for AP_TRANS fact table

MDW Conversion Schema Rule page for AP_TRANS fact table

The AP_TRANS conversion rule tells the MDW currency conversion process what criteria to use to perform the currency conversion on the amount columns listed in the MDW Conversion Schema Rule.

From Example 1 in Section 3.1, we find that table F_AP_TRAN has a date/period column that is used to determine the exchange date. If you want to have the exchange date follow the transaction date, then you specify conversion date as “date column.” This will force the currency conversion utility to take the date from the F_AP_TRAN table, as specified in the Schema Rules. In this example, Recent Date (Recent Dt.) is used for the conversion date. Therefore, you are overriding the exchange date used for currency conversion with the recent date (that is, processing date). You can also override the exchange date with a pre-specified date. This feature is useful when your company has a policy of using a particular date as a standard exchange date for a subset of data.

In this example, chunking rules are not specified. Therefore, you are telling the MDW Currency Conversion utility to perform currency conversion for all of the rows in F_AP_TRAN table.

Chunking Rules

Chunking rules are parameters that the currency conversion process uses to identify the subset of data in MDW tables that are affected by the conversion process. Chunking rules are considered part of the currency conversion rules. They are specific to user requirements and are treated as user data. Therefore, PeopleSoft provides only sample data with your EPM product.

Chunking rules consist of performance (PF) business unit, start date, and end date. These parameters are optional. If you do not provide business unit and chunking date parameters, the process performs conversion on the entire table.

Chunking rules identify the subset of data in MDW tables that experience currency conversion. These rules consist of:

• Business Unit: This rule is optional. If it is provided, only MDW fact data that belongs to this business unit will experience currency conversion. Otherwise, data for all business units experience currency conversion. The business unit in the currency conversion rule is always a PF business unit. If the MDW table has different business unit granularity levels (source or composite), refer to section 4.2 on how this PF business unit is translated into the equivalent source or composite business unit.

• Start Date: This rule is optional. If it is provided, any date greater than or equal to this parameter experiences currency conversion.

• End Date: This rule is optional. If it is provided, any date less than or equal to this parameter value experiences currency conversion.

Setting Up MDW Currency Conversion

You run the currency conversion process from a prepackaged ETL job, which uses the Currency Conversion Rule ID as an input and is a composite of the conversion rules whose parameters you have set up before you run the currency conversion process. The Currency Conversion Rule ID provides the necessary information to perform the currency conversion, such as the rule to obtain the appropriate exchange rate, the rule to obtain the subset of data on which the currency conversion is performed, and the source and target columns for currency conversion.

To set up currency conversion, use the Schema Definition (CCU_SCHEMA_DEFN) component and the Currency Conversion (CCU_CONV_DEFN) component.

Before you run the ETL currency conversion process, you must define the required parameters. This section discusses how to:

1. Set up the schema definition and columns.

2. Define schema source columns.

3. Define schema target columns.

4. Define rules for currency conversion.

Pages Used to Set Up the Schema Definition and Currency Conversion Rules

Setting Up the Schema Definition

Access the Schema Definition page.

Business Unit Column	Enter the name of the PF (performance) business unit column.
MDW Fact Exchange Dt Column (MDW fact exchange date column)	Enter the date column used to determine the exchange date or conversion date.
Date/Period Dimension Record	Enter the level (year, month, day, and so on) for the Fact Exchange Date column.
Address Book Column	Enter the column that indicates the Address Book value. This value is required only if the fact table has an Address Book entry.

Setting Up MDW Currency Conversion Rules

Access the MDW Currency Conversion Rule page.

Currency Conversion Rule

The name of the currency conversion rule.

Conversion Rule Definition

Chunking Rule Definition

Setting up the MDW Conversion Schema Rule

Access the MDW Conversion Schema Rule page.

Record (Table) Name	Select the record on which the conversion will be performed. The record name must exist in the schema definition.
To Amount Column	Select the column for the conversion result. The options are obtained from the schema definition.
From Amount Column	The column to be converted. This field is automatically populated from the schema definition when you select the To Amount Column.

Running the ETL Currency Conversion Process for the MDW

The process that actually converts monetary amounts in the Multidimensional Warehouse is an ETL utility. MDW currency conversion is a post process that needs to be applied after initial data load of all Fact tables. Therefore, before running this utility, make sure to run all the fact jobs in your project.

You can perform the ETL currency conversion process using both a direct exchange rate and a triangulated exchange rate. (A triangulated exchange rate conversion takes place when no direct exchange rate between a from currency and a to currency exists, but the exchange rates exist between the from currency and a reference currency, and from the reference currency the to currency. Using the triangulation method, the currency conversion process indirectly establishes the exchange rates between a from currency and a to currency using the intermediary reference currency. You set up triangulation parameters with other EPM setup functions.

If you provide an optional exchange rate date parameter, the currency conversion process searches for an exchange rate for a given exchange rate date. If you do not provide an exchange rate date parameter, the conversion utility uses the date of the transaction to determine the exchange rate.

Note. The MDW currency conversion process does not perform balancing; that is, when there are parent and child tables and rounding occurs, the process does not ensure that the sum of the child tables equals the value of the parent table.

If you map multiple source business units into one warehouse business unit (WBU), the default currency of the source business units must be the same as the default currency of the warehouse business unit.

Note. The Currency Conversion Rule ID exists on the MDW Conversion Schema Rule and the MDW Conversion Schema Rule pages. The Currency Conversion Rule ID provides the MDW Currency Conversion process with the necessary information to perform the currency conversion that you have previously identified, such as the rule to obtain the appropriate exchange rate, the rule to obtain the subset of dates within which the currency conversion will take place, and the source and target columns for the currency conversion.

Steps Required to Run the MDW ETL Currency Conversion Utility

Perform the following steps to run the MDW ETL currency conversion process:

1. In DataStage Director, navigate to the MDW currency conversion job by expanding the nodes in the left navigation panel using the following path: EPM90_Utilities, Sequence, SEQ_J_Run_CurrencyConversion.

2. Select the sequence job SEQ_J_Run_CurrencyConversion in the Job Status view and select Job, Run Now... from the menu.

   The Job Run Options box appears.

3. Update the job parameters by entering the required currency conversion rule for a specified fact table, and click Run.

   The job is scheduled to run with the current date and time, and the job’s status is updated to Running.

   Currency conversion occurs for all the facts grouped under this rule.

4. Repeat steps one through three for each currency conversion rule that you want to run.

See Also

Preparing to Load Data Into EPM