The requirement is to flag the incoming record from source to target as I,U,D, I-insert, D-Delete, u-Update. Source and target are various RDBMS system.
Source - Oracle Target - Greenplum
Using lookup on scd type1 we able to achieve I and U flag. Since the lookup is based on equi join of the primary key it was a challenge to flag the deleted record. Not able to use left or full outer join
Your inputs are highly appreciable
I am not sure if I understood the requirement correctly but assuming you are deleting records based on the data not coming from the source.
There could be some ways. You should have a key in source and target to identify the same record.
if above assumptions is correct follow this process :
Please correct if my understanding is wrong with regard to above question.
As Thilak mentioned, source and target are two different RDBMS we wouldn't be able to reverse minus.
You have to handle in two step process, stage everything you receive from source in a staging table within oracle and then while loading delta compare against the staging table to decide I/U/D based on full outer join.
Assuming you always receive full load from source. If source is not sending full data you won't be able to track deletes.
Hope it helps!
Powerexchange is another informatica product which can read from the oracle redo logs to determine changes in the source database records in realtime. Such records can be stamped as I U or D in the DTL__CAPXACTION field. Powerexchange then connects to powercenter mappings through application multigroup source qualifiers.
If the solution they're after is more inline the assumptions the others have made I'd go with Maciej - nice, neat and easy
