« Find out how to achieve change data capture for Oracle 9i database without adding triggers on the source table | Main | How to design if many to many relationship exists between dimension and fact table »
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.
Posted by Milind Zodge at January 27, 2007 10:45 PM
