Versions Compared

Old Version 26

changes.mady.by.user BASTIDAS-ext, Pau

Saved on

compared with

New Version Current

changes.mady.by.user GURRAM-ext, Naveen

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

See also the Data Governance page onExchange Rate data governance, as well as the BW page on BW Currency Conversion.

Data Object Definition

Exchange rates are conversion rates set between two currencies, published in Reuters website, identified by a RIC code (*) and daily uploaded to Solvay systems. The rate is usually ECB (European Central Bank) rate. 

*RIC code: A Reuters Instrument Code, or RIC, is a ticker-like code used by Thomson Reuters to identify financial instruments and indices. The codes are used for looking up information on various Thomson Reuters financial information systems.


Key Concepts:

  1. "From currency" and "To currency" - The data is structured in a way where you can search for the "currency from" and the "currency to" in which you want to know the exchange rate. Therefore, each of the official currencies in the world (based on Reuters) will have its relationship (exchange rate) all the other currencies in the world.
  2. "Exchange rate" - Is the rate at which one currency will be exchanged for another currency.
  3. "Effective date" - It references to the date in which the exchange rate was captured. Normally the end of the month. Note that exchange rates are fluctuating 24x7 with no universal centralized market (the Forex market is considered to be an Over the Counter (OTC) Market) so the end of the month rate is taken as a reference for simplicity on currency conversion. This does not mean that the exchange rates are static within the given month in which was taken.
  4. "Exchange rate type" - Exchange rates can be calculated in different way depending on several parameters. A comprehensive view on what these parameters are and what exchange rate types is available here.


Data Model

Exchange rate data

is design to

in pricing will be used as

so-called "Look-up" data to be able to simply, find the relationship (exchange rate) between 2 different currencies.Exchange rate data in the Data Lake is composed of 1 table view:
  • V_DIM_exchange_rate

Project Space: prj-data-pricing-dev
See how this entity relates to the others in the pricing data model here.

a reference data to translate the incoming transaction record currency into Euros and USD

Given the need to present only CAR3 and CAR4 exchange type data to be used by the transparency dashboard and pricing optimization tool,

there is also an

below view will act as enriched version of the FX Rates

data named as per below

data comparing to the data extracted from source: 

  • V_DIM_exchange_rate_
enrichedThe enriched version has the following characteristics and differences from the non-
  • enriched
version:

Project Space: prj-data-pricing-dev
See how this entity relates to the others in the pricing data model here.

  • Have consistency across the different tables where source system is present in the way sources are named.

  • Have only 1 unique FX rate per pair and type, regardless on whether the underlying data has multiple (equivalent) FX rates from multiple sources. Taking WP1 as the proffered system.

    • [In the final view] Not have a “source system id” column in the final view exposed to users (at pricing data lake level), however, it should be kept for traceability proposes at data ocean or ODS level.
      • Filter only FX rates types = CAR3 and CAR4.
      • Filter Fx rates on effective_date from 1st of Jan 2018.
    These should be kept.

  • Have an additional “to_currency” conversion into USD (in addition to EUR)

    • Have
      • For each currency , the table will hold the Euro and USD conversion details.
      • Has a new “forecasted FX rates” calculated values on FX rate type CAR 3 only
    (therefore we need to keep the forecasted data coming from CAR4 BUT replace the CAR 3 forecasted data)
      • added as additional records in the pricing data lake based on the following criteria:
        • Calculated for effective date for the next rolling 18 months on a daily basis.
        • Taking on a daily basis the last available closed month (considering a closed the previous month from the day 1st of the current month) FX rate (closed month daily available FX Rate) to be taken as the value to be forecasted (for the next 18 months), generating one record per month. Note that since we are only using CAR3 and CAR4 these are monthly rates.
        • There is no need to keep forecasted FX Rates once is overdue, it needs to be replaced by the actual (real) FX rate. We don’t need to keep different versions of FX rates.
        • Add one additional row on daily basis (forecast and historical) with the EUR to EUR conversion set to 1 for the CAR3 and CAR4 data to be exposed to the pricing data lake.

    Project Space: gcp-sqo-data-pricing-d
    See how this entity relates to the others in the pricing data model here.


    Additional Info & Comments

    • Rates are normally updated on either monthly or daily basis, depending on the update frequency in BW. A comprehensive list of "Exchange rate types" and their corresponding update frequencies is available here.  
    • When applying currency conversions using Exchange Rate data, users need to be careful to use the correct type of exchange rate. There can be significant differences between daily rates, end-of month rates, budget rates, etc. Applying exchange rates inconsistently (e.g. using a budget rate in BW but then converting back using a daily rate in Qlik) will result in variances and inconsistencies, which can be significant.
    • More information on the different types of Exchange rate data can be found in the Exchange Rates and the Currency Conversion pages of the BW reporting wiki.


    Data Flow

    Image Removed





    Image RemovedImage Added



    Google Drive Live Link
    urlhttps://docs.google.com/presentation/d/1djAI4zd9RJj7UhYwZx9VLEX_OMM2SNXq/edit#slide=id.p1


    Data at a Glance


    HTML
    <iframe width="1200" height="600" src="https://lookerstudio.google.com/embed/reporting/656626cb-9e98-4b20-aec6-6f15b55e0966/page/tEnnC" frameborder="0" style="border:0" allowfullscreen></iframe>



    Tables & Attributes

    ratio_for_to_currencyFLOATexchange_rate_typeSTRINGexchange_rate_type_usageSTRINGsystem_idSTRINGinserted_dateTIMESTAMPupdated_dateTIMESTAMPdelete_flagBOOLEAN

    View ID:prjgsp-sqo-data-pricing-devd.DataOcean.V_DIM_exchange_rate

    fullnametypedescriptionexchange_rate_keySTRINGsource_system_idSTRINGeffective_dateDATEfrom_currencySTRINGto_currencySTRINGexchange_rateFLOATratio_for_from_currencyFLOAT

    _enriched