Fact Table Updates

[c6lapsteel]

Take for example a Sales Fact table which contains Sales Value in local currency and Sales Value in a standard currency (Euro).

The business requires that yearly average exchange rates should be used to convert from local to standard currencies. This means that at each month end, when yearly rates are republished, the Sales Value (Euro) must be recalculated for all records for the current year.

What does experience suggest is the best approach to updating Fact records? A SQL stored procedure (called from a JobStream node at each month end) or updating within DataManager. (This would presumable require re-staging the data for the current year, and then updating back into the Fact Table?).

Thanks in advance,

[Opher]

Hello - I think that best practices are NEVER change the fact record.  Firstly, the fact record is the transaction as it occurred, so it should contain the exchange rate effective at that time.  Second, you would have to change all fact records cumulatively over time - eventually this might be hundreds of millions of rows!  I think the proper course of action is to have a currency conversion reference table so that queries can select either to use the exchange effective when the transaction occurred (from the fact record) or to convert to "current" using the reference table.

That's how I see it.

Good luck,
Opher