Overwrite fact tables?

View previous topic View next topic Go down

Overwrite fact tables?

Post  nba411 on Wed Jul 31, 2013 4:06 pm

Hi ,

General curiosity here. I know you can update fact tables, but do others overwrite or even truncate fact tables for each load? Or do fact tables maintain a history of changes like dimensions can?

Thanks.

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Overwrite fact tables?

Post  ngalemmo on Wed Jul 31, 2013 5:10 pm

There are various ways to process facts, it depends on what they are. You can update with history (current and old versions of the row), update in place (no row history), or insert only.

I have never seen a situation where one would truncate or replace an entire atomic fact table coming from an operational system for obvious reasons:
1. Fact tables can be huge
2. You generally have more history in the data warehouse than they do in the operational system (longer retention)

It is not uncommon, when producing aggregates, to replace the aggregate table. This assumes an aggregation of existing atomic facts.

In general, you should never only load aggregate data from a source. Always load and maintain atomic level facts and aggregate from them if needed.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Overwrite fact tables?

Post  VHF on Thu Aug 08, 2013 12:51 pm

We currently truncate and rebuild fact tables daily in our sales DW. The largest is 14M rows (1.3GB) representing 7 years of sales history. The fact table is reloaded from two different staging tables from two different source systems. This takes under 5 minutes (SQL Server 2008.)

We periodically re-evaluate this approach. At some point our fact table might get too big for this to work, but so far it has remained viable. The biggest advantage of this approach is incorporating updates in the data without having to update existing records or insert special delta change fact records.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Overwrite fact tables?

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