Recovering OWB mapping with map_reconstruct.tcl script

There is another case for using MAP_RECOSTRUCT.tcl script (except using for export/import mappings between environments). Sometime dueto OWB bug/OWB repository corruption it is impossible to open map for editing in Design Center (in my practice this happened twice as I remember). The problem usually in single operator or attribute.
In this case we can use MAP_RECOSTRUCT.tcl script for identification problem object in mapping - run this script against such corrupted mappings and script outputs in generated file error message which allow identify problem object. Release 0.0.2 of MAP_RECOSTRUCT.tcl script already contains code which output into target file comments if error raised (and script won't stop!) during extraction properties for attribute - so it is possible to get working copy of corrupted mapping...

MAP_RECONSTRUCT script update

I slightly modified script (and OWB Expert) for generating OMB script for recreating mapping, link for download  script and OWB expert

All modifications are errors correction:
- escaping additional character in expressions and join/filter conditions
- supporting key lookup operator in OWB 11gR2 (in this release OMB returns different from previous releases type name for key lookup)
- added processing of error during extraction attribute properties (in case of error attribute will be created with NUMBER datatype, in generated script inserted comment with description of problem)

For first two identified and corrected bugs thanks to Patrick (unfortunately I don't know him full name)!

OneClickETL release 0.1.1

New release of OneClickETL script available for download - OneClickETL-0.1.1.tcl.
Now it allows generate mappings with SCD Type 1 loading type.

Updated Excel file OneClickETL_OE2SH.xls contains additional sheet for SCD1 mapping.

Example of specification for SCD1 mapping

To generate SCD1 mapping specify in C1 cell "SCD1" value. In D column you need to specify role for target columns:
- PRIMARYKEY - this column will be used for matching condition
- TRIGGEREDATTRIBUTE - this column willbe inserted and updated
- empty cell - this column will be only inserted

Prototype of OneClickETL script

ATTENTION! Script drops mappings specified in Excel file!

I released prototype of script which generate set of OWB mappings based on mapping specification from MS Excel file.
Before running this script you need to copy Java Excel API library (jxl.jar file) to OWB_HOME\owb\lib\ext directory.
Java Excel API is an open-source project hosted on sourceforge.net, homepage of this project http://jexcelapi.sourceforge.net/

You can download OneClickETL script from https://sourceforge.net/projects/owbland/files/OneClickETL/ (tcl file is script itself, and also under this folder you can find example of Excel file). Before running script change path to Excel file (value of excelFileName variable at the begining of script).
There are two types (top left cell A1 of worksheet specify it type) of Excel worksheets - the first type (DATAWAREHOUSE) specify name of existing target module, second (MAPPING) - mapping specification.
Cell A2 - it is name of object (name of target module and name of mapping). You must fill A3 cell (but currently script doesn't use value from this cell).
It is enough enter only first line for DATAWAREHOUSE sheet type. In MAPPING sheet type other rows (starting from second) describe mapping - column B contains source attribute column C - target attribute (format of attribute specification is {module name}.{table name}.{attribute name}).


To simplify learning and experimenting with OneClickETL script I also uploaded to OWBLAND Sourceforge project repository two additional files - ONECLICKETL-OE2SH.mdl with exported OWB project (ONECLICKETL) with two Oracle modules (with names OE and SH - these modules contain table definitions from corresponding standard Oracle database examples), and OneClickETL_OE2SH.xls, which contains specifications for two mappings based on this OWB project.



Example of Excel sheet with info about target module

Example of Excel sheet for mapping with mutiply source table

Generated mapping for loading SH.PROMOTIONS table (specification with single source tables)

Generated mapping for loading SH.PRODUCTS table (specification with two source tables)

Example of using Tk GUI (swank) in OMB*Plus

It is a preview of GUI configurator for SCD2 mapping generator script.

namespace eval ::owbeg {}

set currContext [OMBDCC]
set currCtxType [lindex $currContext 0]
set currContext [lindex $currContext 1]
#check current context type?
set tgtTableList [OMBLIST TABLES]
set tgtSeqList [OMBLIST SEQUENCES]

set ::owbeg::V_MAP_NAME MAP_TEST
set ::owbeg::P_SRC_TABLE [file join $currContext [lindex $tgtTableList 0]]
set ::owbeg::G_PARTYID_LIST bk1
set ::owbeg::P_TGT_TABLE [file join $currContext [lindex $tgtTableList 0]]
set ::owbeg::G_DET_PARTYID_LIST partyid
set ::owbeg::G_DET_HISTID_NAME HIST_ID
set ::owbeg::G_DET_STARTDATE_NAME start_date
set ::owbeg::G_DET_FINALDATE_NAME end_date
set ::owbeg::P_HISTID_SEQ seq_1

set tgtTableColumns [OMBRETRIEVE TABLE '$::owbeg::P_TGT_TABLE' GET COLUMNS]

wm title . "Parameters for SCD2 mapping generator"

frame .fttl
frame .fbot
frame .fsrc
frame .ftgt

label .fttl.lb_mapname -text "Mapping name"
entry .fttl.entr_mapname -textvariable ::owbeg::V_MAP_NAME -width 40
grid .fttl.lb_mapname
grid .fttl.entr_mapname

button .fbot.butok -text "Generate" -command "run_gen_proc"
grid .fbot.butok


label .fsrc.lb_srctab -text "Source table"
button .fsrc.but_srctab -text "Choose" -command "chooseSrcTable"
entry .fsrc.entr_srctab -textvariable ::owbeg::P_SRC_TABLE -width 25
label .fsrc.lb_srcbuskey -text "BusinessKey column"
jcombobox .fsrc.cb_srcbuskey -variable ::owbeg::G_PARTYID_LIST -width 10
eval ".fsrc.cb_srcbuskey item append $tgtTableColumns"
grid .fsrc.lb_srctab .fsrc.but_srctab -sticky wn
grid .fsrc.entr_srctab -columnspan 2 -sticky w
grid .fsrc.lb_srcbuskey -columnspan 2 -sticky w
grid .fsrc.cb_srcbuskey -columnspan 2 -sticky w

label .ftgt.lb_tgttab -text "Target table"
button .ftgt.but_tgttab -text "Choose" -command "chooseTgtTable"
entry .ftgt.entr_tgttab -textvariable ::owbeg::P_TGT_TABLE -width 25
label .ftgt.lb_tgtbuskey -text "BusinessKey column"
jcombobox .ftgt.cb_tgtbuskey -variable ::owbeg::G_DET_PARTYID_LIST -width 10
eval ".ftgt.cb_tgtbuskey item append $tgtTableColumns"
label .ftgt.lb_tgthistid -text "Dimension key column"
jcombobox .ftgt.cb_tgthistid -variable ::owbeg::G_DET_HISTID_NAME -width 10
eval ".ftgt.cb_tgthistid item append $tgtTableColumns"
label .ftgt.lb_tgtstrtdate -text "Start date column"
jcombobox .ftgt.cb_tgtstrtdate -variable ::owbeg::G_DET_STARTDATE_NAME -width 10
eval ".ftgt.cb_tgtstrtdate item append $tgtTableColumns"
label .ftgt.lb_tgtenddate -text "End date column"
jcombobox .ftgt.cb_tgtenddate -variable ::owbeg::G_DET_ENDDATE_NAME -width 20
eval ".ftgt.cb_tgtenddate item append $tgtTableColumns"
label .ftgt.lb_seqname -text "Dimension key sequence"
jcombobox .ftgt.cb_seqname -variable ::owbeg::P_HISTID_SEQ -width 20
eval ".ftgt.cb_seqname item append $tgtSeqList"
grid .ftgt.lb_tgttab .ftgt.but_tgttab -sticky w
grid .ftgt.entr_tgttab -columnspan 2 -sticky w
grid .ftgt.lb_tgtbuskey -columnspan 2 -sticky w
grid .ftgt.cb_tgtbuskey -columnspan 2 -sticky w
grid .ftgt.lb_tgthistid -columnspan 2 -sticky w
grid .ftgt.cb_tgthistid -columnspan 2 -sticky w
grid .ftgt.lb_tgtstrtdate -columnspan 2 -sticky w
grid .ftgt.cb_tgtstrtdate -columnspan 2 -sticky w
grid .ftgt.lb_tgtenddate -columnspan 2 -sticky w
grid .ftgt.cb_tgtenddate -columnspan 2 -sticky w
grid .ftgt.lb_seqname -columnspan 2 -sticky w
grid .ftgt.cb_seqname -columnspan 2 -sticky w

grid .fttl -columnspan 2
grid .fsrc .ftgt -sticky n
grid .fbot -columnspan 2

proc chooseSrcTable {} {
  tk_messageBox -message "Choose source table" -type ok
}
proc chooseTgtTable {} {
  tk_messageBox -message "Choose target table" -type ok
}

proc run_gen_proc {} {
  destroy .fttl
  destroy .fbot
  destroy .fsrc
  destroy .ftgt
  puts $::owbeg::G_PARTYID_LIST
}

proc tab_dlg_choose {} {
puts "[.jd.f.tree path get]"
destroy .jd
}

proc tree_dialog {} {
jdialog .jd
frame .jd.f
button .jd.f.butok -text OK -command "tab_dlg_choose"
.jd add .jd.f
jtree .jd.f.tree
.jd.f.tree node add root "OWB"
.jd.f.tree node add -1 "Tables"
.jd.f.tree node add -1 "Views"
.jd.f.tree node add 0 "T1"
.jd.f.tree node add 0 "T2"
.jd.f.tree node add 0 "T3"
.jd.f.tree node add 0 "T4"
.jd.f.tree node add 1 "T1"
.jd.f.tree node add 1 "T1"
.jd.f.tree node add 1 "T1"
.jd.f.tree node add 1 "T1"
pack .jd.f.butok -side bottom
pack .jd.f.tree -side top
.jd configure -modal 1 -visible 1
}

# tree_dialog

proc my_dialog {} {
namespace eval ::mydlgproc {}
set ::mydlgproc::newTab ""
set newt2 ""
jdialog .jd
frame .jd.f
button .jd.f.butok -text OK -command {puts $::mydlgproc::newTab ; set ::mydlgproc::newTab aa; destroy .jd;}
.jd add .jd.f
pack .jd.f.butok -side bottom
.jd configure -visible 1
vwait ::mydlgproc::newTab
set newt2 $::mydlgproc::newTab
namespace delete ::mydlgproc
return $newt2
}

OMB*Plus extensibility - Tk GUI in OMB*Plus

One of the important feature of OWB10g R2 is integration into design client Tcl-based scripting language (OMB*Plus) enhaced with GUI componets - OWB Expert. OWB Expert allow execution of OMB script dirrectly from design client (it is possible to add new command to context menu called from project tree). GUI components play secondary role - the main purpose of these components is setting of paramters passed to OMB script, for example there is component for choosing table from specified module (so usually GUI component perform single task). In most cases for execution of simple task we needed to compose a sequence of sevaral components (choosing module -> choosing table, etc.) - it was quite inconvenient.Maybe the most interesting component is Custom Dialog (allow create dialog windows with several items like text field, label, radio button, etc.), but it has one drawback - this window is non-interactive (there is no way to specify reply for user action, like choosing new value in list, enabling/disabling check box, etc.).

Tcl interpreter allows to write GUI application with Tk extension. And as I explored there is GUI extension for Jacl Tcl-interpreter (OMB*Plus is based on Jacl/tcljava). Name of this extension is SWANK.

Link to swank

Last jar-file of swank compiled with lates Java compiler, so the best way to experiment - use last OWB release (OWB11g R2 which use Java 6 for execution).
Configuration steps:

• copy swank.jar file to OWB_HOME\owb\lib\ext directory;
• add to ombinit.tcl file (OWB_HOME\owb\bin/admin directory) at the end line
  source -url resource:/com/onemoonscientific/swank/library/init.tcl.

OMB*Plus example with Tk widgets:

namespace eval ::owbland {}


OMBCC '/'
set projList [OMBLIST PROJECTS]
set ::owbland::res "a"
jcombobox .combproj -variable ::owbland::res -command "procCSEL"
foreach proj $projList {
.combproj item append $proj
}
pack .combproj -side top


listbox .projlist -height 5
pack .projlist -side bottom


wm title . "Super;) Module Viewer"
wm geometry . 400x250


proc procCSEL {} {
  #puts "selected"
  OMBCC '/$::owbland::res'
  if {[catch {set modList [OMBLIST ORACLE_MODULES]} err]} {
    puts $err
  } else {
    #puts $modList
    set lstSz [.projlist size]
    .projlist delete 0 [expr {$lstSz-1}]
    foreach mod $modList {.projlist insert end $mod}
  }
}

OMB*Plus extensibility - commands redefinition at startup with ombinit.tcl

There is simpler solution for defining commands during OMB*Plus startup - at startup OMB*Plus executes ombinit.tcl script located in OWB_HOME\owb\bin\admin directory.
This file contains definition of several procedures and version from OWB10g R2 also contains code for correcting of multiple path in TCLLIBPATH. In previous post (part 1 of OMB*Plus extensibility) I said that it is impossible to define more than one path in TCLLIBPATH - but with this procedure in ombinit.tcl TCLLIBPATH allow multiple paths sepparated by space.

Here is another addition to previous post - how to modify auto_mkindex to work.
As I said tcljava implementation of auto_mkindex contains a bug (it exist even in last tcljava release).
auto_mkindex is a general Tcl procedure which defined in init.tcl file (in tcljava implementation this file located in jacl.jar archive).
The first we need extract init.tcl file from archive - start command line in Windows and execute commands (suppose OWB installed in c:\oracle\owb10g_r2):

C:\>cd \oracle\owb10g_R2\owb\lib\int
C:\Oracle\owb10g_R2\owb\lib\int>..\..\..\jdk\bin\jar -xf jacl.jar tcl/lang/library/init.tcl
C:\Oracle\owb10g_R2\owb\lib\int>

As result we get file C:\Oracle\owb10g_R2\owb\lib\int\tcl\lang\library\init.tcl, we need to modify it  -
find in file line

puts $f $index nonewline

and replace it with

puts -nonewline $f $index

Now from command line move modified file back to archive

C:\Oracle\owb10g_R2\owb\lib\int>..\..\..\jdk\bin\jar -uf jacl.jar tcl\lang\library\init.tcl

Now auto_mkindex works under OMB*Plus:

OMB*Plus: Release 10.2.0.3.33
Copyright (c) 2000, 2006, Oracle. All rights reserved.
OMB+> auto_mkindex c:/oracle/tcllib myprocedure.tcl
OMB+>

OMB*Plus extensibility

OMB*Plus extensibility - defining new command automatically during startup.

There are several ways to extend features of OMB*Plus for development.
OMB*Plus is an TCL interpreter based on tcljava implementation (look at OWB_HOME\owb\lib\int directory - you will find jacl.jar and tcljava.jar files which are the core of tcljava interpreter).

It is quite simple in Tcl (and in OMB*Plus) add new command - after definition of new procedure you will get new command with the name of just created procedure:

OMB*Plus: Release 10.2.0.3.33
Copyright (c) 2000, 2006, Oracle. All rights reserved.
OMB+> proc MYCOMMAND {txt} {
>   return $txt
> }
OMB+> MYCOMMAND "Text from procedure"
Text from procecure
OMB+>

If you have a lot such procedures (and use them extensively) you can include them in single file and execute this file with source command each time you start OMB*Plus.
It is possible to automate this procedure with Tcl auto_load (search for auto_load or auto_path on wiki.tcl.tk site) feature. When Tcl interpreter cannot find called command withing standard (internal) commands or defined procedures it looks directory from auto_path variable for definitions of this command via tclIndex file (this file contains for each "external" command/procedure reference to file with it definition), run file with definition this command (via source Tcl command) and finally - run requested command.

To enable auto_load feature in OMB*Plus we should make three things:

• create file with definition of new procedure and place this file in selected directory (say to c:\oracle\tcllib)
• create tclIndex file in the same directory
• guide OMB*Plus where look for definitions of new procedures

First task is clear - place our MYCOMMAND procedure in file with name MyProcedure.tcl under c:\oracle\tcllib directory.
Tcl contains standard command auto_mkindex for creation tclIndex file, but tcljava implementation contains bug which prevent using this command. So we will create tclIndex file manualy (the first line is VERY IMPORTANT!):

# Tcl autoload index file, version 2.0
set auto_index(MYCOMMAND) [list source [file join $dir MyProcedure.tcl]]

Now if we add to auto_path variable our directory (MyProcedure) we will get possibility to run commands indexed in tclIndex file:

OMB+> puts $auto_path
resource:/tcl/lang/library
OMB+> info commands MY*
OMB+> MYCOMMAND
invalid command name "MYCOMMAND"
OMB+> lappend auto_path c:/oracle/tcllib
resource:/tcl/lang/library c:/oracle/tcllib
OMB+> puts $auto_path
resource:/tcl/lang/library c:/oracle/tcllib
OMB+> info commands MY*
OMB+> MYCOMMAND "Text to display"
Text to display
OMB+> info commands MY*
MYCOMMAND
OMB+>

And last thing - to set auto_path automatically it is enogh to define new environment variable TCLLIBPATH (the only problem - it seems impossible to define more than one directory via TCLLIBPATH, general method for sepparating paths on Windows OS with semicolon doesn't work for TCLLIBPATH).

Generate mapping creation tcl script

I started development of OMB*Plus program for generating mapping creation script (sometime it is better to use OMB for moving OWB objects to other environment).

Current status - implemented processing for 50% of operators (see table below) and processed a limited list of operator properties (for example not processed EXTRACTION_HINTS, SCHEMA, etc. properties). You can download it from map_reconstruct.tcl.

Implemented operator processing

Operator type	Planed to implement	Implemented	Processed operator properties
ADVANCED_QUEUE
AGGREGATOR	x	x	GROUP_BY_CLAUSE, HAVING_CLAUSE
ANYDATA_CAST
CONSTANT	x	x	EXPRESSION
CONSTRUCT_OBJECT
CUBE	?
DATA_GENERATOR
DEDUPLICATOR	x	x	-
DIMENSION	?
EXPAND_OBJECT
EXPRESSION	x	x	EXPRESSION
EXTERNAL_TABLE	x	x
FILTER	x	x	FILTER_CONDITION
FLAT_FILE	?
INPUT_PARAMETER	x	x
INPUT_SIGNATURE
ITERATOROPERATOR
JOINER	x	x	JOIN_CONDITION
KEY_LOOKUP	x	x
LCRCAST
LCRSPLITTER
MATCHMERGE
MATERIALIZED_VIEW
NAME_AND_ADDRESS
OUTPUT_PARAMETER	x	x
OUTPUT_SIGNATURE
PIVOT	x
PLUGGABLE_MAPPING	?
POSTMAPPING_PROCESS	x
PREMAPPING_PROCESS	x
SEQUENCE	x	x
SET_OPERATION	x
SORTER	?
SPLITTER	x	x
TABLE	x	x	LOAD_COLUMN_WHEN_INSERTING_ROW, LOAD_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_DELETING_ROW, UPDATE_OPERATION
TABLE_FUNCTION
TRANSFORMATION	x	x
UNPIVOT	x
VIEW	x	x	LOAD_COLUMN_WHEN_INSERTING_ROW, LOAD_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_UPDATING_ROW, MATCH_COLUMN_WHEN_DELETING_ROW, UPDATE_OPERATION

OWB11g R2 impressions

Standalone version of OWB11g R2 for Windows has been released in april 2010. This event pushed me to deep learning of new OWB release.

What is good:

1) now OWB is multiplatform tool which allow to use any RDBMS as target

What is bad:

1) bug which prevent deployment of processflow packages from Design Center/Control Center Manager

2) by default OWB generate execution unit in Template Mapping with the same name (worlaround - always rename execution unit and that name is unique across target/stage database)

3) substitution API differs from ODI (API calls work in different way than in ODI)
4) new OWB GUI is less usable (slower and provide smaler working area in mapping/processflow editors) compared to previous release