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?
Option2:
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.
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)
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.
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.
Thank you Gladson !!