The odiRef.getObjectName() function

Many a times it happens that a column value in a table (source or target) needs to be looked up in another table which is not present in the interface as a source. And there is no way you can join it with your source table. So how do we dynamically pull a record using a select query on the target or say, filters ?

Enter the odiRef.getObjectName() function
This function returns the object name, in our case, the table name that we need to dynamically pull data from.
Example:
Suppose you have a table called LOOKUP_TAB (KEY|VALUE format) from which you need to pull a record and insert it in a target column
In the mapping, you can write a query directly as

(select "value" from <%=odiRef.getObjectName( "L" ,"LOOKUP_TAB","D")%> where "key"='the_key')
Make sure you put this query in braces

At runtime, the schema name is automatically prefixed to the table, now, which schema will it take ?
Answer is the staging schema, or if 'staging schema different from target' is unchecked, it becomes the target schema.
This function comes in very handy for lookups ... you can also hard-code the schema and table name while writing the query like this (select "value" from Staging_schema."LOOKUP_TAB" where "key"='the_key')

But everytime the staging schema name changes, you will have to edit the interface hence odiRef.getObjectName() is a good way to do that.

ODI Best Practices Link

Follow this link if you have any interview or want to know the best practices of ODI :)

http://www.oracle.com/technetwork/middleware/data-integrator/overview/odi-bestpractices-datawarehouse-whi-129686.pdf

How to test ODI Interfaces quickly and efficiently ?

Testing an ODI interface, especially when there are tens of tables joined at the source can get very tedious at times. Finding simple errors like missing parenthesis or missing expressions can lead to headaches or sleepless nights.
I am putting some of my experiences here, and suggesting a few ways to make life better.

1. Make sure Source data is not having whitespaces before you start developing, if source tables are not trustworthy, use the LTRIM and RTRIM functions

2. Unix based text files have different line feed characters than Windows based, always make sure you know the source.

3. Sometimes the data might contain hidden characters , example, when you retrieve a number 'value' say 10, it will show you 10, but use the function TO_NUMBER(value) and it will give you an error saying invalid number, why ?? because there is a LF character ASCII code 13 after 10 which is not visible to naked eye.

4. Do not develop the interface completely and test its working in one go, there are so many things that might have gone wrong, but ODI will give only one error that it encounters, best way is to run the interface after every mapping (or maybe two) and you will get the errors then and there, although time consuming, it works.

5. Use the Active Mapping checkbox often while testing, disable a mapping and check if interface runs, the enable the mapping and check what happens. This helps in tracking erroneous mappings faster.

6. Always create a copy (duplicate) when doing major changes to interface, we tend to forget what was done before

7. Set 'Delete Temporary Objects' to false, this helps in having a look at what happens behind the curtains, and its an effective way to find out errors

8. Run the code generated by ODI at runtime (in operator window) in SQL developer or similar tools, ODI will not give you the exact line where error is encountered, but SQL editors give.

Hope these pointers help, will add more as I experience