RE: What are the various methods of getting incremental records or delta records from the source systems...
One foolproof method is to maintain a field called 'Last Extraction Date' and then impose a condition in the code saying 'current_extraction_date > last_extraction_date'.
RE: What are the various methods of getting incrementa...
Using mapping parameters and variable or type1 we can easily define from where parameter will start and how variable will change as deltas will get from OLTP systems
RE: What are the various methods of getting incrementa...
Above solution will not be helpful in case of late arrivel fact and late arrivel dimentional records. Generall in any data source we will have transaction date ( when the transaction happened) and load date ( when the data loaded in source ) for numerical measure data similarly Ener date ( when the details are provided by the cutomer ) load date for dimentional data . we have to use both the date stamps to retrive the data from the data sources.
RE: What are the various methods of getting incremental records or delta records from the source systems?
We can use control table update and ipf files for capturing incremental data or delta data from a source. Control table will maintain the details like from which timestamp (previous) to which timestamp (current) we have taken the data. If the session is taking data everyday (daily run) then the delta will be of one day. Previous timestamp will be of yesterday's date (P1) and current timestamp will be the time (C1)of run of the job. So in today’s run we will get one day data. Next day when job runs C1 will become P2 and today’s run time will become C2 so we will not miss any records incremented i the source systems. This will go on. Incase on weekly runs the delta will be of one week. Let me know if you need any further information.
RE: What are the various methods of getting incremental records or delta records from the source systems?
Usually CDC(Change Data Capture) is used for delta records. Incremental records is only possible if the source system contains Created data or Modified Date. So the condition looks like this
Createddate> Last Transformation date or Modified date> Last Transformation Date.
In ETL in the schema if one table has date field and another table didnt my suggestion is not to go for CDC because one table will take in Truncate and load method and another table contains CDC data alone. So it lead to mess up..
