Fact Table Loading Strategies

View previous topic View next topic Go down

Fact Table Loading Strategies

Post  sudharsan1984 on Thu Aug 13, 2015 6:20 pm

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

sudharsan1984

Posts : 1
Join date : 2013-03-11

View user profile

Back to top Go down

Re: Fact Table Loading Strategies

Post  gvarga on Fri Aug 14, 2015 4:00 am

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.

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

RE: Fact Table Loading Strategies

Post  turbotortuga on Fri Aug 14, 2015 7:32 am

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
avatar
turbotortuga

Posts : 13
Join date : 2013-11-04

View user profile

Back to top Go down

Re: Fact Table Loading Strategies

Post  ngalemmo on Fri Aug 14, 2015 3:04 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table Loading Strategies

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