ODI Tools per category

Metadata OdiReverseGetMetaData OdiReverseResetTable OdiReverseSetMetaData Oracle Data Integrator Objects OdiDeleteScen OdiExportAllScen OdiExportEnvironmentInformation OdiExportLog OdiExportMaster OdiExportObject OdiExportScen OdiExportWork OdiGenerateAllScen OdiImportObject OdiImportScen Utilities OdiAnt OdiBeep OdiDataQuality OdiKillAgent OdiOSCommand OdiPingAgent OdiPurgeLog OdiReinitializeSeq OdiStartLoadPlan OdiStartLoadPlan OdiUpdateAgentSchedule Internet Related Tasks OdiFtpGet OdiFtpPut OdiInvokeWebService OdiReadMail OdiScpGet OdiScpPut OdiSftpGet OdiSftpPut OdiSendMail

Files OdiFileAppend OdiFileCopy OdiFileDelete OdiFileMove OdiFileWait OdiMkDir OdiOutFile OdiSqlUnload OdiUnZip OdiZip SAP OdiSAPALEClient and OdiSAPALEClient3 OdiSAPALEServer and OdiSAPALEServer3 XML OdiXMLConcat OdiXMLSplit Event Detection OdiFileWait OdiReadMail OdiSleep OdiWaitForChildSession OdiWaitForData OdiWaitForLogData OdiWaitForTable Changed Data Capture OdiRefreshJournalCount OdiRetrieveJournalData OdiWaitForData OdiWaitForLogData OdiWaitForTable

 

Pre-built Virtual Machine for Oracle Data Integrator 12c

Please note that this appliance is for testing purposes only, as such it is unsupported and should not be used in production environments.

This VirtualBox appliance contains a fully configured, ready-to-use Oracle Data Integrator 12c installation.

All you need is to install Oracle VM VirtualBox on your desktop/laptop and import the ODI 12c Getting Started appliance and you are ready to try out Oracle Data Integrator 12c -- no installation and configuration required!
The following software is installed in this VritualBox image:

• Oracle Enterprise Linux (64-bit) 2.6.32-300.39.5
• Oracle XE Database 11.2.0
• Oracle Data Integrator 12.1.2.0.0
• Java Platform (JDK) 1.7.0_15

Please check the ODI 12c Getting Started VM Installation Guide for detailed instructions on downloading and importing the VirtualBox image and make sure to also check the Release Note

Download Link

http://www.oracle.com/technetwork/middleware/data-integrator/odi-demo-2032565.html

Slowly Changing Dimensions in ODI - Tutorial

In this tutorial we will learn how to implement the concept of Slowly Changing Dimensions (SCD) using Oracle Data Integrator. As the name suggests, SCD in data warehousing refers to the slow change in data rather than the change on a regular basis. It is applicable in those cases where the attribute for a record varies over time.

There are three basic types of SCD’s:

1.Type 1 – Overwrite old values

 

In this case, new record replaces the original record. No copy of the original record exists. 

2.Type 2 – Create a new record

 

In this case, a new record is added to the dimension table. 

3.Type 3 – Create new fields

 

In this case, the latest update to the changed values can be seen. The original record is modified to reflect the change. So now let’s begin implementing SCD using Oracle Data Integrator. Open ODI Studio and follow the below steps! 

Pre-requisites: Oracle 10g Express Edition with *SQL Plus, Oracle Data Integrator 11g (version 11.1.1.7) 

Step 1: Create source, target tables for SCD using *SQL Plus

Source table

create table scd_test(EmpId int, EmpName varchar2(30), DeptName varchar2(30), salary number(6,2));

Insert few rows of dummy data inside the source table.

insert into scd_test values(101,'Karan','Computer',2200.23); insert into scd_test values(102,'Mahesh','Computer',3200.53); insert into scd_test values(103,'Prasad','Mechanical',5300.13);    Target table

create table scd_target(EmpId int, EmpName varchar2(30), DeptName varchar2(30), salary number(6,2), joining_date date, flag number);

Step 2: Create models for source and target tables 

Under Designer tab, create a new Model folder and then right click it, select Models–>Create new model. Reverse engineer both the source and the target tables under the same model. Provide any existing Oracle logical schema while re-factoring. 

Step 3: Modify target table model and it’s columns for SCD  

Open the target table datastore and change OLAP type to slowly changing dimension.

Then expand your target datastore to get all columns. Open required columns one by one and do the changes as follows!

 

Step 4: Create new interface 

While creating new interface select IKM as Oracle Slowly Changing Dimension. Map the source and the target tables. For columns joining_date and flag specify the implementation as shown below!

Creating interface

Run the interface without any errors. View the target table data.

Running the interface for the firsttime

Now, update few records of the source table. In this case, I will update name and salary of employee. Hence as per SCD Types 1 and 3, first a new record will be inserted since we had selected Add row on change for Employee name and then salary will be overwritten as we had selected Overwrite on change for Employee salary. 

update scd_test set empname='nitesh' where empid=101; update scd_test set salary=5634.43 where empid=102;

Run the interface again and then view the target table data. It now reflects SCD.

Output data

That completes this tutorial. Keep visiting for more!

Time Dimension Table Script

Following is the SQL script to create time dimension date starts from 01/01/2011 and ends after 1000 days.

CREATE TABLE time_calendar_dim AS
SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
TO_CHAR(CurrDate,'DY') AS Week_Day_Short,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
-- 31 AS Month_Time_Span,
MAX(TO_NUMBER(TO_CHAR(CurrDate, 'DD'))) OVER (PARTITION BY TO_CHAR(CurrDate,'Mon')) AS Month_Time_Span,
--to_date('31-JAN-2010','DD-MON-YYYY') AS Month_End_Date,
MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'Mon')) as Month_End_Date,
TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
TO_CHAR(CurrDate,'Mon') AS Month_Short,
TO_CHAR(CurrDate,'Month') AS Month_Long,
TO_NUMBER(TRIM(leading '0'FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
-- 31 AS Quarter_Time_Span,
COUNT(*) OVER (PARTITION BY TO_CHAR(CurrDate,'Q')) AS Quarter_Time_Span,
-- to_date('31-JAN-2010','DD-MON-YYYY') AS Quarter_End_Date,
MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'Q')) AS Quarter_End_Date,
TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
TO_CHAR(CurrDate,'YYYY') AS Year_ID,
--31 AS Year_Time_Span,
COUNT(*) OVER (PARTITION BY TO_CHAR(CurrDate,'YYYY')) AS Year_Time_Span,
-- to_date('31-JAN-2010','DD-MON-YYYY') AS Year_End_Date
MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'YYYY')) Year_End_Date
FROM
(SELECT level n,
-- Calendar starts at the day after this date.
TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') CurrDate
FROM dual
-- Change for the number of days to be added to the table.
CONNECT BY level <=1000)
ORDER BY CurrDate;

Create a New Project with ODI

STEPS:

• Create a new project with Oracle Data Integrator

• Create new data server and physical schema for a source RDBMS table

• Create new logical schema for the newly created physical schema

• Create new ODI model for the RDBMS table source

• Create a new data server physical schema for a target RDBMS table

• Create new logical schema for the newly created physical schema

• Create new ODI model for the target RDBMS table

• Create new ODI interface T2T Transformations

• Test the interface and verify results

Create a new project with oracle data integrator

Start ODI Studio and select work repository. Provide the user name and password and connect to the repository.

Knowledge Modules (KM)

Knowledge Modules (KM) implement “how” the integration processes occur. Each Knowledge Module type refers to a specific integration task:

Reverse-engineering metadata from the heterogeneous systems for Oracle Data Integrator (RKM).
 
Handling Changed Data Capture (CDC) on a given system (JKM)
 
Loading data from one system to another, using system-optimized methods (LKM). These KMs are used in interfaces. 
 
Integrating data in a target system, using specific strategies (insert/update, slowly changing dimensions) (IKM). 

These KMs are used in interfaces Controlling Data Integrity on the data flow (CKM). These KMs are used in data model's static check and interfaces flow checks. 
 
Exposing data in the form of web services (SKM).

Create new data server and physical schema for source RDBMS table

To create a new Physical Schema, we first need to create a data server. Navigate to ‘Topology’ tab, expand the ‘Technologies’ folder in ‘Physical Architecture’ tab in the left Pane. We can see number of folders inside this tab. Select the ‘Oracle’ folder (Since we are dealing Oracle database tables right now). Right Click on the Oracle folder and select ‘Create New Data Server’

 

Create New physical Schema for this Data Server

Select schema name from the list.

Create new logical schema for the newly created physical schema

Open the ‘Logical Architecture’ under the Topology Tab and right click on the ‘Oracle. Choose ‘New Logical Schema’ option to create a new logical schema for the newly create physical schema.

A new screen will appear. Provide the name of logical schema and choose the Physical schema from the list of physical schema.

 

Create new ODI model for source RDBMS table 

To create a new model for source, navigate back to the ‘Designer’ tab. Under the ‘Models’ tab in left pane, select ‘New Model Folder’ to create a folder for the model.

Right click on the ‘APPS_GL’ folder and select ‘New Model’ to create the source ODI model for source table.

A new screen will appear. Fill in the Name, Code will be populated automatically. Select ‘Oracle’ from the Technology drop down list and select the logical schema for this source table.

Select ‘Reverse Engineer’ tab in the left panel. Click on the Reverse Engineer tab at the upper left column to import the data into your ODI schema from your database.

Select ‘Selective Reverse Engineer’ tab in the left panel. Click on the Selective Reverse Engineer tab used to import particular data into your ODI schema from your database.

Table imported in 'SRC_APPS' model.

Create new data server, physical schema and logical schema for a target RDBMS table

Since our source database and target database are on different server. We need to create a new data server, new physical and logical schema. Same steps will be repeated as source data server, physical and logical schema. Follow the screen shots for creating the target data server, physical schema and logical schema.

Create new ODI model for the target RDBMS table

Create new ODI interface table to Table transformations

Select the ‘Projects’ Tab in ‘Designer’ Pane. Expand ‘First Folder’ and right click on ‘Interfaces’ to create a ‘New Interface’

Drag source table , target table from source and target model to interface mapping tab

We can add source and target tables from 'Quick-Edit' tab in Interface. Click Plus symbol and add source.

 

Join between tables

Click the flow tab and assign IKM for the target table.

Save the interface and Execute.

Test the interface and verify results

To verify the successful execution of the interface, navigate to the Topology Tab. Select Date > Today > and Interface info with date and time will be seen there. The green status depicts the successful execution of the interface.

Regards,
Haree