Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Dynamic Transformations and Jobs : use-cases

  1. #1
    Matt Casters Guest

    Default Dynamic Transformations and Jobs : use-cases

    Dear Kettle devs,

    Over the weekend I received a question to deliver use-cases behind creating
    transformations and jobs dynamically or automatically, as opposed to
    creating them statically in Spoon.

    Well obviously the number one case is the situation where you need to move
    hundreds of database tables from one database to another. Let's say it's a
    data staging exercise. Each table needs fields renamed and rejects from the
    table output step need to be caught and put in a generic table somewhere so
    we can put a small report on it.

    This is the sort of thing that, even though it is really simple, can't be
    solved with a classical transformation/job construction.

    Now obviously a lot of folks wouldn't even think of building a generic
    solution for this kind of situation. However, creating hundreds of
    transformations by hand (each with the same logging configured and the same
    input, select, output and rejects steps) is tedious and error prone work
    that nobody likes to do. The loss of time in creating and maintaining this
    set of transformations is also staggering.

    The question to this list is then: do you know of other situations? Do you
    perhaps want to populate your slowly changing dimensions straight from
    metadata stored in a spreadsheet? Things like that?

    Let us know, we're interested!

    Cheers,
    Matt
    --
    Matt Casters <mcasters (AT) pentaho (DOT) org>
    Chief Data Integration, Kettle founder, Author of Pentaho Kettle
    Solutions<http://www.amazon.com/Pentaho-Kettle-Solutions-Building-Integration/dp/0470635177>
    (Wiley <http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0470635177.html>)
    Fonteinstraat 70, 9400 OKEGEM - Belgium - Cell : +32 486 97 29 37
    Pentaho : The Commercial Open Source Alternative for Business Intelligence

    --
    You received this message because you are subscribed to the Google Groups "kettle-developers" group.
    To post to this group, send email to kettle-developers (AT) googlegroups (DOT) com.
    To unsubscribe from this group, send email to kettle-developers+unsubscribe (AT) g...oups (DOT) com.
    For more options, visit this group at http://groups.google.com/group/kettle-developers?hl=en.

  2. #2
    Roland Bouman Guest

    Default Re: Dynamic Transformations and Jobs : use-cases

    Variations of this case I have encountered (and automated the quick
    and dirty way)

    - dump a table to excel (allowing simple filters)
    - periodical (daily) calculation of changes between a database and a
    historized version of the same database (identical schema but with
    additional timestamp, change_type (IUD), global id columns). changes
    are to be logged to file in csv format and applied to the history
    database.

    On Mon, Feb 28, 2011 at 9:04 AM, Matt Casters <mcasters (AT) pentaho (DOT) org> wrote:[color=blue]
    > Dear Kettle devs,
    >
    > Over the weekend I received a question to deliver use-cases behind creating
    > transformations and jobs dynamically or automatically, as opposed to
    > creating them statically in Spoon.
    >
    > Well obviously the number one case is the situation where you need to move
    > hundreds of database tables from one database to another.

  3. #3
    Christopher Lavigne Guest

    Default RE: Dynamic Transformations and Jobs : use-cases

    I had the need to create a transformation at run-time that would denormalize
    the data from a standard transaction fact table into a flat fact table. The
    columns in the flattened fact were not known in advance - purely driven by
    the data in the transaction fact. I did this by first developing a base
    transformation, then breaking the .ktr file down into dynamic and static
    pieces. I then created a separate transformation that creates the dynamic
    pieces (e.g. row denormalizer, insert/update steps) in separate files
    (includes of course the proper xml tags).



    So the run-time process is a single job that:



    1) Executes a transformation that builds dynamically defined step
    metadata files based on the data in the transactional fact.

    2) Executes a script job-entry to concatenate the static files and
    dynamic files created in Step 1 - output is a single .ktr file.

    3) Executes the .ktr file created in Step 2.



    Regards,



    Chris



    _____

    From: kettle-developers (AT) googlegroups (DOT) com
    [mailto:kettle-developers (AT) googlegroups (DOT) com] On Behalf Of Matt Casters
    Sent: Monday, February 28, 2011 3:05 AM
    To: Kettle Developers mailing list
    Subject: Dynamic Transformations and Jobs : use-cases



    Dear Kettle devs,

    Over the weekend I received a question to deliver use-cases behind creating
    transformations and jobs dynamically or automatically, as opposed to
    creating them statically in Spoon.

    Well obviously the number one case is the situation where you need to move
    hundreds of database tables from one database to another. Let's say it's a
    data staging exercise. Each table needs fields renamed and rejects from the
    table output step need to be caught and put in a generic table somewhere so
    we can put a small report on it.

    This is the sort of thing that, even though it is really simple, can't be
    solved with a classical transformation/job construction.

    Now obviously a lot of folks wouldn't even think of building a generic
    solution for this kind of situation. However, creating hundreds of
    transformations by hand (each with the same logging configured and the same
    input, select, output and rejects steps) is tedious and error prone work
    that nobody likes to do. The loss of time in creating and maintaining this
    set of transformations is also staggering.

    The question to this list is then: do you know of other situations? Do you
    perhaps want to populate your slowly changing dimensions straight from
    metadata stored in a spreadsheet? Things like that?

    Let us know, we're interested!

    Cheers,
    Matt
    --
    Matt Casters <mcasters (AT) pentaho (DOT) org>
    Chief Data Integration, Kettle founder, Author of Pentaho Kettle Solutions
    <http://www.amazon.com/Pentaho-Kettle...ration/dp/0470
    635177> (Wiley
    <http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0470635177.html> )
    Fonteinstraat 70, 9400 OKEGEM - Belgium - Cell : +32 486 97 29 37
    Pentaho : The Commercial Open Source Alternative for Business Intelligence





    --
    You received this message because you are subscribed to the Google Groups
    "kettle-developers" group.
    To post to this group, send email to kettle-developers (AT) googlegroups (DOT) com.
    To unsubscribe from this group, send email to
    kettle-developers+unsubscribe (AT) g...oups (DOT) com.
    For more options, visit this group at
    http://groups.google.com/group/kettle-developers?hl=en.

    --
    You received this message because you are subscribed to the Google Groups "kettle-developers" group.
    To post to this group, send email to kettle-developers (AT) googlegroups (DOT) com.
    To unsubscribe from this group, send email to kettle-developers+unsubscribe (AT) g...oups (DOT) com.
    For more options, visit this group at http://groups.google.com/group/kettle-developers?hl=en.

  4. #4
    Peter Hunsberger Guest

    Default Re: Dynamic Transformations and Jobs : use-cases

    We've had requirements similar to Christophers, but we had built a
    custom crafted solution if Kettle had been around at the time (maybe
    it was, and we just didn't know about it), and if it could have done
    metadata driven transforms it would have been a far preferable
    solution.

    Peter Hunsberger

    On Mon, Feb 28, 2011 at 8:17 AM, Christopher Lavigne
    <chris_lavigne (AT) breadboardbi (DOT) com> wrote:[color=blue]
    > I had the need to create a transformation at run-time that would denormalize
    > the data from a standard transaction fact table into a flat fact table.

  5. #5
    Mark Hall Guest

    Default Re: Dynamic Transformations and Jobs : use-cases

    A process such as this is commonly needed for converting transactional
    data into a form that is typically acceptable to data mining
    market-basket analysis schemes. It would be nice to have a simple and
    elegant way to do this in Kettle.

    Cheers,
    Mark.

    On Tue, Mar 1, 2011 at 3:17 AM, Christopher Lavigne
    <chris_lavigne (AT) breadboardbi (DOT) com> wrote:[color=blue]
    > I had the need to create a transformation at run-time that would denormalize
    > the data from a standard transaction fact table into a flat fact table.

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.