Exchange Rates File

The exchange rates file captures conversion rates between any two currency codes that may appear in your fact data. The standard practice for fact data is to load the source system values in the original currency and allow the platform to convert the amounts to the primary currency. This file facilitates that process and triggers bulk updates in nightly processing any time you wish to change your exchange rates for financial reporting purposes. Adding new rates to the file with an effective date equal to the batch date triggers a mass update to all positional facts, converting all the amounts to the new exchange rate even if the item/location did not otherwise change in the source system.

Note that you do not have to provide exchange rates data for the following scenarios:

• You are loading your data already converted to primary currency

• You only use a single currency for your entire business

• You are only implementing a Science module and expect to perform those processes in the local currency amounts

Even when exchange rates are not required as a separate file, you should still populate the default currency codes (DOC_CURR_CODE, LOC_CURR_CODE, LOC_EXCHANGE_RATE) in the fact data files with default values.

Table 7-6 Exchange Rate File Required Fields

Column Header	Usage
START_DT	Contains the effective start date of the exchange rate. Set to the current business date to trigger new rate conversions.
END_DT	Contains the effective end date of the exchange rate. Default to 21000101 if the rate should be effective indefinitely.
EXCHANGE_RATE	Contains the exchange rate for the specified currency/type/effective date combination.
FROM_CURRENCY_CODE	Code of the currency to be exchanged.
TO_CURRENCY_CODE	Code of the currency to which a currency is exchanged.

Sample data for the EXCH_RATE.csv file columns above:

START_DT,END_DT,EXCHANGE_RATE,FROM_CURRENCY_CODE,TO_CURRENCY_CODE
20180514,21000101,0.8640055,CAD,USD
20180514,21000101,0.1233959,CNY,USD
20180514,21000101,1,USD,USD

The exchange rates data must also satisfy the following criteria if you are loading data for use in Retail Insights reporting:

1. Rates must be provided in both directions for every combination of currencies that can occur in your dataset (for example, USD > CAD and CAD > USD).

2. Rows must be included for each currency to itself with a fixed rate of 1 and end date of 2100-01-01 (as shown in the above example). These rows are used when RI converts data to document (source) currencies, to display the correct amounts on rows that have the same local and primary currency.

3. Dates must provide complete coverage of your entire timeframe in the dataset, both for historical and current data. The current effective records for all rates can use 2100-01-01 as the end date. Dates cannot overlap, only a single rate must be effective per day.

4. Rates should not change more often than absolutely necessary based on the business requirements. If you are implementing RI with positional data, a rate change triggers a complete recalculation of the stock on hand cost/retail amounts for the entire business across all pre-calculated aggregate tables. When RI is not used for financial reporting you might only change the rates once each fiscal year, to maintain a single constant currency for analytical purposes.