none
Currency Conversion - Many-To-Many RRS feed

  • Question

  •  

    I simply can't figure out how the many-to-many conversion works. I'll start by explaining what I would like to do:

     

    I have a general ledger fact table containing transactions for several individual countries, which is why they are in different currencies as well. I'll illustrate the table with the relevant fields:

     

    Fact General Ledger

    • dim_accounting_period_id
    • dim_company_id
    • dim_currency_id
    • amount_lcy

    I would like to make a cube where the user freely can select their preferred reporting currency, nomatter the source currency. Therefore I have made a fact table contaning exchange rates for all combinations of source- and destination currency for all applicaple timeperiods, like so:

     

    Fact Exchange Rate

    • dim_accounting_period_id
    • dim_source_currency_id
    • dim_destination_currency_id
    • exchange_rate_period_end
    • exchange_rate_period_avg

    In addition to this I have a currency dimension like this:

     

    Dim Currency

    • dim_currency_id
    • currency_iso_code
    • curency_description

     

    When the user selects a reporting currency from the dim currency table, the cube should treat that as the reporting currency used, and therefore link all transactions from the fact table to the rates in the exchange rate fact table that has that particular destination currency, same as reporting currency.

     

    Could someon please be so kind to explain to me if this is possible, and if so, then how? I am not sure that the BI wizard with many-to-many conversion does exactly what I would like it to do.

     

    Another question is with regard to my two types of exchange rates, average and period end. Since this is a financial cube, you would normally use the average rate for your profit & loss and the period end rate for the balance sheet. I get the end rate, since I can just set the aggregation to "lastnonempty" and it works like it is supposed to, however for the average rates I am running in to problems with the aggregation method. I want it to do the following, there the reporting currency is set to i.e. DKK:

     

    Profit and Loss transactions (Where the analysis has been limited to only period 1 & 2):

     

    Transaction 1     Period 1     GBP 100,00  @ should convert to the avg rate for period 1

    Transaction 2     Period 2     GBP 20,00 @ should convert to the avg rate for period 2

    Transaction 3     Period 1     GBP 80,00 @ should convert to the avg rate for period 1

     

    Balance Sheet transactions (Where the analysis has been limited to only period 1 & 2):

     

    Transaction 1     Period 1     GBP 100,00  @ should convert to the end rate for period 2

    Transaction 2     Period 2     GBP 20,00 @ should convert to the end rate for period 2

    Transaction 3     Period 1     GBP 80,00 @ should convert to the end rate for period 2

     

    How is this possible - Also in relation to my many-to-many setup?

     

    Thanks in advance to anyone that could help me with these questions!

    Wednesday, March 26, 2008 10:22 AM

All replies