TechWare

Oracle Data Integrator (ODI) is a very powerful product when handled correctly. Unfortunately, some mistakes may lead to dramatic results in Integration projects. This post compiles the Top 10 best practices that avoid the most common mistakes seen in integration projects made with Oracle Data Integrator. Best Practice #1 – Understand and Use Correctly Topology and Contexts The ODI topology and the contexts are one of the most powerful feature for running your design-time or run-time artifacts in various environments. In ODI, all developments as well as executions are performed on top of a  Logical Architecture  (Logical schemas, logical agent), that resolves, in a given  Context  to a  Physical Architecture  (real/physical source/targets data servers/schemas and ODI run-time agents). Contexts allow you to switch the execution of the artifacts from one environment (context) to another. Now, read the previous paragraph again. Make sure you got the concept of context, logical a

You can get any part of data and any sorted order you want with Analytic functions. Its syntax is OVER ((PARTITION BY … ORDER BY …)). Here is some samples with analytic functions I have used. First sample is getting number of (COUNT) employees by departments and ordered by (ROW_NUMBER ) departments. select     A. DEPTNO ,  row_number  ()   over   (partition   by   A. DEPTNO  order   by   A. EMPNO )  DEPT_EMP_ROWNO ,   A. EMPNO ,  row_number  ()   over   (order   by   A. DEPTNO ,   A. EMPNO )  EMP_ROWNO ,   count   (  *  )   over   (partition   by   A. DEPTNO )  DEPT_TOTAL_EMP_CNT ,   count   (  *  )   over   ()  TOTAL_EMP_CNT ,   count   (distinct   A. DEPTNO )   over   ()  TOTAL_DEPT_CNT from    SCOTT . EMP  A order   by     A. DEPTNO ,   A. EMPNO ; RANK clause is used for getting row number of given order. RANK and DENSE_RANK is differentiates as RANK is considering number of records and at last you can get count of records and DENSE_RANK is not equal to. Y

SCD Types 1) Overwrite old data with new 2) Adding a new row with surrogate key 3) Adding columns represents first, last and previous data Other: If data is big, you can take monthly snapshots to save difference. Data is smaller and you want to track multiple columns, then you can take daily snapshots. SCD type 2 is mostly prefered as keeps old versions. So let's roll on ODI for SCD type 2. Our example will be sh.channels table comes default on Oracle db; CREATE TABLE CHANNELS (   CHANNEL_ID        NUMBER                      NOT NULL,   CHANNEL_DESC      VARCHAR2(20 BYTE)           NOT NULL,   CHANNEL_CLASS     VARCHAR2(20 BYTE)           NOT NULL,   CHANNEL_CLASS_ID  NUMBER                      NOT NULL,   CHANNEL_TOTAL     VARCHAR2(13 BYTE)           NOT NULL,   CHANNEL_TOTAL_ID  NUMBER                      NOT NULL ); INSERT INTO SH.CHANNELS ( CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS, CHANNEL_CLASS_ID, CHANNEL_TOTAL, CHANNEL_TOTAL_ID ) VALUES ( 3, '