ODI Real time Scenario's and Interview Questions: July 2017

Saturday, July 22, 2017

TableFunction Component in ODI 12c (12.1.3 onward)

Table Function Component (Available from 12.1.3 version onward)

Scripts:

CREATE TABLE DEV.DIM_DATE

(

DATE_KEY NUMBER,

DATE_TIME_START DATE,

DAY_OF_WEEK_NUMBER NUMBER(3),

DAY_OF_WEEK_DESC VARCHAR2(25),

DAY_OF_WEEK_SDESC VARCHAR2(10),

WEEK_IN_MONTH_NUMBER NUMBER(4),

WEEK_IN_YEAR_NUMBER NUMBER(4) ,

DAY_OF_MONTH_NUMBER NUMBER(4),

MONTH_VALUE NUMBER(4),

MONTH_DESC VARCHAR2(25) ,

MONTH_SDESC VARCHAR2 (25),

MONTH_START_DATE DATE,

MONTH_END_DATE DATE ,

DAYS_IN_MONTH NUMBER(5),

DAY_OF_QUARTER_NUMBER NUMBER(5),

QUARTER_VALUE NUMBER(5),

QUARTER_DESC VARCHAR2(25),

QUARTER_START_DATE DATE,

QUARTER_END_DATE DATE,

DAYS_IN_QUARTER NUMBER(5),

DAY_OF_YEAR_NUMBER NUMBER(5),

YEAR_VALUE NUMBER(5));

DROP TYPE DEV.T_TF_TAB;

DROP TYPE DEV.T_TF_ROW;

CREATE TYPE DEV.T_TF_ROW AS OBJECT (

ID NUMBER,

CALLDATE DATE

);

CREATE TYPE DEV.T_TF_TAB IS TABLE OF T_TF_ROW;

CREATE OR REPLACE FUNCTION DEV.GET_TAB_TF(STARTDATE VARCHAR2, ENDDATE VARCHAR2) RETURN T_TF_TAB PIPELINED AS

NO_OF_DAYS NUMBER;

BEGIN

select to_date(ENDDATE,'DD-MON-YYYY')-TO_DATE(STARTDATE,'DD-MON-YYYY') INTO NO_OF_DAYS FROM DUAL;

FOR i IN 0 .. NO_OF_DAYS

LOOP

PIPE ROW(T_TF_ROW(i,TO_DATE(STARTDATE,'DD-MON-YYYY')+i));

END LOOP;

RETURN;

END;

Pre-requisites

· Need to have knowledge on Oracle Table Function

· Need to have knowledge on creating function in Oracle DB

Approach:

1. Create Mapping and Drag and drop the Datastore from Model (i.e., DIM_DATE)

2. Drag and drop the Table function from components palette into logic editor

3. Connect Output connector of Table Function to input connector of Data store (i.e., DIM_DATE) as follows

4. Select table function component and go to property inspector and expand connector points and edit as mention below

5. Select DIM_DATE datastore and go to property inspector and map each field as follows

TO_NUMBER(TO_CHAR(TABLEFUNCTION.CALLDATE, 'YYYYMMDD')) AS DATE_KEY,

TABLEFUNCTION.CALLDATE AS DATE_TIME_START,

TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'D')) AS DAY_OF_WEEK_NUMBER,

TO_CHAR (TABLEFUNCTION.CALLDATE, 'DAY') AS DAY_OF_WEEK_DESC,

TO_CHAR (TABLEFUNCTION.CALLDATE, 'DY') AS DAY_OF_WEEK_SDESC,

TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'W')) AS WEEK_IN_MONTH_NUMBER,

TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'WW')) AS WEEK_IN_YEAR_NUMBER,

TO_NUMBER (TO_CHAR (TABLEFUNCTION.CALLDATE, 'DD')) AS DAY_OF_MONTH_NUMBER,

TO_CHAR (TABLEFUNCTION.CALLDATE, 'MM') AS MONTH_VALUE,

TO_CHAR (TABLEFUNCTION.CALLDATE, 'MONTH')AS MONTH_DESC,

TO_CHAR (TABLEFUNCTION.CALLDATE, 'MON') AS MONTH_SDESC,

TRUNC (TABLEFUNCTION.CALLDATE, 'MM') AS MONTH_START_DATE,

LAST_DAY (TABLEFUNCTION.CALLDATE) AS MONTH_END_DATE,

TO_NUMBER ( TO_CHAR( LAST_DAY (TABLEFUNCTION.CALLDATE), 'DD')) AS DAYS_IN_MONTH,

TRUNC(TABLEFUNCTION.CALLDATE) – TRUNC(TABLEFUNCTION.CALLDATE, 'Q') + 1 AS DAY_OF_QUARTER_NUMBER,

TO_CHAR(TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_VALUE,

'Q' || TO_CHAR (TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_DESC,

TRUNC (TABLEFUNCTION.CALLDATE, 'Q') AS QUARTER_START_DATE,

ADD_MONTHS(TRUNC (TABLEFUNCTION.CALLDATE, 'Q'), 3) – 1 AS QUARTER_END_DATE,

ADD_MONTHS(TRUNC (TABLEFUNCTION.CALLDATE, 'Q'), 3) – TRUNC (TABLEFUNCTION.CALLDATE, 'Q') AS DAYS_IN_QUARTER,

TO_NUMBER(TO_CHAR (TABLEFUNCTION.CALLDATE, 'DDD')) AS DAY_OF_YEAR_NUMBER,

TO_CHAR(TABLEFUNCTION.CALLDATE, 'YYYY') AS YEAR_VALUE

Output:

Tuesday, July 18, 2017

Customization of Knowledge Module

Scenario:

Client want to know which all records are inserting and which all records are loaded into target table. For this reason, we customized knowledge module and adding audit table to it

Approach:

1. Import IKM Oracle Incremental Update knowledge module

2. Add two steps

a. Drop audit table

drop table <%=odiRef.getTable( "L" , "TARG_NAME" , "W" )%>||'_AUDIT'

b. Create audit table and populate Unique Key along with Flag indicator

CREATE TABLE <%=odiRef.getTable( "L" , "TARG_NAME" , "W" )%>|| '_AUDIT'

SELECT <%=odiRef.getColList( "" , "[COL_NAME] , ",nt" , "" , "UK" )%> , IND_UPDATE

FROM <%=odiRef.getTable( "L" , "INT_NAME" , "W" )%>

3. Create option to enable and disabling the Audit table information

Output:

Monday, July 17, 2017

SCD Type 3

Type 3 - Adding a new column.

In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current' column and the old one into 'previous' column.

Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed technique.

Pre-requisites:

EMPLOYEES table

EMP table with PREV_SALARY as extra column

Incremental Knowledge Module (IKM Oracle Incremental KM)

Approach:

1. Reverse Engineer both Employees datastore as well as EMP datastore into model

2. Import IKM Oracle Incremental KM

3. Create Mapping and Drag and Drop EMP and EMPLOYEES datastores into logical editor

4. Connect EMPLOYEES datastore to EMP

5. Select PREV_SAL attribute in EMP datastore and go to property inspector, disable update checkbox and enable UD2 checkbox as below

6. Select SAL attribute in EMP datastore and go to property inspector, enable UD1 checkbox as below

7. Customize the KM whichever we imported earlier as below. (i.e., by adding one more step to it)

update <%=odiRef.getTable("L" , "TARG_NAME" , "D" )%> T

set <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD2)" )%> =

<%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>

where <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> in

(SELECT <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%>

FROM <%=odiRef.getTable("L" , "TARG_NAME" , "D" )%> T,

<%=odiRef.getTable("L" , "INT_NAME" , "W" )%> S

WHERE <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> =

<%=odiRef.getColList( "" , "S.[COL_NAME]" , ",nt" , "" ,"UK" )%>

AND <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>

<> <%=odiRef.getColList( "" , "S.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>)

8. Execute the mapping

9. Update the employees table and re-execute the mapping

UPDATE REPLICA.EMPLOYEES

SET SALARY = 22200

WHERE EMPLOYEE_ID = 100;

COMMIT;

10. Output as below

Detection Strategies

Detection Strategies:

A. NOT EXISTS

B. MINUS

C. POST_FLOW

D. NONE

A. NOT EXISTS

a. Load data from source table to C$

b. Compare to C$ vs Target table with all fields and inserts into I$ if it is not matching with IND_UPDATE flag as ‘I’

c. Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d. Whose flags are ‘U’ those records are updated to target table

e. Whose flags are ‘I’ those records are inserted to target table

Sample code for step b. as follows:

/* DETECTION_STRATEGY = NOT_EXISTS */

insert into ODI_TEMP.I$_DEPT

(

DEPARTMENT_ID,

MANAGER_ID,

DEPARTMENT_NAME,

IND_UPDATE

)

select

DEPARTMENT_ID,

MANAGER_ID,

DEPARTMENT_NAME,

IND_UPDATE

from (

select

DEPARTMENTS.DEPARTMENT_ID DEPARTMENT_ID,

DEPARTMENTS.MANAGER_ID MANAGER_ID,

DEPARTMENTS.DEPARTMENT_NAME DEPARTMENT_NAME,

'I' IND_UPDATE

from REPLICA.DEPARTMENTS DEPARTMENTS

where (1=1)

) S

where NOT EXISTS

( select 1 from REPLICA.DEPT T

where T.DEPARTMENT_ID = S.DEPARTMENT_ID

and (( T.MANAGER_ID = S.MANAGER_ID) or (T.MANAGER_ID IS NULL and S.MANAGER_ID IS NULL)) and

((T.DEPARTMENT_NAME = S.DEPARTMENT_NAME) or (T.DEPARTMENT_NAME IS NULL and S.DEPARTMENT_NAME IS NULL))

)

B. MINUS

a. Load data from source table to C$

b. Compare to C$ vs Target table with minus operator and inserts into I$ if it is not matching with IND_UPDATE flag as ‘I’

c. Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d. Whose flags are ‘U’ those records are updated to target table

e. Whose flags are ‘I’ those records are inserted to target table

Sample code for step b. as follows:

/* DETECTION_STRATEGY = MINUS */

insert into ODI_TEMP.I$_DEPT

(

DEPARTMENT_ID,

MANAGER_ID,

DEPARTMENT_NAME,

IND_UPDATE

)

select

DEPARTMENTS.DEPARTMENT_ID,

DEPARTMENTS.MANAGER_ID,

DEPARTMENTS.DEPARTMENT_NAME,

'I' IND_UPDATE

from REPLICA.DEPARTMENTS DEPARTMENTS

where (1=1)

minus

select

DEPARTMENT_ID,

MANAGER_ID,

DEPARTMENT_NAME,

'I' IND_UPDATE

from REPLICA.DEPT

C. POST_FLOW

a. Load data from source table to C$

b. Copy from C$ to I$ with IND_UPDATE flag as ‘I’

c. Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d. Identify not updated records by comparing fields with target and update the IND_UPDATE flags as ‘N’

e. Whose flags are ‘U’ those records are updated to target table

f. Whose flags are ‘I’ those records are inserted to target table

Sample code for step d. as follows:

/* DETECTION_STRATEGY = POST_FLOW */

update ODI_TEMP.I$_DEPT S

set IND_UPDATE = 'N'

where exists (

select 'X'

from REPLICA.DEPT T

where T.DEPARTMENT_ID = S.DEPARTMENT_ID

and ((T.MANAGER_ID = S.MANAGER_ID) or (T.MANAGER_ID IS NULL and S.MANAGER_ID IS NULL))

and ((T.DEPARTMENT_NAME = S.DEPARTMENT_NAME) or (T.DEPARTMENT_NAME IS NULL and S.DEPARTMENT_NAME IS NULL))

)

D. NONE

a. Load data from source table to C$

b. Copy from C$ to I$ with IND_UPDATE flag as ‘I’

c. Identify update records based on Unique Key field and update the IND_UPDATE flag as ‘U’

d. Whose flags are ‘U’ those records are updated to target table

e. Whose flags are ‘I’ those records are inserted to target table

[Note: It will update all the records whether it got changed or not changed]

Pages

Saturday, July 22, 2017

TableFunction Component in ODI 12c (12.1.3 onward)

Tuesday, July 18, 2017

Customization of Knowledge Module

Monday, July 17, 2017

SCD Type 3

Detection Strategies