OWB / OMB Code Generated for Link Tables

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

Tags: , , , , , ,

3 Responses to “OWB / OMB Code Generated for Link Tables”

  1. Denny 2010/05/30 at 8:23 am #

    Hi Dan,

    Great stuff. I’m currently working on a template-driven Datavault ETL-generator for Oracle Warehouse Builder as well. I’ll share my ideas as soon as things start get working.

    Regards,
    Denny

  2. Denny 2010/06/07 at 5:01 am #

    Small update. I’ve constructed two ETL-templates for loading the Hubs, Satellites and Links. Next step is to deduce the generic and specific parts of the loading steps. After that I’ll think about the generation.

    Denny

  3. dlinstedt 2010/06/07 at 6:31 am #

    Hi Denny,

    Cool. The more the merrier… my solution will be ready very soon as SaaS, but more than that – it will allow you to generate maps for Informatica PowerCenter, Microsoft SSIS, and Oracle OWB from one set of metadata – so regardless of tool choice, you can have all maps available for any tool of your choosing. In the future I will expand it to Pentaho, and Talend if possible.

    Cheers,
    Dan L

Leave a Reply

*