Thursday Feb 09, 2012

ODI 11g – Interface Builder

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional based matching, like names ignoring case and so on. Here we will see another example (download OdiInterfaceBuilder.java) showing a different aspect using a control file which describes the interface in simple primitives which drives the creation. The example uses a tab delimited text file to control the interface creation, but it could be easily taken and changed to drive from Excel, XML or whatever you wanted to capture the design of the interface.

The interface can be as complete or incomplete as you’d like, so could just contain the objects or could be concise and semantically complete.

The control file is VERY simple and just like ODI requests the minimal amount of information required. The basic format is as follows;

Directive Column 2 Column 3 Column 4 Column 5
source <model> <datastore>    
   can add many        
target <model> <datastore>    
mapping <column> <expression>    
   can add many        
join <expression>      
   can add many        
filter <expression>      
    can repeat many        
lookup <model> <datastore> <alias> <expression>
   can add many        

So for example the control file below can define the sources, target, joins, mapping expressions etc;

source    SCOTT    EMP
source    SCOTT    DEPT
target    STG_MODEL_CASE    TGTEMP
mapping    ENAME    UPPER(EMP.ENAME)
mapping    DNAME    UPPER(DEPT.DNAME)
mapping    DEPTNO    ABS(EMP.EMPNO)
join    EMP.DEPTNO = DEPT.DEPTNO
lookup    SCOTT    BONUS    BONUS    BONUS.ENAME = EMP.ENAME
filter    EMP.SAL > 1
mapping    COMM    ABS(BONUS.COMM)

When executed, this generates the interface below with the join, filter, lookup and target expressions from the file.

You should be able to join the dots between the control file sample and the interface design above.

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceBuilder;

java –classpath <cp> OdinterfaceBuilder jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK DEMO1 <myinterfacecontrolfile.tab

The interface to be created is passed from the command line. You can intersperse other documentation lines between the control lines so long as the control keywords in first column don’t clash.

Anyway some useful snippets of code for those learning the SDK, or for those wanting to capture the design outside and generate ODI Interfaces. Have fun!

Wednesday Feb 08, 2012

ODI 11g – More accelerator options

A few more options added into the interface accelerator that I blogged about earlier here in initial post and a later one here. Added options for doing position based and case sensitive/insensitive options. These were simple changes added into the auto map class. You can now find the latest updates below;

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceAccelerator;

java –classpath <cp> OdinterfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK <icontrol.csv

In the automapper I created a couple of options that can drive the accelerator, it supports;

  • positional based match (match columns by position from source to target)
  • exact match case sensitive  (match EMPNO with EMPNO, but not empno with EMPNO)
  • exact match case insensitive (match EMPNO with empno)
  • src/target ends with sensitive/insensitive (match PFX_empno with empno/EMPNO)
  • src/target starts with sensitive/insensitive (match empno_col with empno/EMPNO)

Note, you can also use the “diagrams” in the models to greatly accelerate development if source and targets have the same structure – if not then you have to go through the SDK route above if you want to accelerate.

Saturday May 21, 2011

OWB - Mapping Scripting Accelerator

Carrying on from the ODI posts on building lots of simple interfaces rapidly using the SDK, we can see here how to use the OMB scripting commands in OWB to build maps in an identical manner as the post here. The script takes as input the same control file used in the post on the ODI SDK which specifies the mapping name, source module and table and the target module and table name. The script is a simple tcl loop over the file which invokes OMBCREATE MAPPING to create the mapping in OWB.

set f [open /temp/icontrol.csv]
while {1} {
  set line [gets $f]
  if {[eof $f]} {
      close $f
      break
  }
  set toks [split $line ","]
  set g_map_name [lindex $toks 0]
  set g_src_obj_name [lindex $toks 2]
  set g_tgt_obj_name [lindex $toks 4]
  set G_SRC_MODULE [lindex $toks 1]
  set G_TGT_MODULE [lindex $toks 3]
  set G_SRC_OBJ_TYPE TABLE
  set G_TGT_OBJ_TYPE TABLE
  set g_from_group INOUTGRP1

  # a prefix so that we can differentiate from possibly adding the same name as source and target
  set spfx "S_"
  set tpfx "T_"

  OMBCREATE MAPPING '$g_map_name'\
  ADD $G_SRC_OBJ_TYPE OPERATOR '$spfx$g_src_obj_name' BOUND TO $G_SRC_OBJ_TYPE

'$OMB_CURRENT_PROJECT/$G_SRC_MODULE/$g_src_obj_name'\
  ADD $G_TGT_OBJ_TYPE OPERATOR '$tpfx$g_tgt_obj_name' BOUND TO $G_TGT_OBJ_TYPE

'$OMB_CURRENT_PROJECT/$G_TGT_MODULE/$g_tgt_obj_name'\
  ADD CONNECTION FROM GROUP '$g_from_group' OF OPERATOR '$spfx$g_src_obj_name'\
  TO GROUP 'INOUTGRP1' OF OPERATOR '$tpfx$g_tgt_obj_name' BY NAME

}

One of the nice things in the MAPPING related commands is the group level mapping – see the ‘CONNECTION FROM GROUP  …  TO GROUP… BY NAME‘ as used above, there are also options (see the groupToGroupConnectType clause) from scripting as in the UI for ignoring suffixes/prefixes and being case insensitive.

name_mapper_odi_owb

So for example to ignore target column prefixes of STG_ I can augment the BY NAME portion of the command above to be;

..... BY NAME IGNORE TARGET_PREFIX 'STG_'

Which in the UI would be defined as....

name_mapper_odi_owb

This will let me match a column name of EMPNO on source with a target name of STG_EMPNO on the target. Anyway that's a quick run through of something that popped up recently.

About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today