Time Sensitive Measures

What is the best practice for designing facts that have measures that are particular to a point in time? For example, our sales fact needs to show the following:

- amount in US Dollars using exchange rate X that was in affect at the time the transaction occurred
- the amount in US Dollars using exchange rate Y that was in affect at the time the transaction occurred
- amount in US Dollars using exchange rate X that is CURRENTLY in affect

Assuming we have the ability to look up an exchange rate for currency Z to USD at a given point in time, how do we represent this on the fact? We are noticing this is a recurring challenge as we typically want to show all monetary amounts in several difference currencies at several different exchange rates.

The two solutions which we've explored are either:

1) Update the various amount measures (USDInGAAPAtTransactionTime, USDInManagementRateAtTransactionTime, USDInCurrentGAAPRate, etc.) whenever exchange rate changes occur
2) Do some fancy views/joins to expose these numbers from the other tables on the fact

Option 1 is somewhat simple and offers good performance but as a matter of principle it seems to be a bad practice to update measures on facts that have already transpired.

Option 2 provides maximimum flexibility and facts are automatically updated when the exchange rate dimensions are updated, but is fairly complex and we are concerned with performance issues when doing so many joins.

I look forward to your feedback! This is my first post on the forum and I hope to have more

Don't store the 3rd measure in the fact table. Do the calculation based on an exchange rate lookup at query time... its real easy to populate a dimension table with current exchange rates. Carry the currency exchange FK in the fact.

Usually this approach is used when you have one target currency (which appears to be the case). You can use more flexible structures (such as a bridge table) if you need to covert from one currency to any other currency.

Thanks ngalemmo. Would you change your answer if there were ten measures on the fact that needed to be calculated based on today's date as opposed to just one? We started with that approach but it's actually getting more complicated. Let me explain.

We have an exchange rate table that looks something like this:

StartDate EndDate SourceCurrency TargetCurrency ExchangeRateType ExchangeRate

So for a given date there can only be one effective Exchange Rate for a given Exchange Rate Type (e.g. frozen yearly rate, monthly rate, etc.).

Now in our OLTP database we have a sale which has an Amount and a Currency Code. What I didn't mention in my original post is that the exchange rate can be updated historically and that needs to be reflected in the cube. For example, they might go back at the end of the year and say "The official exchange rate that we want to use for fiscal year 2011 is X" at which point we need to show the new amounts.

To your point, we could just look up the appropriate exchange rate at query time. This doesn't seem so bad if you have just a few amounts to show. In our case we actually need to show the amount in USD and in one other currency (dependent upon the customer). So we are talking about 6 different amount fields (2 different currencies x 3 different exchange rates).

Now I'll sprinkle on the final layer of complexity. We also want to associate cost with the transaction. Similar to the actual sale amount, there are a couple of types of cost which we need to record. The cost is actually associated with the product. So we have something like this: Sales Fact => Product => Cost

The cost table looks like this:
StartDate EndDate ItemNumber CostType CurrencyCode CostAmount

We want to have the following cost fields on our fact:
- CostInUSDAtExchangeRateXForCostTypeA
- CostInUSDAtExchangeRateYForCostTypeA
- CostInUSDAtExchangeRateXForCostTypeB
- CostInUSDAtExchangeRateYForCostTypeB
- etc.

So the joining get's even more complex because to show the cost in USD you have to look up the exchange rate that is in affect at that time as well as the cost that is in affect at that time. This is also quite doable--but I'm concerned about whether this is really the best practice to look this up at query time.

So my question is really about whether real-time lookups through bridge tables is a scalable solution for a large number of fields or if we should be looking at actually stamping the measures on the fact and updating facts whenever the dimensions change. Thanks again for your help!

PS: Your original answer might still be the right answer, I just wanted to clarify that it is more complex than what I started with.

If your requirement is to produce conversions to US$ only, I would consider building a simpler version of the currency exchange table as a separate (new) bridge table with date and source currency keys. I would eliminate the date range, target currency, and exchange rate type making the join much, much, simpler. There would be one row per day and a row would contain separate columns for each exchange rate type (year, month, day). (I am assuming there isn't that may different types of rates). The process that generates this bridge would generate one row for every calendar date, including rows with the same rates where the source conversion table (i.e. your current table) spans multiple dates. This would allow you to do a simple, direct, equi-join to the facts, which will perform much better than a range based join.

Even if you need to create a bridge with source and target currencies, it is still manageable as long as the user can only select one currency. You can reduce the size of this bridge if it makes sense to limit it to a short list of target currencies . It may also make sense to implement this as a second bridge table and only used to satisfy the user specified currency requirement. The other bridge table (or a view of this table with a built in filter) could be used to handle the default US$ conversions.

Because the new table contains all types of exchange rates you will not need to perform multiple role-based joins should a query wish to compare the results using different rate types. Also, since the table is small (one row per day per source currency), maintaining it should not be a big deal.

Since this solves the join issue, it also allows you to simplify the measures maintained in the facts. You would not need to pre-calculate and store any measures that use these rates. Reducing the width of the fact table goes a long way to significantly improving performance and data storage requirements.