Tuesday, April 26, 2011

Creating table definitions via OMB*Plus

Recently I had task for importing into OWB large Oracle database schema (about 4000 tables with more than 6000 primary/unique/foreign key constraints). The import process took more than 2 hours (I disabled importing physical properties, paritions and indexes). I tried another approach - generating OMB*Plus script for creating tables (with constraints)  from database dictionary. This approach works twice faster - generated OMB*Plus script created all tables withing 1 hour.

The first create this function (used for extracting CSV list of columns which compose specified constraint):
create or replace function "OWBLand_Get_Constraint_Columns" (P_TABLE_NAME IN VARCHAR2, P_CONSTRAINT_NAME IN VARCHAR2) RETURN VARCHAR2 AS
  vColumnList VARCHAR2(2000);
  TYPE tColumnTab IS TABLE OF VARCHAR2(100);
  vColumnTab tColumnTab;
  CURSOR curConstraints(pt VARCHAR2,pc VARCHAR2) IS
     select column_name from user_cons_columns where table_name=pt and constraint_name=pc order by position;
BEGIN
    OPEN curConstraints(P_TABLE_NAME, P_CONSTRAINT_NAME);
    FETCH curConstraints BULK COLLECT INTO vColumnTab;
    CLOSE curConstraints;
    FOR i in vColumnTab.FIRST .. vColumnTab.LAST LOOP
        IF i=vColumnTab.FIRST THEN vColumnList:= ''''||vColumnTab(i)||'''';
        ELSE vColumnList:=vColumnList||','''||vColumnTab(i)||''''; END IF;
    END LOOP;
    RETURN vColumnList;
END;
/



Save this script to file (for example with name gen_model.sql, don't forget to change target filename in spool command at the start of this script):

set echo off
set pages 1000
btitle off
set heading off
set feedback off

select 'set StartTime [clock seconds]' omb_command from dual;

column table_name NEW_VALUE crtab NOPRINT
column table_comment NEW_VALUE tabcomm NOPRINT
ttitle left 'OMBCREATE TABLE ''' crtab ''' SET PROPERTIES(DESCRIPTION) VALUES(''' tabcomm ''')'
break on table_name skip page

spool c:\owb_temp.tcl

select replace(tc.table_name,'$','\$') as table_name,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(tcm.comments,'\','\\'),';','\;'),'''',''''''),'"','\"'),'[','\['),']','\]'),'{','\{'),'}','\}'),'$','\$'),chr(10),'\n'),chr(9),'\t') table_comment,
'OMBALTER TABLE '''||replace(tc.table_name,'$','\$')||''' ADD COLUMN '''||replace(tc.column_name,'$','\$')||''' SET PROPERTIES(DATATYPE,NOT_NULL'||
case when tc.data_type='NUMBER' then ',PRECISION,SCALE' when tc.data_type='FLOAT' then ',PRECISION'
 when tc.data_type in ('CHAR','VARCHAR','VARCHAR2','RAW','NCHAR','NVARCHAR','NVARCHAR2') then ',LENGTH'
 when tc.data_type like 'TIMESTAMP%' then ',FRACTIONAL_SECONDS_PRECISION'
 end||
',DESCRIPTION) VALUES('''||case when tc.data_type like 'TIMESTAMP%' then 'TIMESTAMP' else tc.data_type end||
''','''||case tc.nullable when 'Y' then 'false' else 'true' end||''''||
case when tc.data_type='NUMBER' then ','''||tc.data_precision||''','''||tc.data_scale||''''
 when tc.data_type='FLOAT' then ','''||tc.data_precision||''''
 when tc.data_type in ('CHAR','VARCHAR','VARCHAR2','RAW','NCHAR','NVARCHAR','NVARCHAR2') then ','''||tc.data_length||''''
 when tc.data_type like 'TIMESTAMP%' then ','''||trim(')' from trim('(' from regexp_substr(tc.data_type,'\(.*\)')))||''''
 end||
','''||replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ccm.comments,'\','\\'),';','\;'),'''',''''''),'"','\"'),'[','\['),']','\]'),'{','\{'),'}','\}'),'$','\$'),chr(10),'\n'),chr(9),'\t')||''')'
omb_command
 from user_tab_columns tc,user_tab_comments tcm,user_col_comments ccm
where tc.data_type not like 'LONG%' and tc.table_name=tcm.table_name
and tc.table_name=ccm.table_name and tc.column_name=ccm.column_name
--and tc.table_name like 'A%'
order by tc.table_name,tc.column_id;

set pages 0
clear breaks
clear columns
ttitle off

select 'puts "created all tables, [expr {[clock seconds]-$StartTime}] seconds"' omb_command from dual;
select 'OMBCOMMIT' omb_command from dual;

select 'OMBALTER TABLE '''||replace(table_name,'$','\$')||''' ADD '||case when constraint_type='P' then 'PRIMARY_KEY' else 'UNIQUE_KEY' end||
' '''||replace(constraint_name,'$','\$')||''' SET REFERENCE COLUMNS ('||
replace("OWBLand_Get_Constraint_Columns"(table_name,constraint_name),'$','\$')||')' omb_command
 from user_constraints
where constraint_type in ('P','U')
--and table_name like 'A%'
order by table_name,constraint_type,constraint_name;

select 'puts "created all primary/unique keys, [expr {[clock seconds]-$StartTime}] seconds"' omb_command from dual;
select 'OMBCOMMIT' omb_command from dual;

select 'OMBALTER TABLE '''||replace(c.table_name,'$','\$')||''' ADD FOREIGN_KEY '''||replace(c.constraint_name,'$','\$')||''' SET REFERENCE COLUMNS ('||
replace("OWBLand_Get_Constraint_Columns"(c.table_name,c.constraint_name),'$','\$')||') SET REFERENCE '||
case when refc.constraint_type='P' then 'PRIMARY_KEY' else 'UNIQUE_KEY' end||' '''||
replace(c.r_constraint_name,'$','\$')||''' OF TABLE '''||replace(refc.table_name,'$','\$')||'''' omb_command
 from user_constraints c,user_constraints refc
where c.constraint_type='R' and c.r_owner=refc.owner and c.r_constraint_name=refc.constraint_name
--and c.table_name like 'AA%'
order by c.table_name,c.constraint_type,c.constraint_name;

select 'puts "created all foreign keys, [expr {[clock seconds]-$StartTime}] seconds"' omb_command from dual;
select 'OMBCOMMIT' omb_command from dual;



spool off




set heading on
set feedback 5
set pages 24

set echo on


Then run script gen_model.sql under SQL*Plus:

@gen_model.sql
As a result you got OMB script (in my case it was owb_temp.tcl file in the root of dick C:) which you can run under OMB*Plus with source command. Before executing this script change current context to corresponding source module (with OMBCC command).

Tuesday, April 12, 2011

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

Friday, April 1, 2011

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

Sunday, March 27, 2011

Corrected bug in last release of map_reconstruct script

There was a bug in last release (0.0.3) of map_reconstruct.tcl script (unwanted backslash in multiline conditions in "if" operators). So download newer release 0.0.4 from OWBLand Sourceforge project (look for file map_reconstruct-0.0.4.tcl).
Release 0.0.3 isСохранить как черновик removed from files repository.

Thursday, March 24, 2011

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





Sunday, February 20, 2011

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