Multi-currency metrics on fact table
Page 1 of 1
Multi-currency metrics on fact table
Wondering what opinions people have regarding how a single metric is stored for multi-currency on a fact table. Consider the following scenario:
Fact table has 50 financial metrics on it with a time grain of month (one row per month per combination of the other dimension keys). Those metrics need to be represented in potentially 4 currencies at a month-end rate; base/local currency, USD, EUR and JPY (our biz has 3 primary offices globally). Is it better to make the fact table super wide, have one column for each currency for a total of 200 metrics and precalculate them in the ETL? Or is it better to force the ad-hoc query user and/or report developer to join to an exchange rate 'dimension' table (or just store the rates on the fact table) and calculate them real-time basis what the local office wants to see?
The other consideration is that the number of metrics on this table will continue to grow, so maintenance is a concern.
Option 1) The DBA side of me wants to store the month end exchange rates for each currency on the fact table and all the other metrics just once, and then have the query users/report developers calculate the different currencies themselves, leaving me with a smaller fact table where I only have to add one column if a new metric is needed, instead of 4 columns (one for each currency). This is flexible as well; if an addtional currency is desired only one exchange rate needs to be added to the fact table, or in the case of it being in a dimension, nothing needs to be done.
Option 2) The BI Architect side of me wants to precalculate everything in the ETL and have the amounts for each currency stored and ready for access, making the fact table as easy to query as possible. Having to compute a currency conversion as well as aggregate across the other dimension hierarchies is an added complexity that could cause issues for users. Disk is cheap, right? Just store it all, that's what the DW is for. Problem is that very wide tables start to be a performance issue and make it harder to find what you're looking for. Adding the extra columns to the table one time isn't too big a deal, but also adding them and maintaining the respective ETL for all of it can become time consuming, especially if someone decides that we need to add another currency which would cause the table to grow by yet another 50 columns.
Anyone have a best practice here? My current situation is that I am already pre-calcing and storing base currency and USD, but need to expand this to the other two currencies. Therefore do I add 100 columns to my existing 100 column fact table, or do I remove the 50 pre-calc'd amounts and just go with real-time currency conversion?
Fact table has 50 financial metrics on it with a time grain of month (one row per month per combination of the other dimension keys). Those metrics need to be represented in potentially 4 currencies at a month-end rate; base/local currency, USD, EUR and JPY (our biz has 3 primary offices globally). Is it better to make the fact table super wide, have one column for each currency for a total of 200 metrics and precalculate them in the ETL? Or is it better to force the ad-hoc query user and/or report developer to join to an exchange rate 'dimension' table (or just store the rates on the fact table) and calculate them real-time basis what the local office wants to see?
The other consideration is that the number of metrics on this table will continue to grow, so maintenance is a concern.
Option 1) The DBA side of me wants to store the month end exchange rates for each currency on the fact table and all the other metrics just once, and then have the query users/report developers calculate the different currencies themselves, leaving me with a smaller fact table where I only have to add one column if a new metric is needed, instead of 4 columns (one for each currency). This is flexible as well; if an addtional currency is desired only one exchange rate needs to be added to the fact table, or in the case of it being in a dimension, nothing needs to be done.
Option 2) The BI Architect side of me wants to precalculate everything in the ETL and have the amounts for each currency stored and ready for access, making the fact table as easy to query as possible. Having to compute a currency conversion as well as aggregate across the other dimension hierarchies is an added complexity that could cause issues for users. Disk is cheap, right? Just store it all, that's what the DW is for. Problem is that very wide tables start to be a performance issue and make it harder to find what you're looking for. Adding the extra columns to the table one time isn't too big a deal, but also adding them and maintaining the respective ETL for all of it can become time consuming, especially if someone decides that we need to add another currency which would cause the table to grow by yet another 50 columns.
Anyone have a best practice here? My current situation is that I am already pre-calcing and storing base currency and USD, but need to expand this to the other two currencies. Therefore do I add 100 columns to my existing 100 column fact table, or do I remove the 50 pre-calc'd amounts and just go with real-time currency conversion?
Guest- Guest
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum