DATA SETS

DataSets

Using UNION ,INTERSECT ,UNION ALL MINUS

STEP 1 : Creating and Reverse Engineering Source

1. Connect to Source schema (SCOTT)
2. Execute below scripts

create table EMP10 AS select * from emp where deptno=10 create table EMP20 AS select * from emp where deptno=20

3. We can use EMP table of SCOTT schema as source
4. Reverse engineer EMP10 and EMP20  table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Connect to Target Schema (TDBU)

create table EMP1020 AS select * from scott.emp where 1=2

2. Reverse engineer into TARGET MODEL

STEP 3 : Creating Interface/Mapping

1. Create interface with the name of m_Data_Sets
2. Click on mapping tab → Drag and drop source (EMP10)and target (EMP1020) in respective locations

3. Click on add/Remove Data Set → Click on Add new dataset
4. change name as EMP20 → select  operator as UNION → Click on Close

5. Drag and drop EMP20 source into source work area
6. 
7. Click on Flow tab
8. Select Target and select IKM SQL CONTROL APPEND
9. Select FLOW_CONTROL = False

10. Click on SAVE
11. Click on RUN
12. Observe output
13. 

Sorting in ODI 11G

Sorting in ODI 11G

1. It is equal to ORDER BY clause in SQL
2. it is available as a component in ODI 12 C

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as Source

STEP 2 : Creating and Reverse Engineering Target

1. Duplicate EMP data store and rename as TGT_EMP_SORT
2. Drag and drop into Target Model

STEP 3 : Change KM

1. Duplicate the existing KM IKM SQL Control Append
2. Rename as IKM SQL CONTROL APPEND SORT
3. Open this KM → Select Details → Double Click on Insert New Rows → end of the code add below code

<%=odiRef.getOption("ORDER_BY")%>

4. Right Click on IKM SQL CONTROL APPEND → Select New Option

5. Provide name and type  as shown → save and close the KM

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of m_SORT_IN_ODI11G
2. Click on mapping tab → Drag and drop source EMP  and target  TGT_EMP_SORT in respective locations

3. Click on Flow tab
4. Select Target and select IKM SQL Control Append Sort → Change options
1. ORDER_BY : ORDER BY DEPTNO ,SAL
2. FLOW_CONTROL : False
3. Truncate :True
4. Create Target Table :True

5. Click on SAVE
6. Click on RUN → Observe Code

7. Observe Target Table data

IKM Oracle Multi Table Insert

IKM Oracle Multi Table Insert

1. Integrates data from one source into one to many Oracle target tables in append mode
2. This IKM must be used in integration interfaces that are sequenced in a Package
3. The first interface of the Package must have a temporary target and the KM option DEFINE_QUERY set to YES.
4. This first interface defines the structure of the SELECT clause of the multi-table insert statement (that is the source flow).
5. Subsequent integration interfaces must source from this temporary data store and have the KM option IS_TARGET_TABLE set to YES.
6. The last interface of the Package must have the KM option EXECUTE set to YES in order to run the multi-table insert statement.

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as source
2. Reverse engineer EMP table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Create Four target tables in Target Schema using below script

Create table Multi_Table_Insert_TEMP_EMP as select * from scott.emp where 1=2; Create table Multi_Table_Insert_EMP10 as select * from scott.emp where 1=2; Create table Multi_Table_Insert_EMP20 as select * from scott.emp where 1=2; Create table Multi_Table_Insert_EMP30 as select * from scott.emp where 1=2;

2. Reverse engineer into TARGET MODEL

STEP 3 : Creating First Interface

1. Create interface with the name of INT_Multi_Table_Insert_TEMP_EMP
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_TEMP_EMP) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = True
6. Select IS_TARGET_TABLE= False

STEP 4 : Creating Second Interface

1. Create interface with the name of INT_Multi_Table_Insert_EMP10
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_EMP10) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = False
6. Select IS_TARGET_TABLE= True

STEP 5 : Creating Third Interface

1. Create interface with the name of INT_Multi_Table_Insert_EMP20
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_EMP20) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = False
6. Select IS_TARGET_TABLE= True

STEP 6 : Creating Fourth Interface

1. Create interface with the name of INT_Multi_Table_Insert_EMP30
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_EMP30) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = False
6. Select IS_TARGET_TABLE= True
7. Select Execute = True

STEP 7 : Create Package

1. Create package with the name of pkg_Multi_Table_Insert
2. Click on diagram tab → drag and drop below interfaces and  connect as shown
1. INT_Multi_Table_Insert_TEMP_EMP
2. INT_Multi_Table_Insert_EMP10
3. INT_Multi_Table_Insert_EMP20
4. INT_Multi_Table_Insert_EMP30

3. Run the package → observe output in three tables