DAC- Change Capture Mechanisms - OBIEE GURU

govtjobonline-Latest Government Jobs 2023 Recruitment Notifications

Latest Govt Jobs Whatsapp Group Links-Telegram

For Latest Govt Jobs 2023 or for Freejobalerts or Join

Latest Government Jobs 2023

Thursday 14 August 2014

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

    Whatsapp group

    Govt Jobs Whatsapp Group Links 2023

    Telegram group

    Govt Jobs Telegram Group

    Facebook group

    Govt Job Online Facebook Group

    Follow on Facebook group

    Govt Jobs Online Facebook Page

    Subscribe to Youtube Channel

    Govtjobsonline Youtube

    Join Latest Govt Jobs Telegram Group here

    Join Government Jobs Whatsapp Group here (State Wise)

    Join State Wise Sarkari Results Telegram here

    1. ITI Jobs Notifications
    2. ITI Apprenticeship Program
    3. Diploma Jobs Notifications
    4. Engineering Jobs Notifications
    5. UPSC Jobs Notifications
    6. Court Jobs Notifications
    7. Staff Nurse Jobs Notifications
    8. NHM Jobs Notifications
    9. Exam Syllabus and Pattern
    10. SSC Recruitment

    No comments:

    Post a Comment

    Latest Govt Jobs Vacancy 2023