Printable View
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.
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
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?
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*
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.
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
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.
Understood.
We'll look forward to seeing how you did it at a later time.
Have a great day!
Bill