BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

Main | February 2007 »

January 27, 2007

How to design fact table for multicurrency column

By: Milind Zodge


Most of the time we have companies doing business in more than one country. If you have KPIs which demands to do a comparison in original currency you will need to have a provision in your design. In this article I am focusing on this case.

Design
There are two ways you can design this.
1. Add a column which will hold the conversion rate of that day/ date. So your fact table will have following columns: local currency amount column, conversion rate, and can also use USD amount column which will hold the converted amount. This will solve the purpose of recording the conversion rate. However if you want to slice and dice the data based on the different currencies and different point in time, you can design using the next approach.
2. Create a Currency Dimension which will hold the conversion rates for every day. Now in fact table store the respective surrogate key along with other amount column. Now you can design an analysis cube to use this dimension.

Conclusion
This way you can achieve maximum performance for your queries or reports or cubes to analyze the data with respect to a daily changing rate.

Share: del.icio.us Digg Furl ma.gnolia Netscape Newsvine reddit StumbleUpon Yahoo MyWeb  

Posted by Milind Zodge at 10:45 PM | Comments (2)