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.

Monday Aug 13, 2007

Control Flow in a Map

[Read More]
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