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