Exchange rate cube data: EXCRATES

Data for the EXCRATES exchange rates cube is loaded into a staging table and stored procedures are used to validate the staged data and transform it so that it can be imported by Designer. Each data value is identified by references to the target element of each of the 10 dimensions which make up the EXCRATES cube.

The process uses these tables and stored procedures:

  • The staging table is DAT_EXCRATES_STAGE.
  • The stored procedure to validate staged data is depm_integration_bp_excrates_prepare_staging.
  • The stored procedure to process staged data is depm_integration_bp_excrates.
  • The output table is ATT_EXCRATES (cube data table).

This table describes DAT_EXCRATES_STAGE:

Field Required Description
PERIOD Y Period used with the time dimension; period and year are merged and displayed as PP_YYYY. For example, 01_2015.
YEAR Y Year used with the time dimension; period and year are merged and displayed as PP_YYYY. For example, 01_2015.
EXDTYPE Y One of two elements from the exchange type dimension:
  • MOVEMENTS (standard for all periods)
  • OPENING_BALANCE (only for first period and an opening balance account)
BPDENTITY Y Base level element from the Entity dimension required for EXDRATE H or HI; otherwise N.A.
BPDINTERCOMPANY Y Base level element from the intercompany dimension - distinct from the BPDENTITY value - required for EXDRATE HI; otherwise N.A.
BPDACCOUNT Y Base level element from the account dimension required for EXDRATE H or HI; otherwise N.A.
EXDCCURRENCY Y Base level element from the company currency dimension, an ISO code of the source currency for the exchange rate.
EXDGCURRENCY Y Base level element from the group currency dimension, an ISO code of the target currency for the exchange rate.
EXDRATE Y A one- or two-character code for the exchange rate type from the list:
  • F = Closing Rate (Period End Rate)
  • H = Historical Rate
  • HI = Historical Rate Intercompany
  • PA = Periodic Average Rate
  • N = No translation
ExchangeRate Y Numeric data amount stored as a string; currency exchange rate (multiplier) to convert company currency to group currency.

For historical rates (type H, HI), the rate is the movement rate for the period for the entity and for the account (and optionally for the intercompany).