Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Database lookup caching in subtransformations

  1. #1

    Default Database lookup caching in subtransformations

    I have two fact tables. Both fact tables share the same set of dimensions (dim_date, dim_time, dim_customer, et al). For each fact table, I have a transformation. Both fact-generating transformations go through the identical dimension table lookup steps. For each dimension lookup, I have caching and load entire table enabled. This reduces the ETL time from 4 mins to 2 seconds. Since the dimension tables are short, this is practical.

    Now to reduce redundancy, I want to isolate the dimension lookup steps in a separate subtransformation that both fact-generating transformations can share. My question is, if I place the dimension table lookup steps in a subtransformation and enable caching, does a table reload happen every time the substransformation is called, or will the dimension content be held in memory until the ETL is complete?

    Thank you,


  2. #2


    Speaking in another programming language - subtransformations are like pre-processor makros which help you keeping your code clean (reduce redundancy). If you have a DB-Lookup in a subtransformation and you use this sub.transformation 4 times it is like having 4 DB-Lookup steps. They do not share resources (memory, connection, etc.), regardless if you use the sub-transformation in one transformation or if you use the same sub.transformation in different transformations.

  3. #3
    Join Date
    Sep 2011


    Usually I solve this by writing the shared results to a 'data cube' using the Serialize to / from file steps, so you can create the shared results first, and then use and re-use it in the rest of the processing. Perhaps this could work for you too?

Tags for this Thread

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.