Overwrite fact tables?
3 posters
Page 1 of 1
Overwrite fact tables?
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.
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
Re: Overwrite fact tables?
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.
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.
Re: Overwrite fact tables?
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.
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
Similar topics
» Does it belong in the stage tables or fact tables?
» Difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact tables, Aggregate tables or a different approach
» Number of Columns in Fact Tables vs. Dimension Tables
» Difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact tables, Aggregate tables or a different approach
» Number of Columns in Fact Tables vs. Dimension Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum