# Thread: Currency Exchange as a dimesnion

1. Registered User
Join Date
Dec 2004
Location
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. DBJ Author
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. Registered User
Join Date
Dec 2004
Location
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. DBJ Author
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. Registered User
Join Date
Dec 2004
Location
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. DBJ Author
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. Registered User
Join Date
Dec 2004
Location
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. DBJ Author
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
•