Data Warehousing Community Forum
February 10, 2012, 01:25:01 am

Pages: [1]   Go Down
  Print  
Author Topic: How to ensure that new rows(data) from the source will get inserted into target  (Read 1071 times)
shankar.singu
DW Fresher
*

Reputation: +6/-0
Offline Offline

Posts: 16


View Profile
« on: May 26, 2010, 03:11:09 pm »

Hi,

How to ensure that new rows (data) from the source will get inserted into target?

I found in google the following answer.
“In the SCD Type2 we have three options to recognise the newly added rows: 1. Version number 2. Flag value 3. Effective Date Range”

But I am not clear that how it works. Can anyone please elaborate the above answer?
Regards,

Sankar. S
Logged
Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #1 on: May 27, 2010, 11:48:19 am »

“In the SCD Type2 we have three options to recognise the newly added rows: 1. Version number 2. Flag value 3. Effective Date Range”

To insert new rows into target you essentially need some way of identifying which rows in the source are new.
By new rows, of course, we mean rows that have not already been inserted into target before.

This can be achieved in many ways, some of which are -
1. Version number - The version number in the source is incremented everytime there is a change in a row.
When populating target from source, you compare the IDs of the rows.
If an ID exists in source but doesn't exist in target - this is a new row and must be inserted in target.
If the version number for the same ID is different in source and target - this is a modified row and must be updated in target.

2. Flag Value - Any row with flag value "set" is considered new and inserted into target.
Once you insert a row in target, you "unset" its flag value so you don't insert it again during next run.

3. Effective Date Range - All rows in source include a date value column.
The target keeps track of the date range that has already been pulled.
All rows newer that the already pulled date range are considered new and are inserted in target.
Logged

If most people said what’s on their minds, they’d be speechless.
Pages: [1]   Go Up
  Print  
 
Jump to: