Skills an ETL developer on ODI 11g (Oracle Data Integrator) requires.

ETL skills in general

As with everything in life you need to be passionate about what you are doing. This certainly also holds true for your job as an ODI developer. When we hire someone we first try to figure out how much passion the guy or gal has for data warehousing and business intelligence.

Independent of the tool, any ETL developer needs to have very good SQL knowledge. Good is not good enough. SQL is the most important skill. This holds definitely true for ETL tools that push the ETL code down to the database, but also for tools that execute the code on their application server such as Informatica. An ODI developer must know how to write advanced analytic functions. Analytic functions allow any procedural logic to be replaced by set based logic. Forget about PL/SQL. Or any such procedural language.

Apart from good SQL knowledge any ETL developer also must have a sound understanding of database technology, e.g. being able to read and interpret an explain plan is a distinct advantage.

Throw some data modelling experience into the mix and you have a solid skillbase as an ETL developer.

Specific ODI (11g) skills

What about the specific knowledge you need to work with ODI then?

Apart from SQL, you need to know some basic Java and have experience with scripting languages (ideally Jython, Java Bean Shell, and Groovy). This is useful to write ODI Knowledge Modules and do some fancy stuff with the substitution API using Java Bean Shell.

As of ODI 11g it is useful to have very good Java/Groovy skills. This will allow you to automate a lot of ODI tasks and exploit the new SDK to its full potential.

Last but not least it is useful to have some knowledge around application servers, in particular Weblogic. The ODI agent, the ODI console (ODIC), and the Enterprise Manager plugin all run on Weblogic. Knowledge around Weblogic is useful for troubleshooting stuff. I personally hate it, but that is probably because I don’t know enough about it myself.

A look in the crystal ball

The world of data warehousing, enterprise application integration, master data management, and SOA are moving closer together. You will see this reflected in the various tools, e.g. in ODI 11g there is a strong (somewhat overlooked) emphasis on web services and data services. In my opinion this stuff will become more and more important. If you want to learn one new skill then this should be around web services, SOA, queue and message based systems etc.

Last Words

You see the actual tool knowledge itself is not that relevant (you know a fool …). None of the ETL dudes who work alongside me have actually had any prior knowledge of ODI. We have a mix of guys with experience in Informatica Powercentre, Oracle Warehouse Builder, and the Microsoft stuff (what was the name of that again?). With proper mentoring it takes them on average two to three weeks to pick up the specifics of ODI.