Tuesday Jun 21, 2011

OWB – OWBLand on SourceForge

There are a bunch of interesting utilities that are either experts or OMB scripts that are hosted on SourceForge by some keen OWB users (see the home here). One of the main initiatives has been an Excel to OWB ‘one click ETL’ utility, which looks to have had a fair amount of code added, there is an example but its kinda light on documentation, but does look like it covers quite a lot. One of the nice things about SourceForge is that you can peek into the statistics and see what kind of activity has gone on, from last August there have been a bunch of downloads with a big peak last November…

owbland

Another utility that is there is one to generate OMB from a mapping definition, a bunch of useful stuff there - http://sourceforge.net/projects/owbland/files/

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.

Tuesday Jun 26, 2007

OWB Model Introspection

[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