Multiple local or transaction Currency

View previous topic View next topic Go down

Multiple local or transaction Currency

Post  davidbi on Fri May 02, 2014 8:29 am

My data mart has multiple currency.
For example 1.order amt(Euro)
2.cash back associated with order(Yen)
3.Referring Cash Back(Can$).
And i will all these measures converted to standard us dollars.

Initially i was thinking of one transaction currency and one global currency but it won't work for the above rule. Any one have suggestions or ideas on the above scenario ?



davidbi

Posts : 11
Join date : 2014-04-24

View user profile

Back to top Go down

Re: Multiple local or transaction Currency

Post  ngalemmo on Fri May 02, 2014 1:45 pm

First, it is extremely unusual for a single transaction to be recorded in multiple currencies. It is not unusual to express a transaction in a local and standard currency. As far as calculating the standard currency value, you need to discuss with your Finance dept. Currency exchange almost never happens at the time of the transaction, so there is often a nominal exchange rate that may change daily, weekly or even monthly depending on how FX is handled by the company.

Aggregations are performed on the standard currency. The local currency is semi-additive. If you so sum local currency, it must be bounded by the currency. It is possible to produce a full summary in a local currency if you apply the nominal exchange rate to the standard currency during summarization.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple local or transaction Currency

Post  davidbi on Fri May 02, 2014 2:11 pm

Thanks for the response. What i am planning to have in the fact table is
OrderAmt local Currency| OrderAmt US Currency |Cash back associated with order local Currency|cash back associated with order US Currency |Referring Cash Back Local Currency |Referring Cash Back US Currency

So total 6 measures.
Do you think this is a good option or any suggestions ?

davidbi

Posts : 11
Join date : 2014-04-24

View user profile

Back to top Go down

Re: Multiple local or transaction Currency

Post  ngalemmo on Fri May 02, 2014 2:15 pm

That's fine. I assume there is a currency dimension as well and a support table with exchange rates.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple local or transaction Currency

Post  davidbi on Fri May 02, 2014 2:25 pm

Yes, that's right. I have one currency dimension and one currency fact table. And if i need to convert to any currency the user wants, then I got to have combination of all currency for every currency for a every date right ?

davidbi

Posts : 11
Join date : 2014-04-24

View user profile

Back to top Go down

Re: Multiple local or transaction Currency

Post  ngalemmo on Fri May 02, 2014 8:16 pm

You could just have conversions to standard.  It all depends on how precise it needs to be.  Unless you are a financial institution it probably doesn't matter that much, since the standard currency value is an approximation anyway.

Also, conversion rates are usually stored in two mutually exclusive columns: a multiplier and a divisor. You would use one or the other depending on the currency. The reason for this is some currencies may have many thousand to 1 ratios and you can lose significant precision if you only store it one way.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple local or transaction Currency

Post  davidbi on Mon May 05, 2014 9:19 am

Alright, thanks for the tip.

davidbi

Posts : 11
Join date : 2014-04-24

View user profile

Back to top Go down

Re: Multiple local or transaction Currency

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum