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

Hi Guys,

Today we are going to talk about an old question that we already got several, hundreds, thousands (lol) of questions but, at true, is pretty easy!

I would like to indroduce you to the Mr. SCD!!!!!

In few lines, you will be able to configurate and use it…. Let’s start:

This post defines the necessary setting to use the Oracle Slowly changing dimension .

Set the columns with required properties namely,

SURROGATE_KEY – Sequence

NATURAL_KEY – Primary or Unique Key

OVERWRITE ON CHANGE – Column for which data needs to be updated

ADD ROW ON CHANGE – Column for which a new Surrogate Key and New rows to generated when the data for this Column changes

CURRENT RECORD FLAG – 0 or 1  .  The KM populates this column so no need to populate this column .(1 : Current Record , 0 : Past Records)

STARTING TIMESTAMP – Column  indicating the beginning of a Record’s availability. use SYSDATE or SYSTIMESTAMP depending on the datatype.

ENDING TIMESTAMP -Column indicating the end of the Record’s availability. The value is a default of “01 January 2400″

Map the columns in this way where the Surrogate Key needs to be sequence .  The ending timestamp and Current Flag columns  need to be mapped even that the  KM will populate those column accordingly. Just add any value to them.

Note :

1. If in case you don’t  have any ‘Overwrite on change‘ column , please check the option ignore errors on Update Existing row., since in the select statement it wont find the column to do query and will error out.

2. If the Target datastore is table , Flow control or static control have to yes and the appropriate CKM have to be selected , else there will be error in Historize old rows saying ‘invalid user.table.column, table.column, or column specification”.

Well, as you can see, no secrets to use it!!!!