While writing another post for odiexperts , I realized that its been a long time since we have opened odiexperts.com and we haven’t celebrated our first birthday . So I scrolled back to Cezar’s first lovely post – Welcome to ODI Experts, this is Cezar Santos.I saw the posted date  of August 16 , 2009 . Yeppiee !!!  we have crossed an year but we forgot to celebrate our first birthday, so even though we are a little late but not too late .

I  welcome you all for our lovely birthday and Thank you so much Cezar for giving me the opportunity to write in this blog and above all a big Thanks you to all our viewers and everyone who has made this blog a success . Thank you so much.

A special thanks to Craig Stewart who have provided us his value expertise and suggestion and making the post more valuable .

We will try our best to keep supporting and bringing more ideas ,tips , best practices of ODI to all of you and above all we request all viewers to provide us more value suggestion so that we can provide more quality and work in our effort towards bringing the best of ODI .

Yes… Birthday…  this is one of the days that we realize how fast is the time…

I like to write but I’m not sure if I have the right words today… but Thank you ALL!

Dev, our partnership is a success! Your hard-work, intelligence, dynamism and fast mind could bring to ODI Experts the possibility to be here until today.

I don’t know if all friends and readers of ODI Experts Blog knows but we don’t make any money from this work. We are here because we love what we do. A lot of people thinks we are Oracle employee, but not, we doesn’t have any professional relationship to Oracle.

When we got emails (and they are several by day) or reply a comment or even reply at ODI Official Forum, is just because we like to help and, even more, see the people happy in realize his job.

Because of that, I need to thank to some friends that helped us in the same spirit.

Thanks a lot our lovely viewers from both of us . We love to see you again and keep helping us to grow odiexperts.com

Thanks

Hi Everyone…

One more post in our “Oracle Data Integrator 11g” serie.

In the last two features of the ODI 11g we have been covering the Initial and repository logic part . Now in this Post we will be covering few of the Inside looks, option changes in ODI 11g.

The idea is show what changed in the GUI. How to find some features that we already know and some new.

As I already said, now there is just an “ODI Studio” and the 4 ODI modules are inside the same visual interface.

Once logged into the ODI 11g we find that unlike different component in ODI 10g we have all the Designer, Topology , Operator and Security Manager integrated into Sub tabs which can be Closed and called again. If you have accidently closed any Navigator , you can call from View options.

Even in the each Component, instead of Tabs we find Sub sections.

Others and Solution are something we don’t use more and we can minimize them to have more space. To minimize, at the end you will find down arrow under each sub section and you can click on it and click on Minimize.

All the minimized sections are stored under this small bar with three dots at the end .

Once click you will find all the minimized objects. Click on it to expand.

Option which were seen in the title bar option in ODI 10g is not available as Scroll bar option

You can find the Import Work Repository option and other options which we found in File in 10g are now again as scroll menu at the top tab in Designer.

Similar such categorization and formatting is pretty much applied to other components of ODI.

ODI 11g also allows few good handling of Tabs while working with multiple or parallel Interface or Models etc . By default when you open objects in ODI they open by tabs .

and if you want to see two parallel tabs , and select New Editor Tab Group and see the objects in parallel and you can do for multiple objects.

The Interface is a little different in ODI 11g .

In ODI 11g we can split the Inter face into two screens, you can enable those options

And this way,you can see the two part of the ODI Interface simultaneously, the only restriction you cant see the same option on both section.

We can also view the source data store as Compact and symbolic.

There is also new add a lookup functionality ,where you can add datastore and make the required joins in a form of wizard.

and has a different color for lookup

You can also optimize the size of Datastore .

There are more user defined Flags

There is also a Quick Edit tab where we can see all our joins , lookup and all other information and we can edit and modify them accordingly .

The Flow Control bar is little different from previous version and you can select the IKM and LKM when you select the Staging Area.

ODI 11g supports Partition and Sub partition too and we shall cover them in a separate post in near future in detail .

The Models are pretty much having the same functionality except instead of Reverse button we have an icon to do that . The green arrow icon lets us to do our Reverse engineer.

In the Column as we have discussed before we have support for Partition and Sub Partitioning

Well, we tried to give you a general vision of everything in the graphical interface and its characteristics. More will come in the next days….

Thank you for visit us!

Hi Guys,

Another really usefull tip about data types!

There are some time it happens we don’t have the required data type in respective Technology in ODI say for example DATETIME YEAR TO DAY ,DATETIME YEAR TO SECOND etc and so when we do a Selective reverse or RKM we don’t get the correct data type listed causing which we get the Null data type or get assigned to String .

The other simple solution if you are particular in getting the data type listed in Model . Here are the steps to Manually add the data type in Topology and create the respective conversation syntax.

Step1 . Go to the Respective data type and right click and Insert Data type or Duplicate an already existing datatype

Step2. Provide the required code and syntax

Step 3 . Provide the required converted To and Converted From detail for the Technology you are going to Work with . Lets say my target will be Oracle in that case , I have assigned to Timestamp .

Easy and Simple solution in dealing with Data types missing.

Any comments???

It’s our pleasure to have you here, at  www.odiexperts.com

Hi Friends,

Right now I’m in São Paulo airport waiting for a flying that is very delayed! With some time free, let me write about something that I want to publish there is a long time….

Surprise! Isn’t an Oracle Data Integrator issue! hehehehehehe……

I really like to work with ORACLE RDBMS but doesn’t exist a “Is Number” function is, in my opnion, a lack of funcionality that I can’t understand.

Every time that is necessary to check if the returned value from a column (or a substring from it ) is number, I created a database (PL/SQL) function that return true or false and then uses it in the query.

In this way works, but if I want to do the same in a Microsoft SQL Server, there is a “native” function to do it.

The big problem is that, sometimes, you don’t have an user that can create PL/SQL procedures and, then, you can’t do this type of validation.

For solve this, I created the following technique:

A single SQL query that can return if a column is number or alphanumeric

select case when trim(TRANSLATE('1234f','0123456789', ' ')) is null
            then 'numeric'
            else 'alpha'
       end
from dual

Where ’1234f’ should be substituted by the column that you wish to validate.

The principle is very simple, if  the numbers from a string are removed and the result is different of null then the string isn’t numeric. The Trim funcion eliminates any remained space.

Plus, it is pretty usefull when there some non-visible ASCII char  in a column.

Well, I couldn’t let to show a use of this in ODI.

When there is a text file to be loaded and it is necessary to check (CKM) if the columns are with the right data (thru ODI constraint) this single query can send to the error table (E$) any record that has the wrong data.

See you around!

Hello all,

OdiSqlUnload is a great utility for unloading  but then we need to hardcoded and provide the parameters of the username and password which can change in the Other Environment in ( Testing , UAT or Prod ) . Here is a small trick to handle that situation.

In the Command on Source, Select the Required Technology and Schema name .

Copy the Syntax of the OdiSqlUnload into an ODI procedure into Command on Target  with Sunopsis API as the Technology

OdiSqlUnload “-FILE=” “-DRIVER=<%=odiRef.getInfo(“SRC_JAVA_DRIVER”)%>” “-URL=<%=odiRef.getInfo(“SRC_JAVA_URL”)%>” “-USER=<%=odiRef.getInfo(“SRC_USER_NAME”)%>” “-PASS=<%=odiRef.getInfo(“SRC_ENCODED_PASS”)%>” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=,” “-ROW_SEP=\r\n” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1″ “-XML_CHARSET_ENCODING=ISO-8859-1″ “-FETCH_SIZE=5000″ “-QUERY=select * from QUERY”

This way ODI will automatically get the parameters from the topology and also use the Fetch_Size 5000 for good performance .

Also look into this post for generating header in OdiSqlUnload – http://odiexperts.com/?p=1845

Just a small trick . Look for more ideas at odiexperts.com