FACT table Incremental load!

View previous topic View next topic Go down

FACT table Incremental load!

Post  rami.qutub on Wed May 05, 2010 2:17 pm

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

rami.qutub

Posts : 1
Join date : 2010-05-05

View user profile

Back to top Go down

Re: FACT table Incremental load!

Post  ngalemmo on Wed May 05, 2010 4:49 pm

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

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

View user profile http://aginity.com

Back to top Go down

Incremental Load ( With Out Date Field)

Post  arunmani916 on Sat Nov 27, 2010 1:06 pm

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.

arunmani916

Posts : 1
Join date : 2010-10-11
Location : CA

View user profile

Back to top Go down

Re: FACT table Incremental load!

Post  hang on Sun Nov 28, 2010 8:17 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: FACT table Incremental load!

Post  warrent on Wed Dec 01, 2010 7:56 am

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

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: FACT table Incremental load!

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