15 Haziran 2014 Pazar

ODI 12c Knowledge Module for Hierarchical Queries

Hi

In this entry , I will explain how to implement hierarchical queries over ODI via Knowledge Module.

Case Study:
What we  have  a table which contains employee information and for each employee there is a manager defined.Basic Rule  a manager can be an employee  which  means  while A is the manager of B , C is also can be the manager of  A  etc. Basically in oracle we can use CONNECT BY to show information about hierarchical structures and in this example we will create a KM that can implement a standart CONNECT BY query.

Query as follows:

Our Source table: ( EMPLOYEE)
Target  Table: (EMPLOYEE_HR)

Step1:
First of all we gonna create standart mapping as follows:


Filter part contains following sql:
level <= 3 AND department_id = 80

Step2:
For Execution we choose our new KM called  IKM_SQL_Control_Hierarchical


Everthing is almost as same as standart IKM SQL Control Append.We just add specific options for CONNECT BY and START WITH scripts.

KM Options as follows:

OPTIONS:
IS_NOCYCLE:  IF NULL KM use 'CONNECT BY' IF 'YES' KM USE 'CONNECT BY NOCYCLE '

START_WITH_OPTION:  IF NULL KM does not use 'START WITH' script Otherwise KM use
the value which is typed into the textbox.

CONNECT_OPTION:
KM use the value which is typed into the textbox.

When Execution completed output look like this:

For more detail you can download the Knowledge Module link below:

http://sharesend.com/ynq43t7q



30 yorum:

  1. Hi,
    link http://sharesend.com/ynq43t7q seems to be down, can you send me the IKM mentioned in your post ?
    ( http://ozukun.blogspot.hr/2014/06/odi-12c-knowledge-module-for.html )
    to address kf.ostalo@gmail.com ?
    thnx

    YanıtlaSil
  2. Hi

    Sorry for late reply , been a while to post this entry.I sent you IKM with e-mail hope it helps.

    YanıtlaSil
    Yanıtlar
    1. Hello Ozgur,
      like Kresimir, can you send me this ikm in my email: lucasdataintegrator@gmail.com

      Thanks!

      Sil
    2. Hi Lucas

      I sent your e-mail hope it helps.

      Thanks

      Sil
  3. hy, can you send me the km? thanks.
    fernandohein@gmail.com

    YanıtlaSil
  4. Hi Fernando

    I sent your e-mail hope it helps.

    Thanks

    YanıtlaSil
  5. Hi!
    Can you please send me the km also?

    E-mail is perkovicboris@yahoo.com

    Thank you!

    YanıtlaSil
  6. Hi, Can you please send me KM to my email vinayaseshu@gmail.com?

    Thanks,
    Vinay

    YanıtlaSil
  7. Hi - great article - exactly what I am trying to do. Any chance you could email me the KM - stephenhrunnalls@gmail.com?

    Thanks

    YanıtlaSil
  8. Hi Stephen

    Sorry for late reply , i sent you via e-mail.Hope it helps.

    Thanks again.

    YanıtlaSil
  9. Hi Yanitla,
    COuld you please send the KM on sachchidanandsngh73@gmail.com

    YanıtlaSil
    Yanıtlar
    1. Hi

      Sorry for late reply , i sent you via e-mail.Hope it helps.

      Thanks again.

      Sil
  10. Hi Yanıtlar,

    could you please send the KM on amurillr@gmail.com?

    Thanks in advance.

    YanıtlaSil
    Yanıtlar
    1. Hi

      Sorry just read your comment.I send KM tp your email.Hope it helps.

      Thanks

      Sil
  11. can you please email KM to gopal.rk4@gmail.com

    YanıtlaSil
  12. hi
    please send me link

    YanıtlaSil
  13. hi

    please send me link of km
    thanks

    YanıtlaSil
  14. hi

    can you please mail the km for hierarchy.(lalith9326@gmail.com)

    YanıtlaSil
  15. Hi
    Please send the KM to my email, suguselva24@gmail.com

    YanıtlaSil
  16. hi,
    Please send the KM to my email. ghosh.pritha1993@gmail.com

    YanıtlaSil
  17. Hi,

    Could you please send me the KM? Thanks in advance.

    YanıtlaSil
  18. hi, could you send me the KM please? My e-mail adress is ulkudmn2@gmail.com

    YanıtlaSil
  19. Hi . could you please send me the KM? id :kdivyadiya@gmail.com

    YanıtlaSil
  20. Hi,
    Can you please send me this KM?
    ID: ghosh.pritha1993@gmail.com

    YanıtlaSil
  21. Hi
    Can you please send me this KM?
    ID: ghosh.pritha1993@gmail.com

    YanıtlaSil