Services To Generate ETL Load Code

How would you like to take: a) a source model, b) target model, c) cross-reference (source-to-target) spread-sheet, push it IN to a service, and automatically generate Target ETL Loading Code?   I’ve got a service I’m working on that does just that.  I hope it’s available soon.  For now, here’s some preliminary information & an example that generates OWB / OMB Script code from the inputs mentioned above.

For now, I generate only Oracle Warehouse Builder Code.  Shortly (within the next few weeks) I’ll be generating Informatica v9 loading code.

Required Inputs:

  • Source Data Model DDL
  • Target Data Model DDL
  • XLSX or XLS (excel spreadsheet) source to target cross-reference

Right now, I generate code to load data from a Staging Area to a HUB and a LINK TARGET, this afternoon I’ll be generating code to load and end-date the Satellites.

Example Source Data Model DDL:

CREATE TABLE "STAGE"."AHLTAT_DIAGNOSIS"
(
   "DOC_REF"             NUMBER   CONSTRAINT  "SYS_C0011445" NOT NULL ,
   "CREATETIME"          DATE  NULL ,
   "CREATEUSERNCID"      NUMBER(20)  NULL ,
   "MODIFYUSERNCID"      NUMBER(20)  NULL ,
   "MODIFYTIME"          DATE  NULL ,
   "PRIORITY"            VARCHAR2(50 BYTE)  NULL ,
   "DIAGNOSESCOMMENT"    CLOB  NULL ,
   "EXTENDEDICD9"        VARCHAR2(50 BYTE)  NULL ,
   "SENSITIVITY"         VARCHAR2(50 BYTE)  NULL ,
   "UPDATEDON"           DATE  NULL ,
   "UPDATEDBY"           NUMBER(20)  NULL ,
   "TMIP_STATUS"         NUMBER  NULL ,
   "UPDATEFLAG"          VARCHAR2(1 BYTE)  NULL ,
   "CDR_DATAID"          NUMBER(20)  NULL ,
   "CREATEDON"           DATE  NULL ,
   "CREATEDBY"           NUMBER(20)  NULL ,
   "PREFIX"              VARCHAR2(5 BYTE)  NULL ,
   "CLINICIANNCID"       NUMBER(20)  NULL ,
   "ENCOUNTERNUMBER"     NUMBER(20)  NULL ,
   "UNIT_NUMBER"         NUMBER(10)  NULL ,
   "MEDCINID"            NUMBER(20)  NULL ,
   "DATAID"              NUMBER  NULL ,
   "DIAGNOSISNCID"       NUMBER(20)  NULL ,
   "FACILITYNCID"        NUMBER(20)  NULL ,
   "BILLINGCODENCID"     NUMBER(20)  NULL ,
   "ICD9NCID"            NUMBER  NULL ,
   "SOURCENCID"          NUMBER(20)  NULL ,
   "NEWFOLLOWUP"         VARCHAR2(50 BYTE)  NULL ,
   "POINTOFCAREFACILITYNCID"  NUMBER(20)  NULL ,
   "STATUSNCID"          NUMBER(20)  NULL ,
   "CHRONICITYNCID"      NUMBER(20)  NULL ,
   "NOTEDDATE"           DATE  NULL ,
   "ONSETDATE"           DATE  NULL ,
   "RS_FORCENULL"        VARCHAR2(15 BYTE)  NULL
);
 
COMMENT ON TABLE "STAGE"."AHLTAT_DIAGNOSIS" IS 'Records diagnosis information provided by the clinician.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CREATETIME" IS 'Createtime is the time created the diagnoses.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CREATEUSERNCID" IS 'Createuserncid is the create user numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."MODIFYUSERNCID" IS 'Modifyuserncid is the modify user numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."MODIFYTIME" IS 'time modified.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."PRIORITY" IS 'Indicates the order, whether the primary diagnosis or secondary, etc., diagnosis.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."DIAGNOSESCOMMENT" IS 'Text field for amplification of the diagnosis.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."SENSITIVITY" IS 'Sensitivity indicates the sensitivity of the diagnoses.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."UPDATEDON" IS 'Updatedon is the date who updated.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."UPDATEDBY" IS 'Updatedby is who updated.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."TMIP_STATUS" IS 'The tmip_status is for data manager transfer from LDDB to TMIP.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."UPDATEFLAG" IS 'Updateflag is the flag to indicate if update.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CDR_DATAID" IS 'CRD_dataid is the CRD data identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CREATEDON" IS 'Createdon is the created date.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CREATEDBY" IS 'Createdby is who created.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CLINICIANNCID" IS 'Clinicncid is the clinic numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."ENCOUNTERNUMBER" IS 'Sequential number automatically assigned to an encounter for a selected patient.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."UNIT_NUMBER" IS 'Unit_number is number for the unit.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."MEDCINID" IS 'Medcinid is the mediine identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."DATAID" IS 'Dataid is the unique data id for the patient.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."DIAGNOSISNCID" IS 'Diagnosysncid is the diagnosys numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."FACILITYNCID" IS 'Facilityncid is the facility unique numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."BILLINGCODENCID" IS 'Billingcodencid is the billiing code numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."ICD9NCID" IS 'Icd9ncid is the icd9 numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."SOURCENCID" IS 'Sourcencid is the source numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."NEWFOLLOWUP" IS 'Newfollowup is the follow up.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."POINTOFCAREFACILITYNCID" IS 'Point of Care Facility unique id. It is unique for the machine/server on which the Clinical note was created.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."STATUSNCID" IS 'Statusncid is the status numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."CHRONICITYNCID" IS 'Chronicityncid is the chronicity numerical concept identification number.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."NOTEDDATE" IS 'Notedate is the date noted.';
COMMENT ON COLUMN "STAGE"."AHLTAT_DIAGNOSIS"."ONSETDATE" IS 'Onsetdate the date on set.';

Target DDL:

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
) ;
COMMENT ON TABLE VAULT.HUB_FACILITY IS '<Business_Name>Facilities</Business_Name>';
COMMENT ON COLUMN VAULT.HUB_FACILITY.FAC_ID IS 'Facilityncid is the facility unique numerical concept identification number.';

Cross-Reference Example:

SOURCE TABLE SOURCE COLUMN GROUP TARGET TABLE TARGET COLUMN
AHLTAT_DIAGNOSIS DOC_REF 1 SAT_AHLTAT_DIAGNOSIS DOC_REF
  DATAID 1 HUB_DIAGNOSIS DIAGNOSIS_DATAID
  FACILITYNCID 1 HUB_FACILITY FAC_ID
  DIAGNOSISNCID 1 SAT_AHLTAT_DIAGNOSIS DIAGNOSISNCID
  ENCOUNTERNUMBER 1 HUB_EVENT EVNT_ID
  CLINICIANNCID 1 HUB_CLINICIAN CLINICIAN_NCID
  UNIT_NUMBER 1 HUB_UNIT UNIT_ID
  MEDCINID 1 HUB_MEDCIN MEDCIN_ID
  CREATETIME 1 SAT_AHLTAT_DIAGNOSIS CREATETIME
  CREATEUSERNCID 1 SAT_AHLTAT_DIAGNOSIS CREATEUSERNCID

OMB Source Script:

OMBCREATE TABLE 'AHLTAT_DIAGNOSIS' 
  SET PROPERTIES (DESCRIPTION) VALUES ('Records diagnosis information provided by the clinician.') 
  ADD COLUMN 'DOC_REF' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',0,'') 
  ADD COLUMN 'CREATETIME' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'Createtime is the time created the diagnoses.') 
  ADD COLUMN 'CREATEUSERNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Createuserncid is the create user numerical concept identification number.') 
  ADD COLUMN 'MODIFYUSERNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Modifyuserncid is the modify user numerical concept identification number.') 
  ADD COLUMN 'MODIFYTIME' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'time modified.') 
  ADD COLUMN 'PRIORITY' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',50,0,'Indicates the order, whether the primary diagnosis or secondary, etc., diagnosis.') 
  ADD COLUMN 'DIAGNOSESCOMMENT' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('CLOB',0,'Text field for amplification of the diagnosis.') 
  ADD COLUMN 'EXTENDEDICD9' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',50,0,'') 
  ADD COLUMN 'SENSITIVITY' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',50,0,'Sensitivity indicates the sensitivity of the diagnoses.') 
  ADD COLUMN 'UPDATEDON' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'Updatedon is the date who updated.') 
  ADD COLUMN 'UPDATEDBY' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Updatedby is who updated.') 
  ADD COLUMN 'TMIP_STATUS' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',0,'The tmip_status is for data manager transfer from LDDB to TMIP.') 
  ADD COLUMN 'UPDATEFLAG' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',1,0,'Updateflag is the flag to indicate if update.') 
  ADD COLUMN 'CDR_DATAID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'CRD_dataid is the CRD data identification number.') 
  ADD COLUMN 'CREATEDON' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'Createdon is the created date.') 
  ADD COLUMN 'CREATEDBY' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Createdby is who created.') 
  ADD COLUMN 'PREFIX' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',5,0,'') 
  ADD COLUMN 'CLINICIANNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Clinicncid is the clinic numerical concept identification number.') 
  ADD COLUMN 'ENCOUNTERNUMBER' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Sequential number automatically assigned to an encounter for a selected patient.') 
  ADD COLUMN 'UNIT_NUMBER' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',10,0,0,'Unit_number is number for the unit.') 
  ADD COLUMN 'MEDCINID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Medcinid is the mediine identification number.') 
  ADD COLUMN 'DATAID' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',0,'Dataid is the unique data id for the patient.') 
  ADD COLUMN 'DIAGNOSISNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Diagnosysncid is the diagnosys numerical concept identification number.') 
  ADD COLUMN 'FACILITYNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Facilityncid is the facility unique numerical concept identification number.') 
  ADD COLUMN 'BILLINGCODENCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Billingcodencid is the billiing code numerical concept identification number.') 
  ADD COLUMN 'ICD9NCID' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',0,'Icd9ncid is the icd9 numerical concept identification number.') 
  ADD COLUMN 'SOURCENCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Sourcencid is the source numerical concept identification number.') 
  ADD COLUMN 'NEWFOLLOWUP' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',50,0,'Newfollowup is the follow up.') 
  ADD COLUMN 'POINTOFCAREFACILITYNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Point of Care Facility unique id. It is unique for the machine/server on which the Clinical note was created.') 
  ADD COLUMN 'STATUSNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Statusncid is the status numerical concept identification number.') 
  ADD COLUMN 'CHRONICITYNCID' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',20,0,0,'Chronicityncid is the chronicity numerical concept identification number.') 
  ADD COLUMN 'NOTEDDATE' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'Notedate is the date noted.') 
  ADD COLUMN 'ONSETDATE' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'Onsetdate the date on set.') 
  ADD COLUMN 'RS_FORCENULL' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',15,0,'')
OMBCOMMIT

OMB Target Script:

OMBCREATE TABLE 'HUB_FACILITY' 
  SET PROPERTIES (DESCRIPTION) VALUES ('<Business_Name>Facilities</Business_Name>') 
  ADD COLUMN 'FAC_SQN' 
  SET PROPERTIES (DATATYPE,PRECISION,SCALE,NOT_NULL,DESCRIPTION) VALUES ('NUMBER',12,0,0,'') 
  ADD COLUMN 'FAC_LDTS' 
  SET PROPERTIES (DATATYPE,NOT_NULL,DESCRIPTION) VALUES ('DATE',0,'') 
  ADD COLUMN 'FAC_RSRC' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',20,0,'') 
  ADD COLUMN 'FAC_ID' 
  SET PROPERTIES (DATATYPE,LENGTH,NOT_NULL,DESCRIPTION) VALUES ('VARCHAR2',100,0,'Facilityncid is the facility unique numerical concept identification number.')
OMBCOMMIT

OMB Sequence Script:

OMBCREATE SEQUENCE 'SQN_HUB_FACILITY' 
  SET PROPERTIES(DEPLOYABLE,INCREMENT_BY,START_WITH) 
  VALUES ('false',1,0)
OMBCOMMIT

OMB Mapping Script:

OMBCREATE MAPPING 'M_HUB_DIAGNOSIS_AHLTAT_DIAGNOS' 
 SET PROPERTIES (BUSINESS_NAME, DESCRIPTION) 
 VALUES ('Load HUB_DIAGNOSIS from AHLTAT_DIAGNOSIS', 'RapidACE Generated Mapping') 
 ADD TABLE OPERATOR 'HUB_DIAGNOSIS' BOUND TO TABLE 'HUB_DIAGNOSIS'
 ADD TABLE OPERATOR 'AHLTAT_DIAGNOSIS' BOUND TO TABLE '../STAGE/AHLTAT_DIAGNOSIS'
 ADD DEDUPLICATOR OPERATOR 'DD_UNIQUE'
 ADD EXPRESSION OPERATOR 'EXP_CHG_DATATYPE'
 ADD LOOKUP OPERATOR 'LKP_TARGET_KEY' BOUND TO TABLE 'HUB_DIAGNOSIS'
 ADD FILTER OPERATOR 'FIL_EXISTING_RECS'
 ADD ATTRIBUTE 'DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'DD_UNIQUE'
   SET PROPERTIES (DATATYPE,DESCRIPTION) VALUES ('NUMBER','Dataid is the unique data id for the patient.')
 ADD ATTRIBUTE 'DATAID' OF GROUP 'INGRP1' OF OPERATOR 'EXP_CHG_DATATYPE'
   SET PROPERTIES (DATATYPE,DESCRIPTION) VALUES ('NUMBER','Dataid is the unique data id for the patient.')
 ADD ATTRIBUTE 'ODIAGNOSIS_DATAID' OF GROUP 'OUTGRP1' OF OPERATOR 'EXP_CHG_DATATYPE'
   SET PROPERTIES (DATATYPE,DESCRIPTION,EXPRESSION) VALUES ('NUMBER','Dataid is the unique data id for the patient.','INGRP1.DATAID')
 ADD ATTRIBUTE 'ODIAGNOSIS_DATAID' OF GROUP 'INGRP1' OF OPERATOR 'LKP_TARGET_KEY'
   SET PROPERTIES (DATATYPE,DESCRIPTION) VALUES ('NUMBER','Dataid is the unique data id for the patient.')
 ADD ATTRIBUTE 'DIAGNOSIS_DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'FIL_EXISTING_RECS'
   SET PROPERTIES (DATATYPE,DESCRIPTION) VALUES ('NUMBER','Dataid is the unique data id for the patient.')
 ADD CONNECTION FROM ATTRIBUTE 'DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'AHLTAT_DIAGNOSIS'
   TO ATTRIBUTE 'DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'DD_UNIQUE'
 ADD CONNECTION FROM ATTRIBUTE 'DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'DD_UNIQUE'
   TO ATTRIBUTE 'DATAID' OF GROUP 'INGRP1' OF OPERATOR 'EXP_CHG_DATATYPE'
 ADD CONNECTION FROM ATTRIBUTE 'ODIAGNOSIS_DATAID' OF GROUP 'OUTGRP1' OF OPERATOR 'EXP_CHG_DATATYPE'
   TO ATTRIBUTE 'ODIAGNOSIS_DATAID' OF GROUP 'INGRP1' OF OPERATOR 'LKP_TARGET_KEY'
 ADD CONNECTION FROM ATTRIBUTE 'ODIAGNOSIS_DATAID' OF GROUP 'OUTGRP1' OF OPERATOR 'EXP_CHG_DATATYPE'
   TO ATTRIBUTE 'DIAGNOSIS_DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'FIL_EXISTING_RECS'
 ADD CONNECTION FROM ATTRIBUTE 'DIAGNOSIS_DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'FIL_EXISTING_RECS'
   TO ATTRIBUTE 'DIAGNOSIS_DATAID' OF GROUP 'INOUTGRP1' OF OPERATOR 'HUB_DIAGNOSIS'
 ADD ATTRIBUTE 'LDIAGNOSIS_SQN' OF GROUP 'INOUTGRP1' OF OPERATOR 'FIL_EXISTING_RECS'
   SET PROPERTIES (DATATYPE,PRECISION,SCALE,DESCRIPTION) VALUES ('NUMBER',12,0,'')
 ADD CONNECTION FROM ATTRIBUTE 'DIAGNOSIS_SQN' OF GROUP 'OUTGRP1' OF OPERATOR 'LKP_TARGET_KEY'
   TO ATTRIBUTE 'LDIAGNOSIS_SQN' OF GROUP 'INOUTGRP1' OF OPERATOR 'FIL_EXISTING_RECS'
 ADD CONSTANT OPERATOR 'CNST_LDTS_RSRC'
 ADD ATTRIBUTE 'DIAGNOSIS_LDTS' OF GROUP 'OUTGRP1' OF OPERATOR 'CNST_LDTS_RSRC'
   SET PROPERTIES (DATATYPE,DESCRIPTION,EXPRESSION) VALUES ('DATE','The date and time the record was loaded.','SYSDATE()')
 ADD ATTRIBUTE 'DIAGNOSIS_RSRC' OF GROUP 'OUTGRP1' OF OPERATOR 'CNST_LDTS_RSRC'
   SET PROPERTIES (DATATYPE,LENGTH,DESCRIPTION,EXPRESSION) VALUES ('VARCHAR2',20,'The record source of the data.','[OMBToSettableString 'AHLTAT_DIAGNOSIS']')
 ADD CONNECTION FROM ATTRIBUTE 'DIAGNOSIS_LDTS' OF GROUP 'OUTGRP1' OF OPERATOR 'CNST_LDTS_RSRC'
   TO ATTRIBUTE 'DIAGNOSIS_LDTS' OF GROUP 'INOUTGRP1' OF OPERATOR 'HUB_DIAGNOSIS'
 ADD CONNECTION FROM ATTRIBUTE 'DIAGNOSIS_RSRC' OF GROUP 'OUTGRP1' OF OPERATOR 'CNST_LDTS_RSRC'
   TO ATTRIBUTE 'DIAGNOSIS_RSRC' OF GROUP 'INOUTGRP1' OF OPERATOR 'HUB_DIAGNOSIS'
 ADD SEQUENCE OPERATOR 'SQN_HUB_DIAGNOSIS' BOUND TO SEQUENCE 'SQN_HUB_DIAGNOSIS'
 ADD CONNECTION FROM ATTRIBUTE 'NEXTVAL' OF GROUP 'OUTGRP1' OF OPERATOR 'SQN_HUB_DIAGNOSIS'
   TO ATTRIBUTE 'DIAGNOSIS_SQN' OF GROUP 'INOUTGRP1' OF OPERATOR 'HUB_DIAGNOSIS'
OMBCOMMIT
OMBALTER MAPPING 'M_HUB_DIAGNOSIS_AHLTAT_DIAGNOS'
 MODIFY OPERATOR 'FIL_EXISTING_RECS' SET PROPERTIES (FILTER_CONDITION) VALUES ('INOUTGRP1.LDIAGNOSIS_SQN IS NULL')
 MODIFY OPERATOR 'LKP_TARGET_KEY' SET PROPERTIES (LOOKUP_CONDITION) VALUES ('INGRP1.ODIAGNOSIS_DATAID = OUTGRP1.DIAGNOSIS_DATAID')

This is all Pattern Based generation.  If you change the Cross-Reference, it will change the generation.  If you change the source or target model, it will change the generation… 

I can produce 100% error free ETL mappings for multiple ETL tools this way.  Would you be interested in a service like this?  

You can contact me directly for my technology (which is RapidACE based, or you can contact my friends)  at: http://www.analytixids.com – they’ve got some really cool technology.

Cheers,
Dan Linstedt

Tags: , , , , ,

No comments yet.

Leave a Reply

*