Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Dimension lookup/update preventing an update

  1. #1

    Default Dimension lookup/update preventing an update

    Hi,

    I am having a strange problem and I am suspecting that a component 'Dimension lookup/update' is locking rows for update in the last component. Transformation consist of following elements:

    Name:  000357.jpg
Views: 34
Size:  9.3 KB

    When 'Update ETL_ID' is moved to another transformation everything works fine.

    ps: last step in transformation was added because I have not found a solution how to insert an etl_id into the dimension lookup component.

    Thank you

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Depending on the Commit size configured in the Dimension-Lookup/Update step I sure would expect lock conflicts with your design.
    But I wonder what's exactly happening in that SQL Script.
    Do you really need it to execute for each row?
    So long, and thanks for all the fish.

  3. #3

    Default

    Probably this is the issue. The last component is a simple update that sets the batch_no for rows that were inserted in the last run. If there would be a way how to setup current batch_no inside "Dimension lookup/update" this step could be left out.

    Another change that I want to track are deleted records from the source. I have done this operation manually with a new sql step but with an overhead of doing another read from the source and target table. Would you maybe suggest any other design pattern how to effectively track I/U/Deletes?

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by mertez View Post
    If there would be a way how to setup current batch_no inside "Dimension lookup/update" this step could be left out.
    I never used that DWH step myself, but from a quick look I would think that if you bring the batch number as a field you should be able to write it to the table, too.

    Quote Originally Posted by mertez View Post
    Another change that I want to track are deleted records from the source.
    Are we really discussing data warehousing and a dimension table? My understanding is that you seldom delete rows from a dimension, because there always could be a fact referencing it.
    Or are you just using the step for convenience and there's no DWH in sight, just plain old table synchronization with a twist (batch number)?
    Maybe you would benefit from the Merge-Rows step in that case.
    I frequently use Merge-Join to get a full outer join to keep all fields from both streams for logging purposes.
    So long, and thanks for all the fish.

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.