16 Temmuz 2013 Salı

ODI Knowledge Module for UNION , UNION_ALL, INTERSECT

Hi all

In this entry , i will explain how to make UNION , UNION_ALL, INTERSECT style operations without making extra insertion or temp table creation.


 *Case : What i want to do is to gather all employee information which currently holds in different tables.
 ( Tested enironment : Oracle 10g database , ODI 11g )

 At first we will create a table which holds the SQLs for final statement.

CREATE TABLE REPORTING.INTERFACE_QUERY
 (

INTERFACE_NAME VARCHAR2(2000 BYTE),
EXE_SCRIPT LONG

 )


 * This table contains two columns ,one interface name which holds the name of the interface and  EXE_SCRIPT which holds the SQL created by ODI interface.

*What we gonna do is  to extract source tables called EMPLOYEE_PRE , EMPLOYEE_VICE_PRE, EMPLOYEE_PRG  and fill them into a table called EMPLOYEE_ALL


*Now creating  interfaces which are their target tables are dummy ( yellow interfaces )


















* In this interface we will add EMPLOYEES who are currently presidents and with the help of  IKM knowledge module called  IKM SQL Control Append-SubQuery

Link for Download
http://rapidshare.com/files/815556971/KM_IKM_SQL_Control_Append_SubQuery.xml

SQL statement which is created by the interface will be inserted into REPORTING.INTERFACE_QUERY table.Same operation will be done for vice president and programmer tables .

As a result we have following data in our reference table ( Interface_Query ) and we have 3 temp interface.















*Now what we have to do create a main interface which gathers all sql statements above and  load data into our target table.

*I will prepare the allignment of the columns for my target table which has to be matched with my subquery columns.

Before:












After:













Then i created a new interface called Interface_main. Mapping looked like this









* I drag 3 temp interfaces as a source and in order to maintain integrity  connecting them with dummy join ( department_name )  and adding my target table with mappings ( those mapping added as dummy too )

* now i am assigning the knowledge module  called  IKM SQL Control Append-Union-Minus-Intersect-Union All

Link for Download
http://rapidshare.com/files/1050531261/KM_IKM_SQL_Control_Append_Union_Intersect_Union_All.xml

*Parameters for this Knowledge Module look like this

















*Basically  knowledge module  gathers SQL statements from REPORTING.INTERFACE_QUERY  and depends on the parameter we choose, it makes UNION , UNION ALL or INTERSECT and creates a resultset which load into our target table. As a result following sql statement has been created and executed.



















*


Hiç yorum yok:

Yorum Gönder