Unit Test Framework for ETL (OWB and ODI)

Creating a good unit testing admittedly is as challenging as coding itself. It requires the same attention and time even though it's one of the most common part of the development life cycle that is not taken very seriously (alongside documentation).

In my previous project on OWB, I spent a lot of time creating an automated unit tests using utPL/SQL test framework. The challenges are even greater as everything has to be coded and compiled as PL/SQL packages. The framework does not have UI tools that can be used to speed up the unit test creation. But it provides many assertions libraries that suffice to say it can handle most of the assertions conditions that I needed.

It took almost the same amount of time to create a unit test for one OWB mapping. However I felt it was perhaps the most productive time on the entire development cycle. It cut out a lot of manual steps which are prone to errors. It helped to identify broken mappings which are caused by DDL changes. Last but not least, it was used for regression tests.

I had an opportunity to build the automated unit tests for ODI interfaces in my recent project. This time I used SQL Developer Unit Test Framework. The basic idea is the same as utPL/SQL but it has UI tools which simplifies creating unit tests. It also has libraries which perhaps is one of the feature that increase the productivity creating the unit tests.

ODI is not native to PL/SQL which means you cannot call ODI executable objects (scenario, load plan) directly from PL/SQL. Fortunately Oracle provides a SOAP API that can be called from PL/SQL using utl_http package. So the idea is to create a procedure that uses the API to call ODI scenario and assert the result.

I will talk about the implementation in the next posts. Watch out this space!