ODI-IKM Incremental Update (Updateless) v1

I have been thinking of creating a brand new Knowledge Module which will make an Incremental Update without using any UPDATE/DELETE SQL statements, only with inserts to increase performance. Initially, I was thinking that there should be a second integration table, but when I started scrawling to figure out flow diagram and how Knowledge Module works, I realized that there is no need for a second integration table.
If you are using Oracle database which your target table existing, this Knowledge Module can fit your needs. Let me try to explain how it works;

1. Drops and Creates I$ flow table (as usual)
2. Inserts the missing records (new records on source) to I$ flow table (if INSERT_NEW_ROWS option is set to true).
3. Inserts the matching records (existing records on source) to I$ flow table (if UPDATE_EXISTING_ROWS option is set to true).
4. Inserts the non-matching records (deleted records on source) to I$ flow table (if NOT_DELETE_NON_EXISTING_ROWS option is set to true).
5. Wrap them up and truncate & insert target table from flow table and drop flow table (if DELETE_TEMPORARY_OBJECTS option is set to true).
6. Analyze target (if ANALYZE_TARGET option is set to true). Continue reading

Using DBLink for Oracle to Oracle in ODI

DBLink is the one of the most powerful attributes, if it is needed to load data from one Oracle database to other Oracle database especially when you are doing extraction. If you want to use DBLink, that you already implemented in Topology as “Instance / DBLink (Data Server)”, in a Knowledge Module in Oracle Data Integrator (ODI), there are not fancy options. Continue reading

Adding Variabled HINTS in ODI

As in my previous post I have described how to implement HINTS per interface. It is a little bit tricky because HINTS are stored in development “ODI interfaces” and scenario of the interface must be regenerated when a HINT is changed and completely in developers’ hands.

I have thought about it and found a way to let dba’s change HINTS by updating a parameter table for each interfaces’/procedures’ each step. Once your dba updates/inserts the desired row (per SQL statement for each Knowledge Module or ODI Procedure) and commit the record it is been executed in the next execution of that interface or procedure.

Continue reading

Adding HINTS in ODI

We all know that Oracle is powerful when you use HINTS and override tables’ physical options and database parameters. There are many tuning and/or performance related blogs on this subject, but what I will share is how to implement HINTS in ODI. Continue reading

Adding Functions to ODI

In most cases we (ETL developers) are stuck into doing something. Sometimes an analytic function helps, sometimes a function in database. But is there a way to use this function in a structured way that ODI can understand? Let’s try to find out. 🙂

Languages in Topology Manager help you leverage your languages that you can use in ODI. You can even add a new language (if you know and eager to type all commands). Continue reading

ODI Handling DQ

If you have some SQL scripts to send output to some business users, especially for data quality issues or other information, how would you handle this. In case you do not have a data quality tool for that.

Step 1 : Prepare your data quality scripts.
Step 2 : Put those scripts into files.
Step 3 : Prepare PL/SQL or ODI code to read contents of those files. Send this script to your database, insert output to an ERROR table.
Step 4 : Read ERROR table by ERROR_CODE and loop it from beginning to end.
Step 5 : Send e-mail for each script, attach the appropriate file and mention how many rows are generated in this ERROR_CODE.
Step 6 : Continue with Step 4. Continue reading