Results 1 to 2 of 2

Thread: CDC to perform SCD type 2

  1. #1
    akanksha.1787 is offline Junior Member
    Join Date
    Nov 2016
    Rep Power

    CDC to perform SCD type 2

    Hi Experts,

    Does, the Attunity Replicate tool also implement the SCD Type 2.
    As in ,does the tool maintains the various old and new versions of the updated records like in other ETL tools?

    Thanks & Regards,

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Nashua, NH - USA.
    Rep Power
    >> SCD Type 2

    Hmm, I guess you are referring to : Slowly Changing Dimension

    Google suggests theses definitions. They appear to the 'Informatica' related.

    Type 1: The new record replaces the original record. No trace of the old record exists.
    Type 2: A new record is added into the customer dimension table.Thereby, the customer is treated essentially as two people.
    Type 3: The original record is modified to reflect the change.

    Replicate does #1 in the course of its normal 'apply changes' option.

    Replicate would do #2 if the source does that.
    Silly make-up example. Customer (old) address is stored under a PK with id = 123
    Now the customer moves and a new address is created with id=812, the customer record is update to point to 812, not 123 anymore.
    Replicate will just follow that exactly.

    For #3 it seems there is some sort of denormalization taking place.
    Closest you get to that with Replicate is through the "data enrichment" transformation functions source_lookup (or target_lookup).
    Replicate only executes those when the base record is updated.
    Let's say a sale is made with a foreign currency and a column is added with approx US dollar value calculated using a lookup for the current exchange rate.
    Now the exchange rate changes. That sale record does NOT change.
    But if say a comment on the sale record is updated, then the new calculation is re-run unless additional logic only executes the math on insert.

    For complex SCD actions you may want to look at the Replicate STORE CHANGES option in addition to, or instead of TARGET APPLY
    With the STORE CHANGES options, replicate will create 'change table' entries (__CT) which for updates will have a Before Image and After Image and will have header information identifying the TIME, Transaction, and User if/when the database source provides that.

    Hope this helps,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts