Increment Date type variable in ODI

Hi Guru,

It is possible to increment the Date type variable in ODI.

In the refreshing tab of the variable add the appropriate SQL statements

for example

Oracle:
select SYSDATE+1 from dual

Thanks Guru
Ram kumar Lanke

Retrieve the name of cuurent ODI user using ODI variable

Hi Guru,

It is possible to Retrieve the name of cuurent ODI user using ODI variable.

Define a variable with the following sql statement in the refreshing tab

select USER_NAME from SNP_SESSION where SES_NO=<%odiRef.getSession()%>

Thanks Guru
Ram Kumar Lanke

ODI variables value concatination

Hi Guru,

It is possible to concatenate the values of two odi variables by using the third variable

In the refreshing tab of the third variable write
the following sql statement

select '#PROJ1.VAR1'||'#PROJ2.VAR2' from dual

use this third variable to store the concatenated value

Thanks Guru
Ram kumar Lanke

Displaying ODI variable in operator log

Hi Guru,

There are two ways to display variable values in operator log.
This technique would allow the recording of variable values during the runtie and would be useful for execution of audit operations.

Solution 1:

It is possible to specify the "-SESSION_NAME" parameter in the OdiStartScen tool.

In this case pass the value of the ODI variable to this parameter for display in ODI journal.

solution 2:

A following jython script step in a procedure or in a knowledge module is used to write the variable values to operator log

a='Table='+'#variablename'
raise(a)

Note: this jython script need ignore errors checkbox to be checked in the step.
Do not forgot that such an integration interface must be called from an ODI package including steps which declare and and refresh odi variable

Thanks Guru
Ram kumar Lanke

ODI variables and Jython scripts

Hi Guru,

It is possible to pass the file or name of the file to jython without hardcoding it into the jython procedure.

The following is an example

fsrc=open('#Myvar.txt','r')
fsrc=open('#Myvar_new.txt','r')

Make sure that you are using expression editor to write variable

Thanks Guru
Ram kumar Lanke

Jython script - ODI variable

Hi Guru,

There is no direct way to assign a value to the ODI variable through a jython script.

This is performed by using JDBC connections to the appropriate database inserting/updating the table field which is used to store the value.

Note : the database table used to store the values is an application based table but not an internal repository table

The value inserted into the table may then be accessed in a package step made up of an ODI variable set up in the Refresh mode.

Thanks Guru
Ram Kumar Lanke

Using ODI variables

Hi Guru,

Always use ODI variable to lower Maintenance costs

You can use ODI variable in the follwoing

1. for modifying topology objects

2. for modifying knowledge modules

3. for modifying models and data stroes

4. for modifying varaibles

5. modifying sequences

6. modifying user defined functions

7. modify integration interfaces

8. modify procedures

9. modify packages

Note: if the variable is used in integration interface it is necessary to insert a "declare variable" step at the beginning of the ODI package

Thanks Guru
Ram kumar Lanke

FRIDAY, 20 MAY 2011

ODI variables in ODI API tools

Hi Gurus,

When ever you are trying to use variable in odi tools

enclose the odi tool api parameter with double quotes

For example
instead of the following code

OdiFileDelete "-DIR=c:\ram\file" -FILE=#file_name

use the following code

OdiFileDelete "-DIR=c:\ram\file" "-FILE=#file_name"

Thanks Guru
Ram Kumar Lanke

ODI variables can not be used in substitution methods such as getObjectName

Hi guru,

Odi variables can not be used in substitution methods such as getObjectName.

This is because due to the way the ODI source code parser works.

Thanks Guru
Ram kumar Lanke

The use of odi variables to set values to procedure or knowledge module option

Hi Guru,

ODI variables can be used to set the value of a procedure or knowledge module implementing an OPTION initialized with the value stored in the ODI variable.

The method is follows

1. Define the MYVAR global variable or project variable and set the corresponding refresh statement as required.

2. Modify the knowledge module by adding the MYOPTION option

3. Define the integration interface and on the flow tab set the MYOPTION to

#GLOBAL.MYVAR

4. Define a package with the following steps:

step 1 : Declare or Refresh variable MYVAR

step 2: Execute the above integration interface

5. Execute the above package.

Then the knowledge module option will take the value stored in the MYVAR variable.

Thanks Guru
Ram kumar Lanke

Dynamically set an integration interface source set name at run time

Hi Guru’s,

Hope you are fine.

Today let me share very important concept of ODI called using Variable as Resource name.

Suppose if we have multiple files of same structure to be loaded into a single target table, then we need not to use multiple interfaces.

Instead we can use a single interface to load all the flat files of same structure into the single target table.

I am going to try to explain the procedure with following steps

Suppose if we have three Flat files namely EMP1.txt, EMP2.txt and EMP3.txt to be loaded into TRG_EMP table of ORACLE database.

Before going to do this, you need to learn first how to load a single source flat file to a target table of oracle database.

To do the above please go through the following link provided by oracle by examples

ODI11g: Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table

Using the above procedure load the EMP.txt file to

EMP table .

you can download the required files from the following link

Download source files

Now follow the following steps.

First we need to create a table using the following query

CREATE TABLE SRC_FILE_DETAILS

( FILE_NAME VARCHAR2(10 BYTE)

);

Then load the required file names into this table.

INSERT INTO src_file_details values 'EMP1'

INSERT INTO src_file_details values 'EMP2'

INSERT INTO src_file_details values 'EMP3'

Now create three variables with the following deatails

1) Name: count

Data type: Numeric

Action : latest Value

2) Name: Files_Count

Datatype: Numeric

Action: latest Value

Refreshing: select count(*) from src_file_details

3) Name: FILE_NAME

Datatype: Alphanumeric

Action: Latest value

Refreshing:

SELECT FILE_NAME FROM (SELECT FILE_NAME,ROWNUM RN FROM SRC_FILE_DETAILS) WHERE RN=#Project_Name.count

Note: Please replace Project_Name with your project name

Now open the source data store which is participating in the interface for populating target and replace the resource name with #Project_Name.FILE_NAME.txt

Now we are going to design a package looks like below:

Step1:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Step2: Drag and drop the FILE_NAME variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Step name: GetTheFileName

Type: Refresh variable

Step3:

Just drag and drop the interface and change the step name to PopTrgWithThisFileName

Step4:

Drag and drop the count variable.

Click on it. Change the properties as shown in the following

Step5:

Drag and drop the Files_Count variable.

Click on it. Change the properties as shown in the following

Step type : Refresh Variable

Step6:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Drag and drop the Files_Count variable.

Click on it. Change the properties as shown in the following

Step type : Refresh Variable

Step6:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Please replace Proj_Name with your project name

Save all

Run the package

That it.

Now life is so easy.

You can load a table with several files with single interface.

Thanks Guru’s

Ram Kumar Lanke

Using ODI variables to dynamically specify Table column names

Hi guru,

When mapping between source and target data stores, it is possible to parametrize column names usingODI variables ..

By defining an ODI variables such as MyVar and by referencing it in a mapping such as:

TARGET.COL1 = SOURCE.#MyVar

ODI will substitute for it at run time with its value.

Note:

Take care , however , in assigning a valid column name from the SOURCE table to the variable

Thanks Guru
Ram kumar lanke

Using ODI variables to generate Unique URL of a data server during package run time

Hi Guru,

When using variables in the JDBC url the following method is recommended

The data server connection is established at the beginning of the session execution
for example a package

This requires that the variables used in an url and physical schema must be resolved in a different session so that session can use it values.

At the beginning of the session execution, ODI will retrieve the variable's values stored in snp_var_data which from the variable is last refreshed .

This could be from when the package ran last time.

That's why it feels like its always loading previous server data.

Therefore the following is the good method to do this

1. create a child package with the following steps"

step 1 : declare v_loop variable

step 2: declare v_src_physical schema

step 3: declare v_dataserver

step 4: declare v_tar_physical_schema

step 5: i_interface

step 6: declare v_check

Generate a scenario for the above package , this scenario is called in the next package a a child package

2. Create another package with the following steps

step 1 : set v_loop variable

step 2: refresh v_src_physical schema

step 3: refresh v_dataserver

step 4: refresh v_tar_physical schema

step 5: Execution of above child scenaria

step 6: refresch v_check

step 7: evaluate v_check

step 8: set v_loop

step 9: back to refresh v_src_physical schema step

Thanks guru

Ram kumar lanke

Conditionaly replace data in a target data strore

Hi guru,

When executing an interface and using a filter to define subsets of data for intergration, we would like to ensure that the integration interface does not execute the IKM ( and associated TRUNCATE target step) when no records are loaded in the source data stores.

The simplest way of achieving this is to separate the logic into a set of package steps including an ODI variable to store the number of records transferred and to serve as an evaluation step.

The package contains the following steps:

1. Declare ODI variable step

2. an integration interface which transfers source records into a temporary table.

3. A refresh variable step using a sql statement which counts the number of records that have been transferred to the temporary table

4. Evaluate the variable and if no records the package will terminate. other wise the package will execute the following step.

5. Execute an integration interface to load data from the temporary table to the ultimate target table.

Thanks Guru

Ram kumar Lanke

Recommendations of ODI variables to store date values

Hi Guru,

It is highly recommended by the Oracle to use Alphanumeric variable to store date values.

And to refresh this type of variable use the following technology dependent statement.

ORACLE:

SELECT sysdate FROM dual

Microsoft SQL server:

SELECT getdate()

Later when the variable is used in another step, use syntax such as

Oracle:

UPDATE MY_TABLE WHERE MY_DATE=TO_DATE('#PROJ.MY_VAR1','DD-MM-YYYY HH24:MI:SS')

If an ODI 'Date' type variable is required keep in mind that the value will be stored in the following java format

yyy-mm-dd hh:mm:ss.o

When you need to retrive the value from the variable within an integration interface, you are required to alter the date format using SQL instructions inorder to match the fromat expected by RDBMS.

Thanks GURU

Ram kumar Lanke

Best practice to use ODI variables

Hi Guru,

Always try to use the expression editor if possible to retrieve the value of ODI variables.

For example,

#projectcode.variablename

Thanks Guru
Ram kumar Lanke

Numbe Format Exception Error: Comparing ODI variable values

Hi Guru,

When comparing the value of one ODI variable with another in an ODI package, the following error may signaled

java.lang.NumberFormatException:#<project-name>.<variable-name>

In this case you must prefix the variables name with its project code or with the GLOBAL keyword.

You also may need to declare the variable in the package.

Thanks Guru

Ram kaumar Lanke

Running parallel ODI interfaces

Hi Guru,

We know that ODI derives the names of temporary tables from the name of the datastore.

As a result conflicts between multiple simultaneous integration interfaces can arise.

In order to avoid such problems when we are executing parallel scenarios we have to modify the data server to use the ODI Package variables to store prefixes to be used for integration interfaces.

The following steps explain how to do it.

1. Go to the topology manager

2. Edit the physical schema of this target data store

3. In the definition tab,

Replace the existing values set for temporary table prefixes with the name of the ODI package variables.

4. Launch your integration interface from a package in which the ODI variable is declared at the begining.

Thanks Guru,

Ram kumar Lanke

FRIDAY, 6 MAY 2011

MULTIPLE SOURCE FILES-MULTIPLE TARGET TABLES-SINGLE INTERFACE

Hi Guru’s,

We have seen in the last post how to load multiple source files of same structure into a single table.

you can refer it by clicking here

Suppose if we want to load multiple source files into multiple target tables.

Then please just follow the same procedure which I explained in the previous post and do a little change to the target data store.

Open the source data store which is participating of the interface and replace the resource name with #Project_Name.FILE_NAME

Note : Project_Name means your project name

That’s it .

Thanks Guru's
Ram Kumar Lanke

MULTIPLE FILES - SINGLE TARGET TABLE-SINGLE INTERFACE

Hi Guru’s,

Hope you are fine.

Today let me share very important concept of ODI called using Variable as Resource name.

Suppose if we have multiple files of same structure to be loaded into a single target table, then we need not to use multiple interfaces.

Instead we can use a single interface to load all the flat files of same structure into the single target table.

I am going to try to explain the procedure with following steps

Suppose if we have three Flat files namely EMP1.txt, EMP2.txt and EMP3.txt to be loaded into TRG_EMP table of ORACLE database.

Before going to do this, you need to learn first how to load a single source flat file to a target table of oracle database.

To do the above please go through the following link provided by oracle by examples

ODI11g: Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table

Using the above procedure load the EMP.txt file to

EMP table .

you can download the required files from the following link

Download source files

Now follow the following steps.

First we need to create a table using the following query

CREATE TABLE SRC_FILE_DETAILS

( FILE_NAME VARCHAR2(10 BYTE)

);

Then load the required file names into this table.

INSERT INTO src_file_details values 'EMP1'

INSERT INTO src_file_details values 'EMP2'

INSERT INTO src_file_details values 'EMP3'

Now create three variables with the following deatails

1) Name: count

Data type: Numeric

Action : latest Value

2) Name: Files_Count

Datatype: Numeric

Action: latest Value

Refreshing: select count(*) from src_file_details

3) Name: FILE_NAME

Datatype: Alphanumeric

Action: Latest value

Refreshing:

SELECT FILE_NAME FROM (SELECT FILE_NAME,ROWNUM RN FROM SRC_FILE_DETAILS) WHERE RN=#Project_Name.count

Note: Please replace Project_Name with your project name

Now open the source data store which is participating in the interface for populating target and replace the resource name with #Project_Name.FILE_NAME.txt

Now we are going to design a package looks like below:

Step1:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Step2: Drag and drop the FILE_NAME variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Step name: GetTheFileName

Type: Refresh variable

Step3:

Just drag and drop the interface and change the step name to PopTrgWithThisFileName

Step4:

Drag and drop the count variable.

Click on it. Change the properties as shown in the following

Step5:

Drag and drop the Files_Count variable.

Click on it. Change the properties as shown in the following

Step type : Refresh Variable

Step6:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Drag and drop the Files_Count variable.

Click on it. Change the properties as shown in the following

Step type : Refresh Variable

Step6:

Drag and drop the count variable on to the diagram tab of package.

Click on it. Change the properties as shown in the following

Please replace Proj_Name with your project name

Save all

Run the package

That it.

Now life is so easy.

You can load a table with several files with single interface.

Thanks Guru’s

Ram Kumar Lanke

www.odiguru.com

Read more »

Welcome to our website

Sunday, 6 November 2011

Thursday, 13 October 2011

Creating the Master Repository using ODI 11G studio

Tuesday, 11 October 2011

FRIDAY, 20 MAY 2011

Using the above procedure load the EMP.txt file to

EMP table .

you can download the required files from the following link

Now follow the following steps.

FRIDAY, 6 MAY 2011

Using the above procedure load the EMP.txt file to

EMP table .

you can download the required files from the following link

Now follow the following steps.

About Me

Followers

Blog Archive