Invoking an select script through ODI (Oracle Data Integrator)

Question

May I have your opinion on below queries please: Option 1: I have select script handy with me which fetch 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 return results (transformed data output) can be inserted into target of ODI mapping ? Option2: Convert the select script into equivalent ODI mapping by using equivalent ODI transformations , functions , look ups etc and use various tables (tables in join clause) as source of mappings. Basically develop ODI mapping which is equivalent to provided select script plus a target table to insert records into it. I need to know pros and cons of both options in above (if option 1 is possible). Is it still possible to track transformation errors, join source tables and where clause condition related errors etc through ODI with option 1? Log file for mapping failure will have as 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, Rajneesh

Answer 1

Option 1: ODI 12c includes that concept out of the box. On the physical tab of a mapping, click on the source node (datastore). Then in the properties pane, there is the CUSTOM_TEMPLATE option under "Extract Options" menu. This allows to enter a custom SQL statement that will be used instead of the code generated by ODI.

However it is probably less maintainable over time than option 2. SQL is less visual than mapping components. Also if you need to bulk change it, it will be trickier. Changing a component in several mappings can be done with the SDK. Changing SQL code would require to parse it. You might indeed have less information in your operator logs as the SQL would be seen as just one block of code. It also wouldn't provide any lineage.

I believe using Flow Control would work but I haven't tested it.

Option 2 would take more time to complete but with that you would benefit from all the functionalities of ODI.

My own preference would be to occasionally use option 1 for really complex SQL queries but to use option 2 for most of the normal use cases.