Fact Table Loading Strategies

Hello Everyone,

I want to know the strategies of loading the Fact table. I could see in Kimball's forum for best practices and it is suggested to have only insert for Fact table and it is not advisable to update the fact rows. Is this a suggestion because of considering performance or a general practice in Datawarehousing? If there is a requirement to keep the fact data in sync with Source system, can we update the data in Fact table by identifying the changes using the Natural keys? Please let me know the Pros and cons.

Thanks,
Sudharsan

The proper solution is to use timespan fact tables: add effective date , expiration date and Active attributes to your fact table, so you will have both versions. Of course you will work in analysis with the active rows.

If the business only cares about current data you can also keep the historical data in a separate table something like an "Audit_Fact" where you can stored expired records, that way, should the need arise to go back in time you can UNION the data in the tables and see the history as well. There are different ways to do this depending on the ETL tool you use.

One of the main purposes for having an Enterprise Data Warehouse is the ability to analyze data from past and present in order to make intelligent decisions for the business.

RGC

Insert-only is the preferred method, not the only method. It is preferred because it is the easiest to do and retains a full history.

Wither you use a transactional (insert only) fact, snapshot fact or accumulating snapshot fact, depends on your data, the nature of queries and the source. Do what makes sense.