Looking for advice on how to invoke a select script through ODI?

I am a solution architect for a global enterprise tech vendor.

I am looking for advice on the two following options: 

Option 1:

I have a select script that fetches data by joining many source tables and performs some transformations like aggregations (group by), data conversion, sub-string, etc.

Can I invoke this script through ODI mapping and can return results (transformed data output) be inserted into the target of ODI mapping?


Can I convert the select script into equivalent ODI mapping by using equivalent ODI transformations, functions, lookups, etc and use various tables (tables in joint clause) as a source of mappings?

In short, can I develop ODI mapping that is equivalent to provides select scripts plus a target table to insert records into?

What are the pros and cons of the above two options? 

Is it still possible to track transformation errors, join source tables and clause condition related errors through ODI with option 1?

Will log file for mapping failure have granular-level details as offered by option 2?

Can I still enable Flow Control at Knowledge Module and redirect select script errors into E$_ error tables provided by ODI?

Thanks! I appreciate the help. 

33 Answers

author avatar

Thanks for using ODI, I love it, ODI is one of the good tools for ELT and very strong/powerful with Oracle Target (I have used most of the time).

Now your scenario is a good one. Nowadays ODI giving much flexibility in this area. As you know the KM technology within ODI, several types of knowledge Modules are available which gives you an option to address any problem in the real data warehouse world.

I am not sure what version of ODI you are using, and I may be assumed that you should be 12c ( I am using ODI 12c) and above. There are multiple options you can achieve this Query(source) to the table(target) in ODI 12c. I would prefer to simply override the extract option in the physical tab. if the query is complex, well tested, and performing well with expectations then simply use the extract option. If you are doing the first time then you need to consider few things before getting succeeded such as the mapping of attributes and KM(s), and all other regular target properties. The second option is more of the traditional ETL tool development, which needs more resources than the first one. With correct KM you can use the ELT on both options, also make sure that the query (from clause objects) is all from the source system.

Using the Query makes the job (ETL Development) with appropriate KM is a huge plus because if there is any business change or rules update just update the query and test it. At the same time, the problem is when the version change(or upgrade) not only ODI but underlying Oracle DB, where some of the functions will be re-tested every time or to be removed with current versions as the maintenance is hard to keep track. Always suggest selecting best KM for each data extract as well as the flow control (LKM, IKM or CKM)

author avatar
Top 20Real User

But I never had been a specialist in ODI since we focussed on OWB and possible DevOps enhancements,

But together with IKAN we worked on migration projects from OWB to ODI with Databtech (i.e Angelo Castriotta), which may be one of the best-experienced partners for ODI.

author avatar
Top 5Real User

Thank you Gladson !!

Learn what your peers think about Oracle Data Integrator (ODI). Get advice and tips from experienced pros sharing their opinions. Updated: April 2020.
442,517 professionals have used our research since 2012.