multiple currencies for measures in Fact Table

Hello,

In my transaction table, a transaction can be in any currency in which the customer is paying (OCUR for Original Currency) - plus the transaction is also always converted in USD (SCUR for standard currency). I have included a Currency dimension that will allow relevant currencies to be displayed for the measures in the fact table. However, very often users view the cube without bringing the corresponding currency measure. This leads to erroneous values displayed (aggregation of measures in different currencies).

I do not wish to store currency values in the measures, since it is going to be cumbersome. (e.g. measure displayed in all possible currencies). Is there any other model I can consider?

Thanks
Maleeksha

Why don't you remove original currency from cube...? It's not as crazy as it sounds, you could create an SSAS perspective that hides the orig ccy dimension and measures and only display the USD measures. Perhaps you need to create audience centric perspectives, one for basic cube users without origg ccy measures and one for advanced users that includes all measures. Sorry, I have assumed you are using SQL Server Analysis Services - perspectives may not be an option if you use a different technology.

Your problem is to do with user education, if the reporting requirement is to have both original and standard currency then you probably can't remove original and you'll have to educate the users to bring in original currency dimension when using a measure that isn't USD.

One method I have seen used is to label the measure with an appropriate name that indicates the currency. Such as PremiumOrigCCY and PremiumUSD.

thedude wrote:
Your problem is to do with user education, if the reporting requirement is to have both original and standard currency then you probably can't remove original and you'll have to educate the users to bring in original currency dimension when using a measure that isn't USD.

Thanks thedude,

user education is the hardest part!!! - I'll try to see if comments(rather 'cautions'!) can be associated with the measures display in the OLAP tool.

Thanks,
Maleeksha

In your query tool, make the column with the individual currencies not so obvious.