Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact Table Loading Strategies

4 posters

Go down

Fact Table Loading Strategies Empty Fact Table Loading Strategies

Post  sudharsan1984 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

Back to top Go down

Fact Table Loading Strategies Empty Re: Fact Table Loading Strategies

Post  gvarga 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

Back to top Go down

Fact Table Loading Strategies Empty RE: Fact Table Loading Strategies

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

Posts : 13
Join date : 2013-11-04

Back to top Go down

Fact Table Loading Strategies Empty Re: Fact Table Loading Strategies

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

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

http://aginity.com

Back to top Go down

Fact Table Loading Strategies Empty Re: Fact Table Loading Strategies

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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