Continue in 2 seconds

We are planning a multinational data warehouse.

By
  • Joe Oates, Clay Rehm, Les Barbusinski
Published
  • June 04 2003, 1:00am EDT
More in

Q:

We are planning a multinational data warehouse. This issue raises a number of interesting questions:

  • How should we handle currency?
  • Data from the different countries deliver data at different times - how do we coordinate this?
  • Each country's data is to be used within each country, but it is also a need for a total view. Should we separate each country physically in different tables or by a dimension?

A:

Les Barbusinski’s Answer: Hard to say without knowing more of the details, but here goes…It seems to me that your best bet would be to have a separate set of tables per country with a limited set of aggregate tables to contain cross-national summary data. That way, issues such as synchronization/contention, currency exchange and security need only be addressed with the cross-national summary data. This approach does impose some additional overhead on the database server and requires that ETL scripts and/or BI reports be "cloned," but the added flexibility more than offsets these disadvantages.

Joe Oates’ Answer: This is a common problem for organizations that span national boundaries. There are two competing goals that must be satisfied. First, management needs to know if sales in the local currency are growing or shrinking from time period to time period. Secondly, the company needs to translate foreign currency into the home currency in which it keeps its books.

The solution is to carry information about the local currency and the home currency in the appropriate fact table(s). I am assuming that you will use dimension and fact tables for your design. You need to add an ISO Currency dimension table to your design and have that as a foreign key in the fact table to indicate the local or transaction currency. In addition to having amount column(s) for the transaction, which is usually the local currency, you need to add one or more columns that correspond to each of the local currency transaction amount for the home currency. The ETL process should do the conversion of the local currency to the home currency for the day that the transaction took place. Using the value at the end of a week or end of a month can give misleading results due to the volatility of foreign exchange rates.

This approach will avoid misleading analysis results. There are several reasons for this. Among them is the situation in which due to the devaluation of a local currency, if only the home currency amounts were kept, it could seem as if there was a sales decline even though sales may really be rising in the local currency. Management will want to do analysis on both the local currency amounts and the corresponding home currency amounts for transactions.

Clay Rehm’s Answer: You will need to track currency history for the currency exchange rate at the time of the transaction as well as the current exchange rate. This means you will need an additional column for the exchange rate and a column for the date of the transaction so the currency can be calculated for a date. You will also need to maintain history for each transaction and the currency rate at the time of the transaction. This way you can provide the exchange rate at the time of the transaction no matter when it happened.

Different data refresh times should not matter – publish the refresh schedule (by country) to your customers on your Web site so they know when data will be refreshed.

Regarding country, I would not track each country separately. I would track country as a dimension and make sure it has as many attributes (columns) as possible in it to make it useful.

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