Introduction

Oracle Data Integrator is a comprehensive data integration platform that covers all data integration requirements: from high-volume, high-performance batch loads to event-driven, trickle-feed integration processes, to SOA-enabled data services. Oracle Data Integrator (ODI) 12c, the latest version of Oracle’s strategic Data Integration offering, provides superior developer productivity and improved user experience with a redesigned flow-based declarative user interface and deeper integration with Oracle GoldenGate. ODI12c further builds on its flexible and high-performance architecture with comprehensive big data support and added parallelism when executing data integration processes. It includes interoperability with Oracle Warehouse Builder (OWB) for a quick and simple migration for OWB customers to ODI12c. Additionally, ODI can be monitored from a single solution along with other Oracle technologies and applications through the integration with Oracle Enterprise Manager 12c.

 

Let’s explore the ODI data preparation components in this blog.

 

 

Data Preparation Components

There are four different transformations in data preparation components in ODI.

  1. Binning
  2. Lag
  3. Lead
  4. Replace

 

 

Binning

Binning is used to categorize the data into multiple groups based on the min value, max value, and the number of buckets we provide.  This will be helpful when we want to analyze the data by categorizing them into groups.

Below is one example where the data is categorized based on employee salary.

 

The database function used in this mapping is given below.

width_bucket(EMPLOYEE.EMP_SALARY,0,25000,5)

 

The data is categorized based on the employee salary.

 

Lag

Lag implements the Oracle Lag function. It is an analytical function that is used to access multiple rows in a table without the need for self-join.

Below is an example of using the Lag transformation to load data into an Oracle table.

 

 

 

 

The Lag function used in this scenario is given below.

 

LAG(EMP_SRC.SAL,1,0) OVER (PARTITION BY EMP_SRC.DEPTNO ORDER BY EMP_SRC.SAL)

 

Salary is used as an expression value for the Lag function.

The Offset value is set to 1 as the previous salary value should be loaded.

The default value is set to zero.

This data can be used by analysts to identify the salary trends for a role in a department.

 

 

Lead

Lead implements the Oracle Lead function. It is used to access the next rows without a join in a table.

 

The lead function used in this example is given below.

 

LEAD(EMP_SRC.SAL,1,0) OVER (PARTITION BY EMP_SRC.DEPTNO ORDER BY EMP_SRC.SAL)

 

Salary is used as an expression value for the lag function.

The Offset value is set to 1 as the next salary value should be loaded.

The default value is set to zero.

 

 

 

 

Replace

Replace is used to replace the string/sub string in a column.

The replace function used in this example is given below.

 

REPLACE(EMP_SRC.JOB,’MANAGER’,’SENIOR MANAGER’)

 

 

 

Below is the screenshot of data in the target table

 

 

Manager string value is replaced with Senior Manager.

 

Conclusion

ODI developers can use this data preparation steps to cleanse the data and load it into Oracle database.

 

References

https://oracle-base.com/articles/misc/lag-lead-analytic-functions