Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Importing Denormalized Data

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

    Default Importing Denormalized Data

    I'm creating a POC to import a delimited file from a vendor into my database. The data I need to import is denormalized into 1 delimited file and I need to normalize it into mutliple tables. For example, the input data looks like this:

    bowls, cereal
    bowls, soup
    silverware, forks
    silverware, spoons
    silverware, knives

    I would like to import this into two tables that look like this:
    dish_type
    * dish_type_id
    - dish_type_name

    dish
    * dish_id
    - dish_type_id
    - dish_name

    So the process is this:
    1. Read Record
    2. Lookup dish_type_id by dish_type_name
    3. If not exists, add new dish_type
    4. Add new dish

    I've played around with a number of scenarios (Lookups, Insert/Update, Filters), but can't seem to figure it out. If I do the insert/update, how do I get the id back out? Do I need to do a subsequent lookup? How do I access the fields of the current row from the input file after I've done the insert/update to the dish_type table to do the insert/update on the dish table?

    If anyone has a similar example that they can share, I'd appreciate it.

    Thanks in advance,

    Chris

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

    Default RE: Importing Denormalized Data

    Hi Chris,

    We had this question before on the forum a couple of entries back: you can use the Combination Lookup step. This will store unique occurences of dish_type_name and add an ID.

    HTH,

    Matt

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

    Default RE: Importing Denormalized Data

    Thanks Matt -- that's what I was looking for. I'm actually importing 5.8 million rows into a 4 table hierarchy. Does the combination lookup do any caching of lookup values?

    Thanks again for your help.

    -Chris

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

    Default RE: Importing Denormalized Data

    No, not yet. In this case we could implement it.
    If you really need it, file a feature request for it under the trackers.

    Thanks,

    Matt

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.