I am a solution architect for a global enterprise tech vendor.
I am looking for advice on the two following options:
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.