Wednesday, April 20, 2011

When Temporary is not Temporary

Lets have a little questionnaire about the temporary interfaces that are used in Oracle Data Integrator. They are also popularly known as "Yellow Interfaces" because of the Yellow icon that ODI gives them at creation as opposed to the Blue icon of a regular interface.

Which of the following statements are false about Temporary interfaces:
  1. The tables get dropped once the calling interface is complete.
  2. The data in the temporary interfaces is deleted once the calling interface is complete.
  3. The table referred to as Target in the Temporary interface is created each time the calling interface invokes it.
  4. They can only be executed in Sunopsis Memory Engine.

Answer Choices:
1 is false
2 is false
3 is false
1 and 2 are false
1 and 3 are false
2 and 4 are false
All of the above

The answer is "All of the above" statements are false.

I will elucidate upon each of the above points and remove common misconceptions:
  1. The table that gets created stays there forever. It is a physical table that exists.
  2. The data in the temporary interface also stays there. It has to be removed manually or overwritten. Even though, this sounds similar to Global temporary tables in Oracle, it is not.
  3. This is dependent upon how you have configured your interface. If the option "CREATE_TARG_TABLE" is set to "Yes", then ODI will attempt to create this table each time. Otherwise, it will NOT. If the table already exists, the statement to create the table will raise an Error (Warning).
  4. The fact that you create a Temporary interface using Sunopsis Memory Engine doesnt mean that it cannot be created in the RDBMS using are working with. You can also create the temporary interfaces in the database of your choice. The fact of the temporary interface being in Memory is coincidental to the idea of a temporary datastore. If the Sunopsis Memory Engine is chosen as the "Work Schema", then the temporary table will remain there untill the Engine is rebooted/shutdown or an explicit code to drop that table is issued.

A very important statement -
There is nothing temporary about a temporary interface, but its name

The term "temporary" comes into picture because the temporary area (or the staging area) can be purged after executions. Though, they have to be purged manually or in a process.
The advantage of using a Yellow interface is ONLY to avoid the actual preparation of the destination datastore in the database or creation of this datastore in the "Model". Since, these are deemed to be temporary, theoretically, they are not part of our data model and hence, dont need to be in the "Model".

In all cases, the destination data is written to a database (either in memory of RDBMS). This depends on what staging area has been chosen.

Typically, you will want to create a temporary interface that gets invoked as a source in another permanent (blue) interface. A yellow interface may be invoked as a source in more then one blue interface.
In this case, each of the blue interfaces will share the same table (populated by yellow interface). So, if you are trying to run those jobs in parallel, then you may want to redesign the usage of yellow interface.

The temporary Interface, has an option to choose its datastore location, being: Data Schema or Work Schema. If the Work Schema is chosen and for StagingArea the Memory Engine then it will remain there until the Engine is shutdown or an explicit code to drop the table is issued.


FX said...

Maybe you should review this post looking at ODI 11g's Sub-select feature. The temporary interfaces now can be implemented as inline views.

Anonymous said...

Maybe you should review this post looking at ODI 11g's Sub-select feature. The temporary interfaces now can be implemented as inline view

Lily said...

excuse me, would you please tell me that this article is about ODI10g or ODI11g? Thx for your response.

Lily said...

If I use temporary interface in ODI 11g, will the temporary table generate in the database?

I did this in ODI 10g and 11g, the tables are generated. Maybe I did the wrong operation.

Ankit Jain said...

This information is applicable to both 10g and 11g.
However, as FX mentioned earlier, 11g has an additional option of using the Temp interface as a subselect.

When used as a subselect in ODI 11g, the temporary tables are not generated. They are referenced in the generated code as an inline view.

Lily said...

Ankit Jain,

Thx a lot.

But when I try to follow the limitations and use the subselect in ODI 11g, I can not check the 'Use Temporary Interface as Derived Table (Sub-Select)'box.

Would you please talk about how to use sub-select or if there is some examples? Thx again!

Ankit Jain said...


Did you upgrade from a ODI 10g instance ? And are you using Oracle as a source/target ?
If yes, then goto Topology manager and in Physical Architecture, double click on Oracle technology. There you will find "Support Derived Table" box unchecked. Check this and then try.