Variable/dynamic based options in ODI

Why variable/dynamic based option ..?

 Lets take a very simple example for this...

Suppose user wants to perform loading from source to target. There are two types of loading

"Initial load" ( first time) target table will be empty & only insert will be performed on target.

"Regular Load" it will be performed after initial & both insert or update may happen on target.

If developers  create option "load_option"  then at a time he can perform only one type of load either "initial" or "regular"

OR

 User have to develop two interfaces & run either of one depending upon type of load. But in this two interfaces are required ( code replication & maintenance overhead)

This can be achieved through a single interface & create variable/dynamic options & its value will be supplied at the time of execution.

Step1: Create A variable "v_load_type" for load type.
In refresh tab :select '#v_load_type' from dual 

Step2. Create option v_option in IKM.

Step3 .Open IKM   ( oracle incremental update)  ,

Add/replace existing step  in KM

1.    /* if regular then do update else don't apply update */

  declare

  dummy_var varchar2(10);

   begin

    <@ if ("regular".equals("<%=odiRef.getOption("v_option")%>")) { @>

/* Put the script code when "v_option" value is "regular" or "not null" according to option condition ,which user want to apply*/

<@ } else { @>
 /*  put else condition script code which user want to apply */

 select null into dummy_var from dual;

<@ } @>

end;

 Step4.

In Interface put Variable #v_load_type in v_option

Step5 Make a package 

a. Refresh variable v_load_type

b. Next step add interface.

c. Create a package scenario with variable parameter v_load_type

Perform "Initial load" : 

Pass parameter 'initial' to the scenario ,only Insert script will be generated/update will not take place

So Finally an option can be made dynamic through appending code

declare

dummy_var varchar2(10);

begin

 <@ if ("regular".equals("<%=odiRef.getOption("v_option")%>")) { @>

--------- Put the code to be genetaed or executed...

<@ } else { @>

select null into dummy_var from dual;

<@ } @>

end;

Options in ODI KM's/Procedures

Loading of File: End of line is not specified

Purpose of options in ODI: Options plays a vital role & though this conditional ( just like if,cases in sql) based queries/codes can be generated for loading purposes.

I have a scenario where my source is a File  ( fixed file -position based records) & its EOL( end of line) is not specified). All the records are appearing in the same line.
The length of record is 8 so below text contain two records
ABCD1234QRST5421

Layout or record C1(1:2),C2(3:4),C3(5:8)
record1: ABCD1234
record2: QRST5421

create datastore for file ABC

Now I want to load the records using LKM file to Oracle(sqlldr).

Step1: First Add an option (FIXED_REC_LENGTH) in LKM which will hold length of record.
Step2: Go to "Generate CTL file" command in LKM & change the code.
If
 Fixed record length option is not null  then it will generate control file with fixed record length
else
It will perform generate normal ODI control file code.

Apply the changes in control file & save it.

Now Create a interface for load file to oracle & in the flow diagram set option "FIXED_REC_LENGTH"
as 8

Choose appropriate IKM ( sql control append) & execute the interface.

CTL generated through LKM is


SnpsOutFile "-File=C:/SNAPON/ABC.ctl"

OPTIONS (
SKIP=0,
ERRORS=0,
DIRECT=FALSE
)
LOAD DATA
INFILE "C:/SNAPON/EOL.txt"  "FIX 8"
BADFILE "C:/SNAPON/ABC.bad"
DISCARDFILE "C:/SNAPON/ABC.dsc"
DISCARDMAX 1
INTO TABLE SNAPON_W.C$_0ABC
(
C2_C1 POSITION(1:2),
C1_C2 POSITION(3:4),
C3_C3 POSITION(5:8)
)

Check target table & 2 records inserted through load.

In next post will we discuss "Variabale" based "Options".

Variable/Dynamic based data sever connection in ODI topology

We can design Variable based connection in ODI Topology manager.

Why variable based connection...?

A. lets say user have requirement  to create some common components ( e.g. Knowledge modules) in ODI which can used across all the different products or projects.

B. Now different projects have different data severs & different data severs ( physical & logical) will be associated with different environments  like production, development.

C. Usually in Any software domain ( banking,automobile,retail etc) developers tries to develop a component which can be used across all the products/projects. 

Example:

 Lets assume source & target databases for 'n' number of projects & all products or projects have same type of  database tables DDL structure.

Source : Oracle & Target : Postgresql

so 'n' number of data base connections required in topology manager ? 

'n' number of interfaces or packages required to perform load to target ?

NO, just one connection (topology manager) & one package(designer) to perform load.

Create a metadata table 'Metadata' in oracle which will contain all the source & target database details like ( sever name,port,etc) & add one column for Primary Key ( numeric)  

Create variables such as, for source schema V_SRC_SCHEMA & V_SRC_database, V_SRC_port,etc & similarly for target V_target .
Create a variable for primary key column defined in metadata table & refresh all source & target variables on the basis of Primay Key. 

Create only one connection in topology manager for source & target with the variables created above & create variable based logical schema too...
Create Models with variable based logical schema
Now create a interface to load data from source to target

Finally put the variables ( Refresh Mode) & Interfaces in the final package & generate Scenario with variable parameter Primary key .

Now Scenario is ready for use, Just pass the of the primary key value of the connection sever ( defined in meta data table) to the scenario & DATA will be loaded from SOURCE --> TARGET for that product/project................

In next phase i will attach screenshot for this ............