Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Combination lookup/update when there are null values

  1. #1

    Question Combination lookup/update when there are null values

    I have a few dimensions that have to support null values. For example, the bug closed by user field is going to be null until the bug has been closed. The Combination lookup/update wants to update a null value into the dimension table in that case, and that fails. I don't want a surrogate key (besides for the closed date data key a surrogate key is somewhat awkward)

    Right now I need 6 nodes each time that happens
    Filter - branch on null value
    Branch 1 - Lookup/Update
    Branch 1 - Select/Rename values (the stream fields have to be in the same order to merge them)
    Branch 2 - Select value: Remove the null lookup field
    Branch 2 - Add constants: Add a null dimension key
    Branch 2 - Select/Rename values (as above)

    Is there an easier or better pattern to this?


  2. #2
    Join Date
    Jul 2009


    Null values are a problem for dimension keys. I don't have much experience with the Combination Lookup/Update step, but the Dimension Lookup/Update step, which is designed for a single dimension, creates a special row where the surrogate key value is zero, and all of the data columns are null. That works well for date dimensions where a date is null, because it just uses the surrogate key of zero.

    I don't know your particular needs, but date dimensions are nice for a lot of analytic tools. You can snowflake the dimension to include rollups by week, month/quarter/year, day of week, etc.

  3. #3
    Join Date
    Apr 2008


    Perhaps you could leverage the "If Null..." step to populate empty values that your DB would like.

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.