Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Help~!

  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Default Help~!

    Hi~all,I have a db table bm,the fields:bm01,bm_bm01(top level code),bm02,bm06:
    table bm:

    then load the data to dim-bm table:



    how to achieve it use The Kettle,give me the steps pls! Thanks!
    Last edited by tomp; 01-16-2010 at 12:46 AM.

  2. #2
    Join Date
    Feb 2009
    Posts
    296

    Default

    Looks like you'd need the de-normalizer. But without more information on the rules you won't get a lot of help.
    The forum lives of good questions and voluntary answers if someone is interested in the question - so maybe you want to rephrase your question and post more information. Might turn out you have an interesting problem there.
    Fabian,
    doing ETL with his hands bound on his back

  3. #3
    Join Date
    Mar 2009
    Posts
    30

    Default Brush up your SQL...

    If the data is really as clean as you describe, you can use

    SELECT l1.bm01 as gzz, l1.bm02 as gzz_name,
    l2.bm02 as bm, l3.bm02 as fgs, l1.bm06 as gs
    FROM bm_src l1
    inner join bm_src l2 on l1.bm_bm01 = l2.bm01
    inner join bm_src l3 on l2.bm_bm01 = l3.bm01
    where length(l1.bm01) = 8
    order by 1

    which will give you the desired output excluding the ID. You can use the 'Add sequence' step for the ID or use a sequence field in your target table. If the hierarchy is ragged (e.g. some trees are only 2 levels deep, or a top level is missing) it gets messy and you need e.g. outer joins if you want to solve it in SQL or use lookups in Kettle and handle the exceptions there.

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.