DAC- Change Capture Mechanisms

There are two kinds of change capture mechanisms

• Change capture using tables:
  
  This is the most common method. It uses S_ETL_I_IMG_ and S_ETL_R_IMG_ table types and the LAST_UPD column of the source tables.
• Change capture using the date column:
  
  In some cases, a predefined date column is used to enable change capture (without use of S_ETL_I_IMG_ and S_ETL_R_IMG_ tables).
  How it will works during loads:- 
  
  
  
  
  • S_ETL_D_IMG_* tables: These are “delete” tables that are used to capture data for rows that have been deleted in the OLTP. Rows are inserted into these “D” image tables via database triggers.
  • S_ETL_I_IMG_* tables: These tables are used for all incremental changes (inserts/updates/deletes). Data is loaded to these tables from their corresponding OLTP base tables and “D” image tables via the DAC at the beginning of a load.
  • S_ETL_R_IMG_* tables: These are “reference” tables that reflect the data that has been loaded to the SRMW. For performance reasons, only rows with last_upd within the prune period are retained in this table. (“Prune days” is explained later in this document.) Data is loaded into the “R” tables via the DAC at the end of a load.
  • Here is a little more detail about why they use this process.
    The LAST_UPD datetime column in the Siebel CRM cannot be
    reliably used to identify changed records (so SELECT * FROM
    S_REVN WHERE LAST_UPD > LAST_EXTRACT_TIME won't work) so they
    populate some IMG tables with the ROW_IDs and modification_nums
    of all records previously extracted during prior ETL runs and
    compare them to the current values in the CRM system. 
  • W_ETL_D_IMG* ,
    W_ETL_I_IMG* AND W_ETL_R_IMG* tables.
    W_ETL_R_IMG* holds all the ROW_IDs and modification_nums for
    records loaded during prior ETL runs
    W_ETL_I_IMG* holds all ROW_IDs and modification_nums where the
    modification_num is either different from the corresponding
    record in the R_IMG table (ie this record has changed since the
    last ETL run) or does not exist in the R_IMG table (ie has been
    inserted since the last ETL run). At the end of the ETL run,
    these are transferred to the R_IMG table and the I_IMG table is
    truncated at the beginning of the next ETL run.  
  • Finally   
    Open the DAC client, navigate to the Tables tab. You will see an
    IMG_SUFFIX column, each Siebel CRM table's IMG suffix is listed
    there. DAC generates the views based on what is listed here.
    If you are building your own custom ETL mappings to extract from
    tables not already mapped, you would add a custom suffix (C1 for
    example) to your table and DAC will build the view during the
    next ETL run. After adding the suffix, right click on the table
    name and select the option to create the SQL for the IMG tables
    and be sure to run that SQL on the database before the next ETL