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



1 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