Currency Conversion RRS feed

  • Question

  • I have a situation where I have three tables in the source i.e.


    1.       Currency

    2.       Currency ExchangeRate

    3.       ExchangeRateType


    I understand the conversion of 1:M, M:1 and M:M currency conversion in the Analysis Services by modeling Currency as a Dim and Exchange Rate as a Fact Table with Time Key for semi-additive aggregation. Same as the way Exchange Rate works in the Adventure Works.


    Now the problem we have, there is no one Exchange Rate, we have different Exchange Rate Types. So different Exchange Rate will be applied to different Currency conversion for Calculations.


    How to materlized these 3 sources table into a dimensional model in a right way, so it answers all the scanerio.


    Thank You


    Friday, May 30, 2008 12:57 AM

All replies

  • Hi Seattle1,


    add that extra dimension in cube like Adventure Works Exchange Rate. In total, you should have these dimensions: Currencies, Types, Dates. Fact is Exchange Rate. If for some reason you have multiple rates inside each row in fact, break it into separate rows by joining each of those columns with types. Actually, join is for multiplication purposes here, not like real table join. Columns should be: ID, time, currency, type, rate (value).


    If you're using this cube for lookup, then add appropriate type dimension member in your lookup also.


    Feedback would be appreciated.



    Tomislav Piasevoli

    Business Intelligence specialist






    Monday, June 16, 2008 9:58 PM