How to delete duplicate records and retain one record among duplicates?

Scenario:
If we are having 5 duplicate records then you have to delete 4 records and retain 1 record.

Process:
Execute Following script:

CREATE TABLE TestTable(
Code Varchar2(1) ,
n number(2)
)
/
Insert into TestTable (CODE,N) values ('A',1);
Insert into TestTable (CODE,N) values ('A',2);
Insert into TestTable (CODE,N) values ('A',2);
Insert into TestTable (CODE,N) values ('B',1);
Insert into TestTable (CODE,N) values ('B',2);
Insert into TestTable (CODE,N) values ('B',3);
Insert into TestTable (CODE,N) values ('C',1);
Insert into TestTable (CODE,N) values ('C',1);
Insert into TestTable (CODE,N) values ('C',2);
/

Query to delete duplicate records and retain one record:

DELETE FROM TESTTABLE WHERE ROWID IN (SELECT ROWID FROM (
SELECT RN,ROWID FROM (
SELECT CODE,N,ROW_NUMBER() OVER(PARTITION BY CODE,N ORDER BY CODE DESC) RN
FROM TESTTABLE) WHERE RN>1));
/

Slowly changing Dimension in ODI

Process

1.     Create source and target data stores

2.     On Target datastore change OLAP Type to Slowly Changing dimension

3.     Expand target datastore and open all columns à under description select appropriate you need to perform (for e.g. add row on change, Overwrite on change ….)

[Note: Natural Key is must which column is unique as per source]

4.     Import IKM Oracle slowly changing dimension Knowledge module

5.     Create Interface in drag and drop source datastore from model into source side of editor and target datastore from target side of editor

For Example:

1.     Create Database tables as follows:

Source: CREATE TABLE PRODUCT( PID NUMBER(3,0), NAME VARCHAR2(10),             COST NUMBER(10,2))

Target: CREATE TABLE PRODUCT_DIM( PID NUMBER(3,0), NAME VARCHAR2(10), COST NUMBER(10,2), FLAG VARCHAR2(2), START_TIMESTAMP TIMESTAMP,             END_TIMESTAMP TIMESTAMP)

2.     Reverse engineer those two tables in ODI

a.     Create model with correspondent logical schema selected

b.     Open Model (Double click it)

c.      Go to Reverse Engineer à Mast attribute “PRO%”

d.     Go to Selective Reverse Engineering à Enable selective Reverse Engineering , New Datastores and Objects to reverse Engineering

e.     Select the objects and click on “Reverse Engineer” at top

3.     Open Target datastore (Product_DIM) and select OLAP TYPE as “Slowly change dimension”

4.     Expand Target datastore à Expand columns à Double “PID” à Description Tab à SCD Behavior to “Natural Key”

“Name” à Description Tab à SCD Behavior to “Overwrite on change”

“Cost” à Description Tab à SCD Behavior to “Add row on change”

“Flag” à Description Tab à SCD Behavior to “Current Record Flag”

“Start_Timestamp” à Description Tab à SCD Behavior to “Starting Timestamp”

“End_timestamp” à Description Tab à SCD Behavior to “Ending Timestamp”

5.     Create project

6.     Import Knowledge à IKM Oracle Slowly Changing Dimension

7.     Create Interface and select appropriate Logical Schema

8.     Drag and drop Source datastore into source side and Target datastore into target side

9.     Auto map à Yes and for Flag = 0 , Start_Timestamp = systimestamp and end_timestamp = systimestamp

10.                        Go to Flow tab and select knowledge module as “IKM Oracle Slowly changing dimension” and flow control as “false”

ODI Exception Handling

Process:

1.     Create a load Plan scenario

2.     Create Scenarios to an interface - make sure atleast one interface should have error so that we can test

3.     In my case, I am creating one interface as error. (Source to target load -> Selecting IKM Oracle incremental load but there is no primary key in target datastore so that it will error out)

4.     Add Scenario to Load Plan

a.     By default you will have root step

b.     Right click on root step and click on add step à select serial

c.      Drag and drop scenarios from Load plans and scenarios tab on to the serial

d.     Create one procedure which indicate it as error (i.e., create procedure with one task à in target side write a query “Select ‘Error’ from dual” and select appropriate technology as “Oracle” and Logical schema)

e.     Generate scenario for above procedure and drag and drop that scenario into exception tab

·Click on “Plus” Symbol

·Add wizard

·Select Exception

·Name the exception

·Drag and drop the scenario on exception

f.       Select serial step à Go to property inspector à Under exception handling à Exception step , select appropriate exception

ODI CDC

Process: Simple CDC
1.     Create source and target datastores
a.     Create database tables
b.     Create Physical Architecture
c.      Create Logical Architecture
d.     Create Model
e.     Reverse Engineer the datastores
2.     Import knowledge module à JKM Oracle Simple
3.     Open Model and navigate to Journalizing and then select Journalizing mode as “simple” and select above imported knowledge module
4.     Now, we need to select which datastore we are going to perform CDC
5.     Right click on datastore à Change Data Capture à “Add to CDC” and click “Yes”
6.     Right click on datastore à Change Data Capture à “Subscriber”à “Subscriber” à Provide subscriber name and click on “plus” symbol
[Note: Datastore should have primary key then only we can apply journalization]
7.     Right click on datastore à Change Data Capture à Start Journal and select appropriate subscriber for your process
8.     Now, create an interface in the project
9.     Drag and drop datastores into interface in appropriate location
10.  Select “Source Datastore” and go to property inspector, there we need to enable “Journalized Data Only” then it will create one filter on source datastore
11. Select that filter and modify the subscriber according to name you provide in your previous step and save it
12. Go to flow and select appropriate knowledge module (i.e., IKM Oracle incremental Update)

How to get source tables and target table to an interface from backend in ODI?

Steps:
1. Login to Work Repository Schema

2.Run below Query:

SELECT distinct SRC_COL.COL_NAME AS "SRC_COLUMN", 
TRG_COL.COL_NAME AS "TRG_COLUMN",
SRC_TABLE.TABLE_NAME AS "SRC_TABLE",
TRG_TABLE.TABLE_NAME AS "TRG_TABLE", 
TRG_TABLE.POP_NAME AS "INTERFACE_NAME",
CASE
WHEN TRG_TABLE.WSTAGE='E' THEN 'TABLE_TO_TABLE_INF'
ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE
FROM SNP_POP_MAPPING COL_MAP,
SNP_TXT_CROSSR SRC_COL_ID,
SNP_POP_COL TRG_COL,
SNP_COL SRC_COL,
SNP_TABLE SRC_TABLE,
SNP_POP TRG_TABLE
WHERE (1=1)
AND COL_MAP.I_TXT_MAP = SRC_COL_ID.I_TXT
AND TRG_COL.I_POP_COL = COL_MAP.I_POP_COL
AND SRC_COL_ID.I_COL = SRC_COL.I_COL
AND SRC_COL.I_TABLE = SRC_TABLE.I_TABLE
AND TRG_COL.I_POP = TRG_TABLE.I_POP;