FACT table Incremental load!

[rami.qutub]

Hi

I have huge data source (1TB), that data source table is not well designed, its primary keys are four columns which are all nvarchar! and in that source there is no date filed neither a serial or sequence #!
my question, how I can implement an incremantal load of the fact???
thanks

[ngalemmo]

Which database system is being used by the source system?

If it is Oracle, there is a pseudo-column called ORA_ROWSCN which is, essentially, an update timestamp. You can use that to locate changed blocks (a block may contain multiple rows, so it is not precise, but better than pulling everything) since the last extract. Other database systems may have similar information available. Look in the manuals.

Depending on your situation, you may need to do further filtering to see if data actually changed.

[arunmani916]

Hi,

I am also facing a similar situation. My Source is SQL server 2007. The source table has a primary key column but there is no date field. And the source count is in Millions. If it has less data I will go with the key column. But It has huge data.

[hang]

Hopefully it's SQL Server 2008 so you can utilise the Change Data Capture feature to let the server work out the delta data in the change table, and it is precise. Otherwise you may have to add a little overhead to the source OLTP system with following options:

1. Adding create and last modified fields to the source table.

2. Adding flag fields to indicate when records have been extracted.

3. Creating triggers to store changes in change capture table.

[warrent]

Another option for SQL Server, which is similar to Oracle's ORA_ROWSCN in Nick's suggestion above, is the ROWVERSION data type (formerly known as TIMESTAMP). You need to add a column to the source table using the ROWVERSION data type. It is a counter on each row that increments whenever a row is inserted or updated. You get the current ROWVERSION using the MiIN_ACTIVE_ROWVERSION() function. To use it, you would save the MIN_ACTIVE_ROWVERSION() number from each run of the ETL system. Then, when you go to get the incremental rows for the next load, you only need to select out those rows with a ROWVERSION value greater than the value you saved from the previous ETL load. These are the incremental set of new and changed rows.

[Sponsored content]