Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Feature or Bug in Combination Lookup?

  1. #1

    Default Feature or Bug in Combination Lookup?

    Hi Matt,

    I have noticed that the Combination Lookup step works really well when using all the fields in the record. However, I tried to use it for only portion of the fields and it drops the other fields not compared and enters null values only. Is this a feature or a bug?

    If it is a feature, how can I do what I am trying to achieve here.

    Cheers
    Jonathon

  2. #2
    Join Date
    May 2006
    Posts
    138

    Default RE: Feature or Bug in Combination Lookup?

    Hi,

    Unlike Dimension lookup update, Combination lookup update only does the business keys/surrogate id... put an update step behind the Combination lookup update to update the rest of the fields.

    Regards,
    Sven

  3. #3

    Default RE: Feature or Bug in Combination Lookup?

    Hi Sven,

    Thanks for the info. Is there a particular reason the Dimension lookup is different in this respect to the Combination lookup? What advantage is there in not updating the other fields like all the other steps do?

    Regards
    Jonathon

  4. #4
    Join Date
    May 2006
    Posts
    138

    Default RE: Feature or Bug in Combination Lookup?

    I don't know why it can't update non-key fields, probably just not implemented ... there's no technical reason why it couldn't be done. I put updating non-key fields on a Change request http://www.javaforge.com/proj/tracke...avigation=true (step 5) but also didn't get around to implementing it.

    For now... put an update step behind combination update with as key the technical key (you're sure the technical key exists after combination update/lookup).

    Regards,
    Sven

  5. #5

    Default RE: Feature or Bug in Combination Lookup?

    I started to do what you suggested, but came up with a problem. The fields have to be compared to return the id that I want. I cant update the table before hand (it is pointless as the values are all there anyway) and I cant do it after the Combination lookup as the field I need to update now has it's values zeroed out. Do I have to do a copy and pass the data, or is there a much nicer solution here?

  6. #6
    Join Date
    May 2006
    Posts
    138

    Default RE: Feature or Bug in Combination Lookup?

    I lost you somewhere but I think what you're trying to do doesn't make sense

    The way it works for Type 1 dimensions (warehousing in general) is that the technical id depends on the business key in your table. you want to update/insert a row in the dimension, you search the row with the incoming business key.

    If it exists you have found the technical key you need to use, else you create a new row/technical id and fill in the business key.

    There's no way you need other fields besides the business key to determine the technical key. If you do need them you either defined your business key wrong, or you have bigger semantical problems. If your technical key would depend on more than your business key there's no way you can maintain uniqueness of the business keys in the type 1 dimension.

    Regards,
    Sven

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

    Default RE: Feature or Bug in Combination Lookup?

    Hi Sven,

    I understand that you only need the business keys to determine the technical key. The problem is that if it does not match (and therefore does not return a technical key) it inserts the record, but only for the values of the business keys, NOT any other fields. I can't include all the fields as business keys, as they are time oriented (for a particular one) and they do not match between two different tables.

    Regards
    Jonathon

  8. #8
    Join Date
    May 2006
    Posts
    138

    Default RE: Feature or Bug in Combination Lookup?

    1 more shot at it.... combination update/lookup always returns a technical id. It will either find an existing one, or it will create a new row with a new technical key and the business key in your incoming row. In the case it inserts a new row it will fill the business key and the technical key, the rest of the columns will be null.

    So if you then put an update step behind it using the technical key as primary key it will work. In the incoming row of the update step you will find all original fields + your technical key. The update step will in your case find the newly inserted row based on the technical key and you can update the rest of your columns.

    If the above does not fit your situation you're trying to do something not supported by the Combination Update/Lookup.

    Sven

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

    Default RE: Feature or Bug in Combination Lookup?

    Just to clear this out before it runs away too far, combination lookup is a method for creating junk dimensions in a data warehouse.


    People find all kinds of other uses for it, like for adding new keys to customer files, etc.
    However, it creates junk dimensions.



    Here is an article on conformed and junk dimensions



    For example, I once needed to load bank statement records for a bank in a fact table. The fact contained keys to date, customer, account, etc. However, the statements also have a number of fields such as type of transaction, description, message, structured messages, etc. That have no place in the fact table. To increase performance, you should put all those flags and text-fields out of the fact and into a junk dimension. Using combination lookup. Ralph Kimball also has some articles on this stuff.



    If you choose NOT to do this and leave the dimension entries in the fact, they are called degenerate dimensions.



    Someone should make a list of definitions or something. Anyone? Anyone? Bueler?



    Matt

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

    Default RE: Feature or Bug in Combination Lookup?

    Hi Sven,

    Yes, you are right. It seems there is a problem with spoon on my machine where it is saying that the fields are not in the incoming stream? The error shows that it was unable to query the database, but it all worked with any changes before? I am getting a NullPointerException coming up as well and when I try and preview the database, it is returning a number of "error while connection messages" and refers to the MySQL driver and then the following:

    ...Unable to find KETTLE Variables for thread [ModalContext]...

    I tried an update to the latest SVN and it still causes the same message?

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.