BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

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

Comments

wonderful points altogether, you just gained a brand new reader. What would you suggest in regards to your post that you made a few days ago? Any positive?

Posted by: London escort agency at May 16, 2011 4:33 PM

This web site is really a walk-through for all of the info you wanted about this and didn’t know who to ask. Glimpse here, and you’ll definitely discover it.

Posted by: Alegro at May 17, 2011 4:08 AM

Post a comment




Remember Me?