Results 1 to 8 of 8

Thread: Currency Exchange as a dimesnion

Hybrid View

  1. #1
    Join Date
    Dec 2004
    Location
    Ottawa, Ontario, Canada
    Posts
    4

    Currency Exchange as a dimesnion

    I have a fact table with amounts, all in a single currncy. I would like to be able to process the cube where I can select the currency from a dimension.

    The format of the currency exchange dimension must include date, currency code, and exchange rate, where the native exchange rate (the currency that the amounts are in) is defined as 1.000.

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    Are you asking how to do it, whether its a good idea, or something else?

    When you way "formatting" with the various attrbutes, do you mean attaching them as member properties, or what?

    Sorry, but I don't quite understand the request.

    Happy New Year!

    Bill

  3. #3
    Join Date
    Dec 2004
    Location
    Ottawa, Ontario, Canada
    Posts
    4
    The question is realted to how I can define a calculated measure that takes the amount, expressed in Canadian dollars, and multiplies it by an exchange rate. The exchange rate for the Canadian dollar is 1.0. I have the exchange rate defined as a member property of the dimension. If the user selects a country code form a currency conversion table, each country code has a time (date) and an exchnage rate associated with it, with the exchnage rate being the property.

    I can see where I can use the date as the common column between the fact table and the currency conversion dimension, but what would be the calculated measure expression which applies the exchange rate in the calculated measure?

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    In its simplest - VERY simplest - form, a calculated measure based upon the base currency measure something like:

    [Measures].[SWISSFR]=[Measures].[EURO]*1.4
    -- Conversion Factor Fixed

    The complication lies in the fact that the conversion factors (the exchange rate) will be changing, not fixed (the reason I questioned putting it in a member property).

    One possibility is to store the exchange rates in an extra cube and then get the appropriate rate for the calc measure by using a lookup on this cube (pulling it in, for example, in the place of the 1.4 above).

    Another approach might be to create a second cube to store conversion rates (perhaps multiple, as for Profit and Loss (P & L) versus Balance Sheet items, a common requirement for financial conversions) over time. Combine the cubes (data to be subjected to conversion plus the new cube) in a virtual cube.

    Shake - Do Not Stir ...

    HTH,

    B*

  5. #5
    Join Date
    Dec 2004
    Location
    Ottawa, Ontario, Canada
    Posts
    4

    Partial exchange rate solution

    I came up with a way in which the exchange rate could be defined as a dimension. It's a combination of how the dimension is defined and the multi dimensional expression. I have CAD as the default member of the exchange rate dimension and the user can select any other exchange rate and have all amounts on the display change, using that exchange rate. I first tested it with a single date. I ran into some lengthy build times when I used all exchange rates and six months. Luckily I only needed three exchange rates over six months and the build time became reasonable again. I have my solution using an exchange rate dimension, so thank you for all your help.

  6. #6
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    Why don't you share the MDX, so others can benefit from the question you asked, and for which you apparently got a solution?

    That's what we're here for ...

    Have a great day ...

    Bill

  7. #7
    Join Date
    Dec 2004
    Location
    Ottawa, Ontario, Canada
    Posts
    4

    Testing and more testing

    I have not finished testing. I did not wish to post anything that was not tested in all of my test cases. There may be more to the solution.

  8. #8
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    Understood.

    We'll look forward to seeing how you did it at a later time.

    Have a great day!

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •