Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Slowly changing dimension based on startdate

  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Default Slowly changing dimension based on startdate

    In short : i have a problem with SCD's where i use a column that already exist in my data as startingdate for my dimension.
    Verbose : We have al test table with 6 colums (see input.png). I have a key (KEY_ID), a department (AFDELING) and a date the people started working (DATUMVAN). Some other fields name( naam), phone(tel) and adress(adres) are not so important. 1 Person changed from department on 2010/07/25 (gse). First we took a dimension lookup/update (see lookup1.png), put in the key-field, created a technical key and put DATUMVAN as date starting field. Then we put AFDELING on insert and the other fields on update (see fields.png). This transformation seems ok but when i look into the destination table i see the following :
    5 records and 2 for gse (see result1). But in DATUMVAN it doesn't show the real date the people started working, it shows 1900. So the real date is gone. So we cannot determine the real starting date of those people anymore...
    We then used an alternative start date (column) : DATUMVAN (see lookp2.png). After truncating the table we started the transformation.
    Now the results are ok (see result2.png). The only problem now is that everytime the transformation runs an extra record is added (see result3.png) i now have gse twice with version 2...
    i tried this in PDI 3.2.2 and 4.0.
    What is the right way to do this so that we can use the startdate but not get the extra records each time the transformation runs...
    thanks,
    Wavex
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2010
    Posts
    114

    Default

    I am not sure if it works but,
    When the column already exists, may be we can use an update step instead of the dimension/lookup step which might prevent the additional insertion of the row.

  3. #3
    Join Date
    Jul 2010
    Posts
    8

    Default

    I've only tested this with a sample database of 10 records. I don't want to validate the 'new' records with a daily dimension that changes 10 times a day....

    Thanks anyway for the reply !

    WaveX

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    This case stumbled over to me on the support channel.
    The gist of the advice I gave for this case is to avoid abusing the dimension record validity dates for other purposes.
    Create a separate column to capture the start at which the person started at the department. (+some other small advice)

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.