Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Default values for "0" (id) dimension row

  1. #1
    OliverFox Guest

    Default Default values for "0" (id) dimension row

    Is there a way to set the fields for the automatically generated "null" value row for a dimension, which was generated by the dimensional insert lookup/update step?
    It seems to me that there is no way to specify the field values for the row which has a "technical key field" value of 0. They are all null.
    Of course I could do a SQL-step afterwards doing the update of that row or I could define defaults in the DB table definition, but these solutions are clumsy.
    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    30

    Default Need a way to 'inject' the 'if field value is null' transform

    The described behaviour still occurs; the 'if field value is null' is a great way of handling all possible NULL values at once, but the dimension update step still generates a first id of 0 with all columns set to NULL. It woud be great if the dimension update would be aware of the preceding 'if field value is null' transform

  3. #3
    Join Date
    May 2006
    Posts
    4,882

    Default

    There's not really a way to make it aware ... assuming you're not every day emptying/reloading your tables, insert the 0 row yourself and you put the data in that you want yourself.

    If you do empty and refill daily ... you can generate a 0-row upfront and insert it with update/insert e.g.

    Regards,
    Sven

  4. #4
    pstoellberger Guest

    Default

    I just stumbled over this issue today as well.
    Why is this row being inserted anyway?
    If i have NOT NULL columns this will break the transformation
    is this really the intended behaviour of that step?
    "insert into scd_table (tk,version) values (0,1)" << thats hardcoded in the source of PDI and just doesn't make any sense to me

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

    Default

    Code:
    is this really the intended behaviour of that step?
    Of-course it is. The number of errors occurring because a DWH admin forgets to insert this row FAR outweighs the burden of inserting the row itself. How hard is it to insert a row yourself anyway?

    And by the way, why would it be wrong to have null value attributes in a dimension? NULL is an attribute value like any other and is well supported by PDI.

    Simply ask yourself what looks cleaner for an unknown value: null or "<unknown>". Once you have answered that question, act accordingly by either not doing anything or by inserting the unknown row yourself.

    Matt

  6. #6
    pstoellberger Guest

    Default

    well, seems like i didn't understand SCD correctly then.
    i understand i need this row, but does that mean i'm not allowed to have NOT NULL columns in a SCD? am i supposed to leave all columns null in this row?

    if this is how its supposed to be, i have no problems inserting this row manually..

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

    Default

    Not null columns are fine, but you need to insert the "unknown" row yourself in that case. All I'm saying is that Kettle has no problem with null dimension attributes.

  8. #8
    Join Date
    Jun 2009
    Posts
    6

    Default

    hey guys

    I do have a question concerning this issue: what should we do with a numeric not null field in this case ?

    What should we put as value in that 'famous" 1st row ?

    What's this "null" row meant to be ?
    Last edited by marouen; 07-09-2009 at 02:06 AM.

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

    Default

    Whenever there is a natural key in your fact data that can't be mapped to a dimension (Example: an order line item for a product that can't be found in the warehouse) we link to this record (typically technical key 0 or as low as possible).

    If we wouldn't do that you would get reports with varying numbers and lines depending on with which dimension you join. That's a BIG no-no in BI.

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.