Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Dependencies between relations and foreign keys

  1. #1
    Join Date
    May 2008
    Posts
    2

    Default Dependencies between relations and foreign keys

    Hello,

    i have a serious problem with relations depending on other relations and parallel transformations.

    Consider the following example schema :

    Person ( ID (number), first name, last name ) primary key (ID) )

    Phones (ID number), phone) primary key (ID) Foreign key on person.ID )


    The ID is being generated in an oracle-database using a sequence.


    I am using the following PDI Transformation steps :
    add sequence - > select values (2X) -> database output (2X)

    (2x = for each relation)

    The problem is, that pdi has to write first into person and then into phones.

    How can i guarantee this?

    Can i model a dependency between these steps or sth else?

    thanks in advance,
    Stefan

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

    Default

    One reliable solution would be to land the data in between (and 2 transformations instead of 1). What would possibly also work is a "blocking step" if your commit size is small enough.

    Regards,
    Sven

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

    Default

    Or use the "Unique connections" option to make all the changes in a single transaction.

    Matt

  4. #4
    Join Date
    May 2008
    Posts
    2

    Default

    Thanks for both replies.

    I already tried all these three solutions.

    The solution using unique connections does not work, because
    kettle does not know in which relation it has to write first.

    The solution with two transformations (first in person, then in phone) doesn't work, because kettle has to use the same id.
    This is being generated in the first transformation. I don't know whether the second transdormation can use these id's.

    I tried the approach with a blocking step, but i'm not sure where to put it. My opinion was to put it between TableOutputPerson and SelectValuesPhone, but this does not work.

    Is there any other way to do this?

    regards,

    Stefan

  5. #5

    Default

    Maybe u can use a temp text file to remember the sequence between two transformations.
    Jason chu
    Beijing Aofei BI Consulting.
    Web: http://www.pentahochina.com
    Email: jianjunchu@gmail.com

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.