Muliple currencies for periodic snapshot fact table

View previous topic View next topic Go down

Muliple currencies for periodic snapshot fact table

Post  cube.head on Fri Aug 14, 2009 9:21 am

We are designing a periodic snapshot fact table that will collect the cost of various tasks, on a monthly basis. The user is an international company, so many of the tasks are expressed in the currency of the country where the work will take place. They don't want to store or calculate exchange rates, they just want to store the current value in the currency for the task, at snapshot time.

But I'm struggling with how to store these values. Do I create a fact table column for each type of value, for every currency?

For instance -- for a column called BudgetedCost, I can record it in the fact table along with all the dimension keys that are applicable to the fact for that snapshot, just like any other fact table. But to account for all the different currencies, do we:

1. Create multiple fact rows that differ only by a different CurrencyID from the Currency dimension?, or
2. Create multiple fact columns, a column for each measurement for each currency?
3. Something else?

1. and 2. both give me anxiety, but I'm leaning towards 2., even if it means a fact table with hundreds of columns...

Thanks for any insight!

cube.head

Posts : 6
Join date : 2009-08-03

View user profile

Back to top Go down

Re: Muliple currencies for periodic snapshot fact table

Post  ngalemmo on Fri Aug 14, 2009 10:34 am

Why would option 1 lead to anxiety? It is the correct way to do it. Remember, the purpose of a dimension is to provide context to the data. Currency is one such context. Besides, what do you plan to do if they add a new currency? As a dimension, it is just another row in a dimension table. Under option 2 you need to change the model and all reports that use the fact table.

Also, if, in the future, they decide to add currency rates and allow currency conversions? As a dimension, adding this functionality is easy while under option 2 it would be incredibly difficult and complex.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Muliple currencies for periodic snapshot fact table

Post  cube.head on Fri Aug 14, 2009 10:47 am

I think I mis-spoke - what they REALLY want to do is collect each fact in whatever currency they want - even if it's for multiple currencies for the same fact.

I guess for some reason I'm stuck on the idea that each single fact row is a measurement for that period, for that task, for that location, for that employee, etc... It's almost like duplicating fact rows JUST for the sake of being able to see the values in each currency is bad for some reason.

But then again, I guess this is the whole point of the dimensional model? The fact rows aren't duplicated - they differ by one attribute: currency, and the values for the measure columns for each "duplicated" fact row is specific for that currency. Right?

The points you make about adding currencies later, or using exhange rates - VERY good point!

cube.head

Posts : 6
Join date : 2009-08-03

View user profile

Back to top Go down

Re: Muliple currencies for periodic snapshot fact table

Post  ngalemmo on Fri Aug 14, 2009 11:28 am

How do they expect to get the value expressed in 'any currency they want' without storing exchange rates? You MUST have exchange rates and the business needs to define the rules for what that exchange rate is... i.e: the closing rate, the noon rate (NY or London), end of month rate, whatever... I mean you simply do not store the value in every concievable currency when you load the facts.

Normally, when exchange rates are involved, you create two sets of columns. One holding the value in the local currency (as indicated by the currency dimension) and another holding the value in a designated standard reference currency (usually US$, Euro or the currency used in the corporate financial reports).

You hold the exchange rates in a separate fact table dimensioned by date, and 'from' and 'to' currency. You can do currency conversion one of two ways (depending on what the business wants, and what data you have available). You can either directly convert from one currency to another or between the standard currency and another currency (triangulation).

But, upon rereading your reply you are implying that the users will manually enter multiple currencies for the same amount... and that there is no requirement to provide other currencies that they did not enter... all I can say is that's one of the strangest things I've ever heard.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Muliple currencies for periodic snapshot fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum