What is the best way to deal with currencies in a data warehouse for a worldwide organization that wishes to allow people to view their data in local, reporting currency?


Doug Hackney’s Answer: Several different approaches are available, all requiring someone to establish a business rule around exchange rates. If your parent company is in the UK, you may want to choose the Pound as the "master" currency and convert on a real-time or daily basis as the transactions are loaded into the data warehouse. We usually carry two columns: "master" and "local" currency in this scenario. The bottom line is that you have to pick a point in time when you are going to convert between the currencies and what you are going to use as the exchange rate (Bank of Scotland, Citibank, etc.). If you’re on a monthly reporting schedule, it gets a lot easier. If you are real-time, and you still need to tie out to the monthly/quarterly/annual financials, it’s a lot harder.

Chuck Kelley’s Answer: It depends on how the calculations are to be presented to the users. Are they based on today’s exchange rates or the exchange rate on the date of the transactions? If based on the date of the transactions, then I would calculate all of the different currencies and store them in the data warehouse. The exchange rate would be another source to the ETL process. If it is based on today’s exchange rate, then you have a bigger problem to solve and it would be done mostly in the presentation layer.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access