Welcome to our website

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.

Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. ed ut perspiciatis unde omnis iste.

Thursday 13 October 2011

Creating the Master Repository using ODI 11G studio


Creating the Master Repository using ODI 11G studio



Creating the master repository creates an empty repository structure and seeds metadata (for example, technology definitions, or built-in security profiles) into this repository structure.


Step1: First you need to create schema for Master Repository
Open the sql developer
And create new schema with the name MasterRepo
Grant the required permissions


 Step2: Start the ODI Studio
 Step3: To create the master repository:

Open the New Gallery by choosing File > New.

In the New Gallery, in the Categories tree, select ODI.
 step4: Select from the Items list the Master Repository Creation Wizard.

Click OK.

The Master Repository Creation wizard opens.

 step 5:

Specify the Database Connection parameters as follows:

Technology: From the list, select the technology that will host your master repository. Default is Oracle.

JDBC Driver: The driver used to access the technology, that will host the repository.

JDBC URL: The URL used to establish the JDBC connection to the database.

Note that the parameters JDBC Driver and URL are synchronized and the default values are technology dependant.

User: The user ID / login of the owner of the tables (for example, odim).

Password: This user's password.

DBA User: The database administrator's username

DBA Password: This user's password



Note: It is strongly recommended that this ID is unique and not used for any other master repository, as it affects imports and exports between repositories


  step6:
Specify the Repository Configuration parameters as follows:

ID: A specific ID for the new repository, rather than the default 0.



Click Test Connection to test the connection to your master repository.

The Information dialog opens and informs you whether the connection has been established. If the connection fails, fix the connection to your master repository before moving to next step.

Click Next.

Do one of the following:



step 7:

Select Use ODI Authentication to manage users using ODI's internal security system and enter the following supervisor login information:
Properties Description
Supervisor User User name of the ODI supervisor.
Supervisor Password This user's password
Confirm Password This user's password



Click Next.




 step 8:

Specify the password storage details:

Select Internal Password Storage if you want to store passwords in the Oracle Data Integrator master repository



 step 9:


In the Master Repository Creation Wizard click Finish to validate your entries.
www.odiguru.com

Creating the Master Repository using ODI 11G studio



Creating the master repository creates an empty repository structure and seeds metadata (for example, technology definitions, or built-in security profiles) into this repository structure.


Step1: First you need to create schema for Master Repository
Open the sql developer
And create new schema with the name MasterRepo
Grant the required permissions


 Step2: Start the ODI Studio
 Step3: To create the master repository:

Open the New Gallery by choosing File > New.

In the New Gallery, in the Categories tree, select ODI.
 step4: Select from the Items list the Master Repository Creation Wizard.

Click OK.

The Master Repository Creation wizard opens.

 step 5:

Specify the Database Connection parameters as follows:

Technology: From the list, select the technology that will host your master repository. Default is Oracle.

JDBC Driver: The driver used to access the technology, that will host the repository.

JDBC URL: The URL used to establish the JDBC connection to the database.

Note that the parameters JDBC Driver and URL are synchronized and the default values are technology dependant.

User: The user ID / login of the owner of the tables (for example, odim).

Password: This user's password.

DBA User: The database administrator's username

DBA Password: This user's password



Note: It is strongly recommended that this ID is unique and not used for any other master repository, as it affects imports and exports between repositories


  step6:
Specify the Repository Configuration parameters as follows:

ID: A specific ID for the new repository, rather than the default 0.



Click Test Connection to test the connection to your master repository.

The Information dialog opens and informs you whether the connection has been established. If the connection fails, fix the connection to your master repository before moving to next step.

Click Next.

Do one of the following:



step 7:

Select Use ODI Authentication to manage users using ODI's internal security system and enter the following supervisor login information:
Properties Description
Supervisor User User name of the ODI supervisor.
Supervisor Password This user's password
Confirm Password This user's password



Click Next.




 step 8:

Specify the password storage details:

Select Internal Password Storage if you want to store passwords in the Oracle Data Integrator master repository



 step 9:


In the Master Repository Creation Wizard click Finish to validate your entries.
www.odiguru.com



Tuesday 11 October 2011

ODI Variables


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.
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

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Blogger