Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: "Sequential execution" ?

  1. #1

    Default "Sequential execution" ?

    Hello,

    basically, I have a transformation which pulls out records from my OLTP database and pumps it into a table in my OLAP database. So I have a table input and a table output component with a field mapping in place. What I want to have is that after the table output has been finished a SQL statement (a stored procedure call) should be executed to update data in my fact table where the logic for that is in the stored procedure. But this doesn't work as expected from a flow POV. Either the stored procedure is called while the table output step hasn't finished yet or it is a transaction thingy, I don't know. Data isn't updated as expected, but IT IS, when calling the transformation again (without new records to move from OLTP to OLAP), so the SP does it's job but not in the context when records are purged from the OLTP into the OLAP database.

    So, is this a parallel execution or transaction thingy, which won't let me SP do the job?

    Any ideas?


    Thanks,
    Thomas

  2. #2
    Join Date
    Feb 2009
    Posts
    296

    Default

    Transformations do not work sequentially. Think of a river - the rows flow down like water.

    You're putting a rock in that river and expect it to be hit ONLY by the last row. That's not what's happening - it's being hit again and again - once per row of data.

    What you WANT to use is a job. The job can call a transaction and AFTER that transaction is done it can run another transaction.

    Hope that helps!
    Fabian,
    doing ETL with his hands bound on his back

  3. #3

    Default

    Yes, that helped a lot!

    I already have a job calling several transformations, so moving the SQL execute into its own transformation and calling the transformation in my job after the loading fact table transformation now gives me the expected result.

    Thanks a lot!

    Thomas

  4. #4

    Default

    You could also put a "Blocking Step" in front of your stored procedure call. This would wait until all rows have been stored in the target table and only sends the last row onward to your stored procedure call.

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.