OneClickETL release 0.4.0

New release of OneClickETL script 0.4.0 supports generation of SCD Type 2 mapping and now it is possible to specify join condition for Joiner operator (for multi source tables) and also you can add Filter operator to source table. Download new release of script (OneClickETL-0.4.0.tcl) here.

For generation SCD Type 2 mapping  set mapping type to SCD2. There are new target column roles - SurrogateId, EffectiveDate and ExpiryDate. Also for sucessfull generation of SCD2 mapping you need to specify name of loading date parameter (used for extracting actual rows from target table for comparing against data from source system) and for each target table (from SCD2 mapping) - sequence object which will be used for populating surrogate identifier column - for this task add new row of type TABLE to target warehouse worksheet. Of course sequence object must be created (or imported) in OWB project.

For specification filtering condition on source table and Joiner operator condition use FilterCondition and JoinCondition accordingly.

For examples for using of these new feature use OneClickETL-0.4.0.xls file from OneClickETL download section of OWBLand Sourceforge project (for SCD2 mapping type look at DWH and Map_5 Excel worksheets, for FilterCondition/JoinCondition parameters look Map_4 and Map5 worksheets). For experiments you can use OWB project ONECLICKETL-0.3.1.mdl

OneClickETL 0.3.1 release

(Article in progress)

New release of OneClickETL (0.3.1) support autogeneration of staging module and processflow which contains all mappings from specification.
To support these features I added three new parameters to target warehouse specification worksheet (StageModule, ProcessFlowModule and ProcessFlowPackage) and also you need define additional worksheet in case of configuration  staging module (i.e. if you define StageModule parameter you also must include in Excel file worksheets with specification of source system).

Screenshot of source system specification worksheet

Screenshot of target warehouse specification worksheet

In download section of OWBLand project you can find new MDL file with demo OWB project (ONECLICKETL-0.3.1.mdl) and example of specification file (OneClickETL-0.3.1.xls).

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):

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)