Monday, November 8, 2010

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)









2 comments: