Skip to main content


ODI Best Practices to Achieve Fast-Paced ETL (Part 2) #C17LV

Integrating data from non-Oracle source systems with an Oracle prepackage solution is a common practice that requires source to Target integration. While implementing BI Apps/Oracle EHA (Enterprise Healthcare Analytics) with a new source, I used several best practices, which can be leveraged while integrating a new source in an Oracle Warehouse using ODI. As a former Oracle development team member, I understand the key issues. In this post, Part 2 in my series, I will explain how you can integrate a new as well as a custom source with an Oracle application using ODI. I have taken an example for Oracle EHA and listed some of Key Design Decisions we have taken that reduced the ETL effort substantially.

Integrating New Source System with Oracle BI Applications:

It is very common for customers to integrate new sources with Oracle BI Applications. Oracle BI Applications contains pre-built data marts as well as reports and dashboards. Oracle usually provides pre-built adaptors to connect with common data sources such as PeopleSoft, Siebel, and JD Edwards. While connecting to pre-defined sources is straightforward, connecting with undefined sources requires more effort on the ETL side. There are several ways to address this including customizing Oracle provided universal adaptors or writing the custom ETL routine.

Case Study: Integrating Oracle Enterprise Healthcare with custom Source System 

While implementing Oracle EHA (Enterprise Healthcare Application) for one customer, I followed the architecture outlined below to integrate their source with Oracle EHA. Creating a view layer on top of the source system and following ODI best practices significantly reduced the development effort.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

These Oracle views resemble the target table structure of the data warehouse, making the ODI data mapping less complex. The view layer doubles up as a data governance layer and it is easy to change this layer without making any additional changes in the ODI Layer.To make the design efficient, the following view creation guidelines are created

View Creation Guidelines

Oracle Views matches the structure of the target HDI (warehouse) structure. Also, below are the high- level design considerations while creating the views:

  • Add View Header Information in the view creation script.
  • Comments against each column in the select clause to indicate the HDI column it is mapped to.
  • In case of a combined query, use Union All instead of Union to improve performance.
  • Sequence generated column need not  be included in the view. It will be populated in the ODI.
  • Request ID (ODI Mapping ID) will be populated at run time and need not be part of the view Definition.
  • Obsolete/Data Governance filtered columns need not be part of the view definition.
  • Database comments need to be included in the View creation script. (comments should include the name of the Cerner table/column)
  • Checksum column will be populated in ETL and need not be part of View definition.
  • Insert date will also be populated in ETL and need not be part of view definition
  • Standard Fields like Enterprise ID will be populated using a global parameter and hence will not be part of the view definition. Making it a global parameter helps in code reusability

In summary, it should be very clear that ODI is a great tool to solve various kinds of Data flow and Data quality scenarios. It is equally important to follow these best practices while designing the solution.

Learn More at Collaborate

ODI is a great ETL tool and if it is used correctly by understanding the design philosophy and best Practices, it can result is saving ETL Time and effort for customers.

If you’re headed to Collaborate 17 in Las Vegas, April 2-6, I invite you to stop by my presentation ODI Customizations to Achieve Fast-paced ETL for Oracle BI Apps on Tuesday, April 4th from 11:00am – 12:00pm.  While you are there, please also check out Perficient’s other must-attend sessions at the show!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Pawan Kumar

More from this Author

Follow Us