Currency Exchange as a dimesnion 1

[atkas]

I have a fact table with amounts that are all in Canadian Dollars. I have created calculated measures for each of these amounts and would like to know how I can display the cube in any of the defined exchange rates in the dimension. The exchange rate dimension consist of time, currency code, and exchange rate where the Canadian Exchange rate is defined as 1.000.

 

[johnherman]

I would create a second cube for the exchange rates and create a virtual cube joining the exchange rate cube with the other fact table cube using shared dimensions of time and country of sale.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[atkas]

All of the amounts are in Canadian dollars. The exchange rate is intended to allow the user to view the results in the currency of choice.

I had not not considered the use of a virtual cube, but since time seems to tbe the only common dimension, I was only able to get the exchange rate to apply to the highest level aggregate and to none of the amounts if I sliced using a non-common dimension.

 

[johnherman]

You need to create a dimension for country code so that the user can select the country of their choice. Each country code will be a "Slice" of the cube. Also create a calculated column using "IF" logic to multiply the amount field by the exchange rate depending on the country code.

(if I am understanding your problem/situation correctly)

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[atkas]

I tried using a dimension but seem to be missing something. I can't seem to pick up the exchange rate I need in my calculated measure.

The fact table basically has:
date
amount (in Canadian dolloars)

The dimension has:
date
cc_code
exch_rate

The currency code for Canada is also in the dimension, with a rate set at 1.000. The Value Expression I enter checks ok but brings a run time error.

The idea is that user selects the currency code and the exchange rate is applied to the amount (in a calculated measure). The defualt memeber for the currency code dimension is set to Canadian dollars.

 

[johnherman]

Hmmm. I'm getting ready to go on vacation so my mind is wandering quite a bit. Let me think about it and I'll get back to you next week.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[johnherman]

Create a new fact table (view?) like this:

Select Ca_table.date, Ca_Amt, CC_Code, exch_rate*Ca_Amt CC_Amt, exch_rate from Ca_table, Exch_table
where Ca_table.date = Exch_table.date

Then turn Analysis Services loose on the fact table. It will find date and CC_code dimensions with Ca_Amt, Cc_Amt, and exch_rate as measures.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[johnherman]

While I'm vacationing, check out these links.

http://www.dmreview.com/article_sub.cfm?articleID=6861

http://www.dmreview.com/article_sub.cfm?articleID=2514

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright