New OneClickETL script release 0.2.0

New release 0.2.0 of OneClickETL script is available for download (look for file OneClickETL-0.2.0.tcl) from OWBLand Sourceforge project.
OneClickETL script automatically generate mappings based on specification from Excel file (rules for prepearing Excel file described in previous OneClickETL-related articles - http://owbeg.blogspot.com/2010/11/prototype-oneclicketl-script.html and http://owbeg.blogspot.com/2010/11/oneclicketl-release-011.html).

Now last release support column expressions - in mapping specification worksheet (in column B where earlier was source columns) it is possible to specify expression over columns (you can even insert standard Oracle function/stored function call).
See screen snapshots with example of expressions in Excel file and generated mappings:

Another example (for SCD Type 1 mapping type):

map_reconstruct script update - better support for OWB11g R2

I released new version (0.0.3) of map_reconstruct.tcl script. Now this script supports OWB11g R2 "unified" mapping datatypes. Earlier releases of this script lost detailed info for several datatypes (for example, for NUMERIC datatype precision and scale properties always was zero, or zero value for length property of VARCHAR datatype).
Also I changed processing of JOINER operator - previous releases finished abnormaly in OWB11gR2 on mapping with joiner.
Download new release (0.0.3) of map_reconstruct.tcl script from OWBLand Sourceforge project

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
}