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)