How to promote code from one environment to another environment?

Scenario:
I need to promote my project from Development environment to SIT environment.

Approach:
Step 1:
Login to ODI Dev Work Repository.
Go to Designer Navigator -- Click on designer menu  -- Click Export -- Select Smart Export and then click ok.

Provide location where you want to export your porject.

Step 2:
Now drag and drop your project from designer navigator into the pop-up and click ok

Step 3:
Login to ODI SIT Work Repository.
Go to Designer Navigator -- Click on designer menu  -- Click Import -- Select Smart Import and then click ok.

Provide location where exported earlier and click on next.


Step 4:
Then it will opens following pop-up

Expand Topology -- 

If you want to use the objects which exported latest means you need to select overwrite. If you want to use already existing object means you need to select re-use. If it is completely new object it will create copy of it. If  want to ignore an object then you need to select ignore.

For example:

Then click ok.

Hence completed successfully!!!

How to Schedule jobs in ODI?

Note: For this demo, I used one package which is already created whose name is PKG_MAIN.

1. Connect to ODI

2. Go to designer tab

3. Expand Project Expand First Folder -- Expand Packages -- Right click on PKG_MAIN -- Click on Generate Scenario

4. Now Expand PKG_MAIN Version 001 scenario

5. There you will find schedule -- Right click on it à click on New scheduling

6. Update the attributes as mention below

i.e., Context: TEST, Logical Agent: Agent and Log Level: 5

Status:

Active need to be enable

Execution:

Daily need to be enable (if you want to schedule daily means you need to enable daily)

Time: You need to specify at what time it need to start.

[Note: Agent is installed in MDM server so it will consider server timezone]

7. We need to update schedule. For that go to topology navigator

[Note: if it not visible then go to menu bar and select window then click on Topology]

8.Expand Physical Architecture Accordion à Expand Agent à Double click on MDMDIAgent -- Click on update Schedule button then it will pop-up to select work repository, you have to select <Work Repo Name> and say ‘OK’

9. Click on View Schedule to confirm whether our schedule is updated or not.Then select tomorrow date under ‘TO’ and click refresh symbol as mention below

Then you can view your schedule there.

[Note: For Example purpose schedule that. Now I am removing it from schedule]

How to convert rows into cols using ODI 11g? (which is pivot in ODI 12c)

Scripts we are going to use in this demo:

--Source Table1 CREATE TABLE COMPANY_BRANCHES (BRANCH_CODE VARCHAR2(3), BRANCH_NAME VARCHAR2(20), ADDRESS VARCHAR2(100)) --Source Table2 CREATE TABLE COMPANY_STATS (BRANCH_CODE VARCHAR2(3), YEAR VARCHAR2(4), QUARTER VARCHAR2(2), PROFIT_MILLIONS NUMBER(8,2)) --Target Table CREATE TABLE COMPANY_ANALYSIS (BRANCH_NAME VARCHAR2(20), ADDRESS VARCHAR2(100), YEAR VARCHAR2(4), QUARTER1_MILLIONS NUMBER(8,2), QUARTER2_MILLIONS NUMBER(8,2), QUARTER3_MILLIONS NUMBER(8,2), QUARTER4_MILLIONS NUMBER(8,2) ) --Source Table1 Data INSERT INTO COMPANY_BRANCHES VALUES('NLR','NELLORE','ARAVINDANAGAR'); INSERT INTO COMPANY_BRANCHES VALUES('GDR','GUDUR','EAST STREET'); --Source Table2 Data INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q1',0.8); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q2',1.2); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q3',1.5); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q4',0.75); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q1',0.9); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q2',1.35); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q3',1.4); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q4',0.82); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q1',0.6); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q2',1.0); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q3',1.3); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q4',0.8); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q1',1.0); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q2',1.3); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q3',1.32); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q4',0.78);

Approach:

Let us consider source data store and target data store are available in ODI Model.

Step 1:

Create new interface and drag & drop 2 source datastore into source editor and drag and drop 1 target datastore into target editor.

Step 2:

Create join between two sources as follows

COMPANY_BRANCHES.BRANCH_CODE=COMPANY_STATS.BRANCH_CODE

Step 3:

Map columns as follows:

Target Datastore Columns	Source Datastore Columns
BRANCH_NAME	COMPANY_BRANCHES. BRANCH_NAME
ADDRESS	COMPANY_BRANCHES. ADDRESS
YEAR	COMPANY_STATS.YEAR
QUARTER1_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q1',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER2_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q2',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER3_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q3',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER4_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q4',COMPANY_STATS.PROFIT_MILLIONS,NULL))

Explanation:

We need to group all 

QUARTER1_MILLIONS,

QUARTER2_MILLIONS,

QUARTER3_MILLIONS,

QUARTER4_MILLIONS

based on BRANCH_NAME, ADDRESS, YEAR so that we need MAX

If we maps the columns like this in ODI then it internally group by following columns

GROUP BY 

COMPANY_BRANCHES.BRANCH_NAME,

COMPANY_BRANCHES.ADDRESS,

COMPANY_STATS.YEAR

Select appropriate KM's.

Output:

Change Data Capture - Simple

CDC:

Change Data Capture name itself clearly mentioning that we are capture all the new data which arriving to datastore.

Scenario:

We have a requirement - First we need load all the data which was existing in datastore. 

After that if there is new data arrive to that datastore means we need to capture that data (new data in the sense inserted or updated records) and perform 

operation only on newly capture data.

Simply we can say it as initial/full load and incremental load.

Approach:

Step 1:

Go to model --> go to data store --> right click on data store --> Change Data Capture --> Subscriber -->Subscriber --> Add SUNOPSIS and say ok

To add subscriber we need to have JKM knowledege module to be import into our project.

KM name:  JKM Oracle Simple

[Note: From where we need to import knowledge modules - <installed ODI path>\Oracle\Middleware\Oracle_Home\odi\sdk\xml-reference]

Open model and then go to Journalizing tab and select JKM knowledge module and save it.

[Note: we can add any name but if you don't want change means use SUNOPSIS by default in code you will get subscriber as SUNOPSIS]

Step 2:

Now we need to add datastore to CDC

Right click datastore -- Change Data Capture -- Add to CDC and click on Yes

Step 3:

Now we need to start the Journal

Right click datastore -- Change Data Capture -- Start Journal and select Subscriber and click on OK

Check the operator it started successfully or not.

Step 4:

Create mapping -- Drag and Drop source and target datastores from model

Connect output connector of source datastore to input connector of target datastore

Mention Auto map,

[Note: In this example I am using two KM's those are IKM Oracle incremental Update and CKM Oracle]

This physical is initial load or full load

Now we will one more physical for Incremental load. (It is the new feature available in ODI 12c onwards) 

[Note:If you want to implement in ODI 11g means we need to create two different interfaces]

Go to Physical Tab and click on new physical as mention below

Name them as :

Physical : Initial

Physical1: Incremental

Go to incremental and select source datastore -- go to properties inspector -- go general tab and enable journalized data only check box

Hence Developement Completed !!!