i’ve just completed the code-generation portion for oracle warehouse builder – data vault link tables.  i thought i’d share a working example of this, including the cross-reference piece so that you can see how this works.  i hope to soon expose the software-as-a-service here on my web-site, and really make it available for testing.  we’ll see how that goes.

the service (as it stands now) is defined as follows:  generate etl code.

inputs: source data model, target data model, cross-reference xlsx or xls spreadsheet
outputs: omb tcl script code.  **note: i also generate the source and target data models to omb script code as well, along with the oracle sequence objects.

example source data model:

[sql]
create table "stage"."ahltat_enc_rtfs"
(
   "doc_ref"             number   constraint  "sys_c0011441" not null ,
   "facilityncid"        number(20)  null ,
   "encounternumber"     number(20)  null ,
   "dts"                 date  null ,
   "enc_rtfsindex"       number  null ,
   "rtftype"             varchar2(10 byte)  null ,
   "noteid"              number  null ,
   "source"              varchar2(20 byte)  null ,
   "userncid"            number(20)  null ,
   "username"            varchar2(20 byte)  null ,
   "dateadded"           date  null ,
   "doc"                 clob  null ,
   "title"               varchar2(80 byte)  null ,
   "status"              number  null ,
   "sensitivitylevel"    number  null ,
   "cosignerncid"        number(20)  null ,
   "originalsize"        number  null ,
   "initialsignature"    number  null ,
   "finalsignature"      number  null ,
   "complete"            varchar2(1 byte)  null ,
   "category"            varchar2(50 byte)  null ,
   "updateflag"          varchar2(1 byte)  null ,
   "createdby"           number(20)  null ,
   "createdon"           date  null ,
   "updatedby"           number(20)  null ,
   "updatedon"           date  null ,
   "readonly"            varchar2(1 byte)  null ,
   "related_section"     varchar2(4000 byte)  null
);[/sql]

example target data model:

[sql]
create table vault.hub_facility (
   fac_sqn number (12) not null,
   fac_ldts date not null,
   fac_rsrc varchar2 (20 byte) not null,
   fac_id varchar2 (100 byte) not null
) ;
create table vault.hub_event (
   evnt_sqn number (12) not null,
   evnt_rsrc varchar2 (20 byte) not null,
   evnt_ldts date not null,
   evnt_id varchar2 (100 byte) not null
) ;
create table vault.lnk_enc_rtfs (
   enc_fac_rtfs_sqn number (12) not null,
   enc_fac_rtfs_ldts date not null,
   enc_fac_rtfs_rsrc varchar2 (20) not null,
   fac_sqn number (12),
   evnt_sqn number (12),
   dts date not null,
   enc_rtfsindex number not null
) ;[/sql]

example cross-reference:  (i realize it’s hard to read given the css)…   anyhow, the column headers are as follows:

source table
source column
group
target table
target column
link target table
link target column


ahltat_enc_rtfs
doc_ref
1
sat_ahltat_enc_rtfs
doc_ref
 
 


 
facilityncid
1
hub_facility
fac_id
lnk_enc_rtfs
fac_sqn


 
encounternumber
1
hub_event
evnt_id
lnk_enc_rtfs
evnt_sqn


 
dts
1
 
 
lnk_enc_rtfs
dts


 
rtftype
1
sat_ahltat_enc_rtfs
rtftype
 
 


 
enc_rtfsindex
1
 
 
lnk_enc_rtfs
enc_rtfs


 
noteid
1
sat_ahltat_enc_rtfs
noteid
 
 


 
userncid
1
sat_ahltat_enc_rtfs
userncid
 
 


 
username
1
sat_ahltat_enc_rtfs
username
 
 


 
dateadded
1
sat_ahltat_enc_rtfs
dateadded
 
 


 
doc
1
sat_ahltat_enc_rtfs
doc
 
 


 
title
1
sat_ahltat_enc_rtfs
title
 
 


 
originalsize
1
sat_ahltat_enc_rtfs
originalsize
 
 


 
complete
1
sat_ahltat_enc_rtfs
complete
 
 


 
category
1
sat_ahltat_enc_rtfs
category
 
 


 
createdby
1
sat_ahltat_enc_rtfs
createdby
 
 


 
createdon
1
sat_ahltat_enc_rtfs
createdon
 
 


 
readonly
1
sat_ahltat_enc_rtfs
readonly
 
 


 
source
1
sat_ahltat_enc_rtfs
source
 
 


 
status
1
sat_ahltat_enc_rtfs
status
 
 


 
sensitivitylevel
1
sat_ahltat_enc_rtfs
sensitivitylevel
 
 


 
cosignerncid
1
sat_ahltat_enc_rtfs
cosignerncid
 
 


 
initialsignature
1
sat_ahltat_enc_rtfs
initialsignature
 
 


 
finalsignature
1
sat_ahltat_enc_rtfs
finalsignature
 
 


 
updateflag
1
sat_ahltat_enc_rtfs
updateflag
 
 


 
updatedby
1
sat_ahltat_enc_rtfs
updatedby
 
 


 
updatedon
1
sat_ahltat_enc_rtfs
updatedon
 
 


 
related_section
1
sat_ahltat_enc_rtfs
related_section
 
 

source table, source column, group, target table, target column, link table, link column
these elements must be defined in the cross-reference with these headers in order to be recognized.

output: omb tcl script for link load mapping:

[sql]
ombcreate mapping ‘m_lnk_enc_rtfs_ahltat_enc_rtfs’
 set properties (description,business_name)
 values (‘rapidace generated mapping, load lnk_enc_rtfs from ahltat_enc_rtfs’,’m_lnk_enc_rtfs_ahltat_enc_rtfs’)
 add table operator ‘lnk_enc_rtfs’
 bound to table ‘lnk_enc_rtfs’
 add table operator ‘ahltat_enc_rtfs’
 bound to table ‘../stage/ahltat_enc_rtfs’
 add deduplicator operator ‘dd_unique’
  add attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘dd_unique’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘dd_unique’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘dd_unique’
    set properties (datatype) values (‘date’)
  add attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘dd_unique’
    set properties (datatype,precision,scale) values (‘number’,38,0)
 add expression operator ‘exp_chg_datatype’
  add attribute ‘fac_sqn’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘ofac_sqn’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,precision,scale,expression) values (‘number’,12,0,’ingrp1.fac_sqn’)
  add attribute ‘evnt_sqn’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘oevnt_sqn’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,precision,scale,expression) values (‘number’,12,0,’ingrp1.evnt_sqn’)
  add attribute ‘dts’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype) values (‘date’)
  add attribute ‘odts’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,expression) values (‘date’,’ingrp1.dts’)
  add attribute ‘enc_rtfsindex’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,precision,scale) values (‘number’,38,0)
  add attribute ‘oenc_rtfsindex’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
    set properties (datatype,precision,scale,expression) values (‘number’,38,0,’ingrp1.enc_rtfsindex’)
 add lookup operator ‘lkp_target_key’
   set properties (lookup_condition)
   values (‘ingrp1.ofac_sqn = outgrp1.fac_sqn and ingrp1.oevnt_sqn = outgrp1.evnt_sqn and ingrp1.odts = outgrp1.dts and ingrp1.oenc_rtfsindex = outgrp1.enc_rtfsindex’)
 bound to table ‘lnk_enc_rtfs’
  add attribute ‘ofac_sqn’ of group ‘ingrp1’ of operator ‘lkp_target_key’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘oevnt_sqn’ of group ‘ingrp1’ of operator ‘lkp_target_key’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘odts’ of group ‘ingrp1’ of operator ‘lkp_target_key’
    set properties (datatype) values (‘date’)
  add attribute ‘oenc_rtfsindex’ of group ‘ingrp1’ of operator ‘lkp_target_key’
    set properties (datatype,precision,scale) values (‘number’,38,0)
 add filter operator ‘fil_existing_recs’
   set properties (filter_condition)
   values (‘inoutgrp1.enc_fac_rtfs_sqn is null’)
  add attribute ‘enc_fac_rtfs_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
    set properties (datatype) values (‘date’)
  add attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
    set properties (datatype,precision,scale) values (‘number’,38,0)
 add constant operator ‘cnst_ldts_rsrc’
  add attribute ‘enc_fac_rtfs_ldts’ of group ‘outgrp1’ of operator ‘cnst_ldts_rsrc’
    set properties (datatype,expression) values (‘date’,’sysdate()’)
  add attribute ‘enc_fac_rtfs_rsrc’ of group ‘outgrp1’ of operator ‘cnst_ldts_rsrc’
    set properties (datatype,length,expression) values (‘varchar2’,20,'[ombtosettablestring ‘ahltat_enc_rtfs’]’)
 add sequence operator ‘seq_generator’
 bound to sequence ‘sqn_lnk_enc_rtfs’
 add joiner operator ‘jnr_all_hubs’
   set properties (join_condition)
   values (‘ingrp1.facilityncid = ingrp2.fac_id and ingrp1.encounternumber = ingrp3.evnt_id’)
  add input group ‘ingrp3’ of operator ‘jnr_all_hubs’
  add attribute ‘facilityncid’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
    set properties (datatype,precision,scale) values (‘number’,20,0)
  add attribute ‘fac_sqn’ of group ‘ingrp2’ of operator ‘jnr_all_hubs’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘fac_id’ of group ‘ingrp2’ of operator ‘jnr_all_hubs’
    set properties (datatype,length) values (‘varchar2’,100)
  add attribute ‘encounternumber’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
    set properties (datatype,precision,scale) values (‘number’,20,0)
  add attribute ‘evnt_sqn’ of group ‘ingrp3’ of operator ‘jnr_all_hubs’
    set properties (datatype,precision,scale) values (‘number’,12,0)
  add attribute ‘evnt_id’ of group ‘ingrp3’ of operator ‘jnr_all_hubs’
    set properties (datatype,length) values (‘varchar2’,100)
  add attribute ‘dts’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
    set properties (datatype) values (‘date’)
  add attribute ‘enc_rtfsindex’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
    set properties (datatype,precision,scale) values (‘number’,38,0)
 add table operator ‘hub_facility’
 bound to table ‘hub_facility’
 add table operator ‘hub_event’
 bound to table ‘hub_event’
  add connection from attribute ‘nextval’ of group ‘outgrp1’ of operator ‘seq_generator’
     to attribute ‘enc_fac_rtfs_sqn’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
  add connection from attribute ‘enc_fac_rtfs_sqn’ of group ‘outgrp1’ of operator ‘lkp_target_key’
     to attribute ‘enc_fac_rtfs_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
  add connection from attribute ‘enc_fac_rtfs_ldts’ of group ‘outgrp1’ of operator ‘cnst_ldts_rsrc’
     to attribute ‘enc_fac_rtfs_ldts’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
  add connection from attribute ‘enc_fac_rtfs_rsrc’ of group ‘outgrp1’ of operator ‘cnst_ldts_rsrc’
     to attribute ‘enc_fac_rtfs_rsrc’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
  add connection from attribute ‘facilityncid’ of group ‘inoutgrp1’ of operator ‘ahltat_enc_rtfs’
     to attribute ‘facilityncid’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘hub_facility’
     to attribute ‘fac_sqn’ of group ‘ingrp2’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘fac_id’ of group ‘inoutgrp1’ of operator ‘hub_facility’
     to attribute ‘fac_id’ of group ‘ingrp2’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘fac_sqn’ of group ‘outgrp1’ of operator ‘jnr_all_hubs’
     to attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘dd_unique’
  add connection from attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘dd_unique’
     to attribute ‘fac_sqn’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
  add connection from attribute ‘ofac_sqn’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘ofac_sqn’ of group ‘ingrp1’ of operator ‘lkp_target_key’
  add connection from attribute ‘ofac_sqn’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
  add connection from attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
     to attribute ‘fac_sqn’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
  add connection from attribute ‘encounternumber’ of group ‘inoutgrp1’ of operator ‘ahltat_enc_rtfs’
     to attribute ‘encounternumber’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘hub_event’
     to attribute ‘evnt_sqn’ of group ‘ingrp3’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘evnt_id’ of group ‘inoutgrp1’ of operator ‘hub_event’
     to attribute ‘evnt_id’ of group ‘ingrp3’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘evnt_sqn’ of group ‘outgrp1’ of operator ‘jnr_all_hubs’
     to attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘dd_unique’
  add connection from attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘dd_unique’
     to attribute ‘evnt_sqn’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
  add connection from attribute ‘oevnt_sqn’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘oevnt_sqn’ of group ‘ingrp1’ of operator ‘lkp_target_key’
  add connection from attribute ‘oevnt_sqn’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
  add connection from attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
     to attribute ‘evnt_sqn’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
  add connection from attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘ahltat_enc_rtfs’
     to attribute ‘dts’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘dts’ of group ‘outgrp1’ of operator ‘jnr_all_hubs’
     to attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘dd_unique’
  add connection from attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘dd_unique’
     to attribute ‘dts’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
  add connection from attribute ‘odts’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘odts’ of group ‘ingrp1’ of operator ‘lkp_target_key’
  add connection from attribute ‘odts’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
  add connection from attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
     to attribute ‘dts’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
  add connection from attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘ahltat_enc_rtfs’
     to attribute ‘enc_rtfsindex’ of group ‘ingrp1’ of operator ‘jnr_all_hubs’
  add connection from attribute ‘enc_rtfsindex’ of group ‘outgrp1’ of operator ‘jnr_all_hubs’
     to attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘dd_unique’
  add connection from attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘dd_unique’
     to attribute ‘enc_rtfsindex’ of group ‘ingrp1’ of operator ‘exp_chg_datatype’
  add connection from attribute ‘oenc_rtfsindex’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘oenc_rtfsindex’ of group ‘ingrp1’ of operator ‘lkp_target_key’
  add connection from attribute ‘oenc_rtfsindex’ of group ‘outgrp1’ of operator ‘exp_chg_datatype’
     to attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
  add connection from attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘fil_existing_recs’
     to attribute ‘enc_rtfsindex’ of group ‘inoutgrp1’ of operator ‘lnk_enc_rtfs’
ombcommit[/sql]

please let me know if you are interested in seeing more of this kind of thing, or if you might be interested in actually using a service like this.  keep in mind the following:

today: we hard-code the “mapping” design, so you don’t have control over the template – you get all my years of experience in performance and tuning, and high-speed etl/elt out of the box for you.  also, you will get (in the next month or so) informatica etl/xml metadata that you can import.

cheers,
dan linstedt
danl@danlinstedt.com