2020-04-08T13:37:00Z

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

RS
  • 4
  • 679
PeerSpot user
4

4 Answers

GN
User
2020-04-23T03:27:41Z
Apr 23, 2020

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)

Search for a product comparison
BD
Consultant
Top 5
2021-02-03T13:26:24Z
Feb 3, 2021

I know I'm late to the party but here are my comments to this.


I would ask what version of ODI are you running, but for the most part it doesn't matter. I will say that since you are saying Mapping which is new terminology to 12c or higher.


Also remember that ODI runs best as a ELT tool and not ETL. Once you get in that mind frame you will kill it at ODI. 


Both of the options are doable in ODI. 


I would lean to option 2 if you are looking to rerun just the errors or do some sort of reporting on the errors. I will say that if the data set is large consider the options for CDC (J$) and in conjunction with error (E$). In some cases this will make your process run sub second. In other cases capturing the error and/or CDC can take a longer to run. In the case where ODI runs longer with error and CDC, make sure that the table indexing and keys are optimized. Sometimes there's no helping the data. Best thing to do sometimes, when you don't do this every single day is to build it three different ways and compare. 


If you are hiring someone to do this they will analyze the requirements and come in with their own standards. I would still ask them for two examples of the build. Truth is most times we have about 4 builds by the time we are done so you aren't asking for much more.

SV
Real User
2020-05-22T08:39:15Z
May 22, 2020

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.

RS
Real User
2020-05-10T08:21:12Z
May 10, 2020

Thank you Gladson !!

Learn what your peers think about Oracle Data Integrator (ODI). Get advice and tips from experienced pros sharing their opinions. Updated: March 2024.
765,386 professionals have used our research since 2012.
Oracle Data Integrator (ODI) is a data integration software solution that provides a unified infrastructure to streamline data and application integration projects. It uses a powerful design approach to data integration, which separates the declarative rules from the implementation details. The solution is based on a unique ELT (Extract Load Transform) architecture, eliminating the need for a standalone ETL server and proprietary engine.  Oracle Data Integrator Features ODI has many...
Download Oracle Data Integrator (ODI) ReportRead more