Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: F1 F1 F1::: Dimension Update Lookup

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default F1 F1 F1::: Dimension Update Lookup

    Hello Matt,
    Having trouble using Dimention Update Lookup step for ETL.
    Problem is the Date Range Start field and Date End fields.


    Lets say I have a Customer dimention that has two date columns
    Date Created and Date Deleted.
    Now lets say I want to answer the questions:
    How long has "XYZ" been our customer?
    Is "XYZ" a valid / active customer?
    Using Dimension Lookup Update, is there any way I can
    answer both these questions using the two date columns mentioned above.
    In other words can I use the date columns from Table Input to populate these fields.

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

    Default RE: F1 F1 F1::: Dimension Update Lookup

    Hi Anonymous,

    This is not possible by design. That is because it is not the primary use of the from-to date range.
    The from-to date range is saying something about the validity of that *version* of the customer record. It is not per-se the range on which the customer was with the company. (created/deleted)

    For example you might have a creation date of 2001/01/01.
    That doesn't mean that you can't have multiple versions of the record in cases where you want to capture changes. (address changes for example).
    All the versions of this record will have the same creation date. That is because you want to answer business questions like:

    What's the sales in February of customers created in January? Even if the customers properties change, you still want to know about the fact that the customer was created in January.

    I have similar reservations about the end-date being used for Date Deleted although the difference is more subtle there.
    For example, in cases of re-activated customers, descriptions and type changes that trigger the creation of extra records etc.

    So, to capture my opinion on this: Creation date is out of the question, deletion date: maybe we can make this an optional feature... maybe.
    It is possible to convince me... sometimes ;-) Perhaps if you do the work of filing for a feature request in the trackers ;-))

    Hope this clarifies this,

    All the best,
    Matt

  3. #3
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: F1 F1 F1::: Dimension Update Lookup

    Thanks Matt,

    We have decided to go with different dates for representing creation and validity.
    In other words we will have a Created_Date and also will have the Valid_From and Valid_To.

    Thanks for the reply though, It validates the decision taken.

    As far as giving you more work to do, maybe

    Thanks,
    Carl Daver

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.