Yellow Interface (Temporary Interface) in ODI11g

Yellow Interface (Temporary Interface) in ODI11g:

This is another quick post again based on some request. After the latest patch release of 12C, I am giving most of my leisure time in exploring and integrating with Bigdata. My personal opinion about this product is, its the only tool that I can think of which has wide variety of features for Hadoop Ecosystem and leading to be the market leader in Data Warehouse World. And thats the reason I am pretty much excited but unfortunately my excitement led me to a problem. During my experiment not sure how I deleted my 11g repositories.  Lost many projects that I had created for my blog. Nevertheless I created one more for this write-up.

Now lets get back to the original topic. I have explained many times on OTN forum, if you are looking for a sub query  implementation to form some sort of joins in blue interface then first think of yellow interface. There is already one post on this written by Cezar Santos. One more post  from David Allan to use a query as source using a KM IKM SQL_as_Source.  Today we will look on the same with different perspective and its implementation in ODI 11g. Keep visiting my blog for next post where I will show you the same implementation in ODI 12C.

Requirement: Load the latest user information based on the last updated time stamp

Source Query:

select e.*
from userinfo e
inner join
(select userno, max(lastupdate) as lastupdate from userinfo group by userno
) f
on e.userno    = f.userno
and e.lastupdate = f.lastupdate
CREATE TABLE USERINFO
(
USERNO   NUMBER,
USERNAME VARCHAR2(50),
USERADDR VARCHAR2(100),
USERCELL NUMBER,
LASTUPDATED DATE DEFAULT SYSDATE
)
CREATE TABLE LATESTUSERINFO
(
USERNO   NUMBER,
USERNAME VARCHAR2(50),
USERADDR VARCHAR2(100),
USERCELL NUMBER,
LASTUPDATED DATE DEFAULT SYSDATE
)

Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1111,‘SIMON’,‘MARY ROE
MEGASYSTEMS INC
SUITE 5A-1204
799 E DRAGRAM
USA’
,6523652312,to_timestamp(’06/04/2013 12:55:29′,‘DD-MON-RR HH.MI.SSXFF AM’));
Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1111,‘SIMON’,‘CENTER FOR FINANCIAL ASSISTANCE
421 E DRACHMAN
TUCSON AZ 85705-7598
USA’
,8562856231,to_timestamp(’06/04/2014 00:00:00′,‘DD-MON-RR HH.MI.SSXFF AM’));
Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1111,‘SIMON’,‘SIMON SMITH
100 MAIN ST
PO BOX 1022
SEATTLE WA 98104
USA’
,7856785665,to_timestamp(’06/04/2015 12:55:51′,‘DD-MON-RR HH.MI.SSXFF AM’));
Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1112,‘JAMES’,‘James Carter
5520 Quebec Place
Washington, DC 20521-5520′
,9696858574,to_timestamp(’09/04/2005 12:55:57′,‘DD-MON-RR HH.MI.SSXFF AM’));
Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1112,‘JAMES’,‘James Carter
4150 Sydney Place
Washington, DC 20521-4150′
,9696858532,to_timestamp(’06/04/2008 12:56:08′,‘DD-MON-RR HH.MI.SSXFF AM’));
Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1113,‘BHABANI’,‘Banaglore,580036’,9090909090,to_timestamp(’06/06/2010 12:57:08′,‘DD-MON-RR HH.MI.SSXFF AM’));
Insert into USERINFO (USERNO,USERNAME,USERADDR,USERCELL,LASTUPDATE) values (1113,‘BHABANI’,‘Bangalore, 4th Cross, BTM Layout’,9191919191,to_timestamp(’04/01/2015 12:58:01′,‘DD-MON-RR HH.MI.SSXFF AM’));

In this image I dragged the source table and manually typed the target name in properties window. Then added the attributes (userno, max(lastupdate)) that i want to select in the subquery. Go to flow tab and select IKM Sql Control Append. Thats it.

INTSUBQUERY1

INTSUBQUERY2

 

Once you save above interface you can see the color changed to yellow. Then create anohter interface with same source table. Drag the yellow one in to the canvas and do the join based on userno and lastupdate column. This is an inner join so no need to select anything in properties as it is the default join.

INTSUBQUERY3

INTSUBQUERY4

 

After execution you can the see below code generated by  ODI which is same as the query we had prepared in the beginning.

INTSUBQUERY5

 

And this is the data that got loaded to my target table.

INTSUBQUERY6

 

Summary:

You can always devise or translate most of the query  into an interface. You just need to figure out how many temporary interface you need to form the query. Once this is done you can leverage all the inbuilt functionality of an Interface. Simple, Flexible and yet Powerful. Let me know if you get stuck anywhere. See you!!

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

6 comments

please post difference between yellow interface and normal interface
scd type-1 vs SCD TYPE-2

THANKS

difference between yellow interface and normal interface

Yellow Interfaces are temporary interfaces where in target table is created inside an interface, they can be used as source in other interfaces for example to perform look up or domain value conversions, since target table is created within interface there is no need to have a datastore/model.

Normal Interfaces are also called concrete interfaces created with specific perform of data transformation/loading, rest are opposite to details provided for yellow interfaces above.

scd type-1 vs SCD TYPE-2

SCD TYPE-1 –> Meant for Merge operation where in all the deltas of transactions coming out of OLTP system/sources will be updated/Merged, you have IKM Incremental Update or Merge KM supporting this, need to define primary/update key to support this.

SCD TYPE 2–> Meant for capturing the changes in dimension tables in Datawarehouse (OLAP), you can see your target tables are created with 3 extra columns like start date, end date and active flag to support in capturing the changes.

There is target specific IKM SCD Type2 KM available in ODI, you can leverage this KM by setting up few parameters like making the target datastore as OLAP, identifying the surrogate/natural keys, overwrite columns and insert new value up on change….etc

Hope this helps!!

Thank you for your contribution Muniraj!!. We do encourage our readers to participate in the discussion and in helping each other.

Just brilliant explanation! Love this blog.

Gauri Chaudhari / Reply

Hi Bhabani,
Can you please guide on following?
If the table definition in database changes for e.g. the column “salary number(4)” changes to “salary Number(10)”.
Do I have to reverse engineer all the models(which are using the table) again and create/refresh new datastores in order to fetch its new defination in ODI studio?

Please Guide.

hi hope you are fine.

kindly can you tell us that while upgrading odi 12c to 11 g we need to update temporary interface manually?

Leave a replay to Gauri Chaudhari Cancel reply

required*

Are you a human? *