none
Currency Conversion RRS feed

  • Question

  • Hi Team -

    I am in a predicament and need help. I am tyring to implement currency conversion. The transactions will be in multiple, local, currencies and we need a Many-To-Many implementation so that end users can convert bewteen one local currency and another for example between GBP and AUD. My problem is that in my case there is no one exchange rate but 5 different types of exchange rate. So, apart from the regular dimensions - time & currency, I am planning to introduce one more dimension  - ExchangeRateType. The fact table will be the standard one which will contain all the exchange rates. So, once all this is in place, I am planning to run the Currency Conversion wizard. So, will this serve my purpose or I need to take a different approach?

    Please let me know.

    Thanks in advance.

    Best regards,
      Girish
    Tuesday, June 16, 2009 8:13 PM

Answers

  • Understood. Definitely, create a new dimension for the Exchange Rate Type then and set its IsAggregatable property to false as the All member would be senseless. Now that I think about it a bit more, I'm not even sure you would need to modify the generated MDX script as the Exchange Rate Type dimension would only be related to the Exchange Rate measure group and would only affect the chosen conversion rate. Try it without modifying the generated script and see if it works.

    Just a quick question though. How are you determining your monthly, yearly average, etc. in your source? If you defined the AggregationFunction property of your exchange rate to average, (or created one with LastChild and another with Average) then it would be calculated in the cube. You could also play around with adding a shell attribute to your time dimension to switch between the different exchange rate types rather than creating an Exchange Rate Type dimension if the only difference between the types is how these rates are determined with respect to time. Just a thought.  The infamous Shell dimension paper can be found here if you haven't already seen it. http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

    Wednesday, June 17, 2009 5:14 PM

All replies

  • Have you seen this blog post? http://blogs.conchango.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx I found it very helpful when I had a similar issue.
    Wednesday, June 17, 2009 2:36 AM
  • Yes, I have been through that post already. But, as I had mentioned before - my case is a bit different as I have 5 different exchange rate types and not one.
    So, I am looking for some guidance here.

    Regards,
      Girish
    Wednesday, June 17, 2009 6:57 AM
  • I'm not sure what you mean by different exchange rate types. Do you mean you have different exchange rates for the conversion of GBP to AUD on any given date? That would lead to chaos. Or do you mean the conversion rates are used in different ways dependent on which measure group, ie. 30 day rolling average, Last recorded, etc. You could just run the currency conversion wizard multiple times for eah different use case and select which measures/measure group are applicable in each case. Anyway, currency conversion is fairly standard across the board and Christian pretty much describes the details very accurately.

    Wednesday, June 17, 2009 1:35 PM
  • Thanks for the reply Mason. What I mean is - We have different exchange rate types - Daily, Monthly, YTD (YearToDate) etc. So, I have created one more dimension that tracks these exchange rate types. So, if I run the wizard will it work for me ? I ask because I have an extra dimension.

    Thanks.

    Best regards,
       Girish 
    Wednesday, June 17, 2009 2:18 PM
  • I'm not sure if the best way is to create a new Exchange Rate Type dimension with five members or to create five different measures in the Exchange Rate fact measure group. Do you have situations where the same measure in some cases needs to be converted by a Daily Rate and in others a Monthly average? If so, I would create the new Exchange Rate Dimension. If not, I would go the separate measures route. I think if you go the first way, you're going to have to manually modify the MDX Script that the Currency Conversion wizard generates. I think Christian's blog should give you good head start on what needs to be changed.

    HTH,
    Martin
    Wednesday, June 17, 2009 2:49 PM
  • Hi Martin,

    The reason I planned to create a dimension for the exchange rate types is that we have a requirement where in the user can select an exchange rate type. It should also be possible to apply different exchange rate types on a same measure...which is not possible with the wizard I guess because in the wizard you can specify only one exchange rate type for a measure. If I create a measure for each exchange rate type and we also consider the afore-mentioned scenario where I have to apply each rate type to each measure then I run into trouble as I have 120 measures that require currency conversion!

    Let me check Christian's blog again to find out what changes are required in the MDX script that the wizard will generate.

    Regards,
      Girish
    Wednesday, June 17, 2009 3:04 PM
  • Understood. Definitely, create a new dimension for the Exchange Rate Type then and set its IsAggregatable property to false as the All member would be senseless. Now that I think about it a bit more, I'm not even sure you would need to modify the generated MDX script as the Exchange Rate Type dimension would only be related to the Exchange Rate measure group and would only affect the chosen conversion rate. Try it without modifying the generated script and see if it works.

    Just a quick question though. How are you determining your monthly, yearly average, etc. in your source? If you defined the AggregationFunction property of your exchange rate to average, (or created one with LastChild and another with Average) then it would be calculated in the cube. You could also play around with adding a shell attribute to your time dimension to switch between the different exchange rate types rather than creating an Exchange Rate Type dimension if the only difference between the types is how these rates are determined with respect to time. Just a thought.  The infamous Shell dimension paper can be found here if you haven't already seen it. http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

    Wednesday, June 17, 2009 5:14 PM
  • Hi All,

    I am creating a currency conversion prototype as suggested by Christian in his blog.

    http://blogs.conchango.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx

    I have linked the 'Reporting Currency' dimension with the ExchangeRate fact table in Dimension Usage and set the relationship type to Regular and I have also linked the 'Reporting Currency' dimension with a fact table 'FactResellerSales'; this one is a Many-To-Many relationship through the ExchangeRate fact table.
    My requirement is Many-To-Many. I applied the conversion on a measure named Total Product Cost in FactResellerSales.

    However, when I browse the cube I am getting in-correct results. What is happening is, when I select a target reporting currency the system returns a sum of measure TotalProductCost for the selected currency. This is not what I want. However, the system is able to fetch the exchange rates properly from the ExchangeRate fact table.

    Please let me know as to what I am missing here.

    Thanks.

    Best regards,
       Girish
    Sunday, July 26, 2009 5:18 PM