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

How many schemas can be used in an ODI interface ?

This question might sound strange, but my experience with people has led to explaining this fact in my blog.
So, tables are contained within a schema, for convenience sake, lets assume everything is done in Oracle SQL. I have two schemas as Source : Source1 and Source2, one staging : Staging and a target schema Target.

Case 1: Can I use tables in Source1 and Source2 joined by some clause as Sources ?
Yes, if tables are joined in source, it doesnt matter what schemas they belong to.
Staging can be set as target or defined as something other than target

Case 2: I need to use table1 from Source1 as source, I will need table2 from source2 while looking up a certain value while inserting in target but, essentially table1 and table2 wont be joined by any logical condition and Staging area is different from target
Now, things get a little tricky here.
Any table X that is referenced in ODI interface should satisfy one of the criteria to work successfully
1. It should be used as a source
2. If  X is used along with another table as Source, X and the other table should be joined
3. If X is not present in Source but needed for some reasons, the staging schema should be the same as the schema to which X belongs, and table X needs to be explicitly called with its schema name (e.g. Select * from StagingSchema.TableX ) and this mapping must be executed on staging area.
(In the next article, I will be explaining the use of 'odiRef.getObjectName' for this purpose)

Case 3: Source1 used as source, Source2 needed but not joined and Staging area same as target.
In this case, the staging schema is that of target i.e. Target
A workaround for this case is to bring the contents to Source2.Table2 into Target.TempSource2 (replicate the table of Source2 schema in Target schema and use it) But there is additional dependency of creating a table and pulling data into another one, which adds to the confusion

So, the moral of the story is : Whenever doing integrations make sure that Source Schemas are organized in such a way that they can be safely used without conflict, or, in other words, the datapoints must be designed smartly.

Cheers!