Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Delete from target using dimension lookup/update step

  1. #1
    Join Date
    Nov 2016
    Posts
    25

    Exclamation Delete from target using dimension lookup/update step

    Hi All,

    I have implemented SCD2 using dimension lookup update step and can populate records with flag 0 or 1. There's a situation in which my source data is getting deleted over the time and same should be reflected in target as well. Is this possible in dimension lookup update step to delete target data when it no more exists in source ?

    Currently, I am doing
    1. Target left join Source
    2. Delete those records from target for which source.business key is null

    Which deletes data perfectly which is no more in source, it would be better if this can be done in one KTR only.
    TIA,
    Sanket Kelkar.

    PDI CE 6.1
    Oracle 11g
    Windows Server 2012

  2. #2
    Join Date
    Feb 2017
    Posts
    18

    Default

    Quote Originally Posted by sanketk View Post
    Hi All,

    I have implemented SCD2 using dimension lookup update step and can populate records with flag 0 or 1. There's a situation in which my source data is getting deleted over the time and same should be reflected in target as well. Is this possible in dimension lookup update step to delete target data when it no more exists in source ?

    Currently, I am doing
    1. Target left join Source
    2. Delete those records from target for which source.business key is null

    Which deletes data perfectly which is no more in source, it would be better if this can be done in one KTR only.
    I do not think that it is possible with the Dimension Lookup Step Directly. However there is a "Merge-Rows (Diff)" Step that you can use to generate a delta of your old Dimension Table and your new Dimension Table (rows that changed will be passed based on a key value). You could probably create an easy logic with a temporary file to store the new "snapshot" of the source data and compare itw ith the Dimension Table that way and delete the data based on these Deltas.

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by sanketk View Post
    There's a situation in which my source data is getting deleted over the time and same should be reflected in target as well.
    That's not how SCD is supposed to work.
    You simply expire the dimension value.

    Remember, in your data warehouse, you have all of your historical values, so you still need to have your old dimension values.

    Change your "Valid Until" value to indicate that it's no longer valid.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.