Thursday Feb 09, 2012

ODI 11g – Interface Builder

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional based matching, like names ignoring case and so on. Here we will see another example (download OdiInterfaceBuilder.java) showing a different aspect using a control file which describes the interface in simple primitives which drives the creation. The example uses a tab delimited text file to control the interface creation, but it could be easily taken and changed to drive from Excel, XML or whatever you wanted to capture the design of the interface.

The interface can be as complete or incomplete as you’d like, so could just contain the objects or could be concise and semantically complete.

The control file is VERY simple and just like ODI requests the minimal amount of information required. The basic format is as follows;

Directive Column 2 Column 3 Column 4 Column 5
source <model> <datastore>    
   can add many        
target <model> <datastore>    
mapping <column> <expression>    
   can add many        
join <expression>      
   can add many        
filter <expression>      
    can repeat many        
lookup <model> <datastore> <alias> <expression>
   can add many        

So for example the control file below can define the sources, target, joins, mapping expressions etc;

source    SCOTT    EMP
source    SCOTT    DEPT
target    STG_MODEL_CASE    TGTEMP
mapping    ENAME    UPPER(EMP.ENAME)
mapping    DNAME    UPPER(DEPT.DNAME)
mapping    DEPTNO    ABS(EMP.EMPNO)
join    EMP.DEPTNO = DEPT.DEPTNO
lookup    SCOTT    BONUS    BONUS    BONUS.ENAME = EMP.ENAME
filter    EMP.SAL > 1
mapping    COMM    ABS(BONUS.COMM)

When executed, this generates the interface below with the join, filter, lookup and target expressions from the file.

You should be able to join the dots between the control file sample and the interface design above.

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceBuilder;

java –classpath <cp> OdinterfaceBuilder jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK DEMO1 <myinterfacecontrolfile.tab

The interface to be created is passed from the command line. You can intersperse other documentation lines between the control lines so long as the control keywords in first column don’t clash.

Anyway some useful snippets of code for those learning the SDK, or for those wanting to capture the design outside and generate ODI Interfaces. Have fun!

Tuesday Jun 21, 2011

OWB – OWBLand on SourceForge

There are a bunch of interesting utilities that are either experts or OMB scripts that are hosted on SourceForge by some keen OWB users (see the home here). One of the main initiatives has been an Excel to OWB ‘one click ETL’ utility, which looks to have had a fair amount of code added, there is an example but its kinda light on documentation, but does look like it covers quite a lot. One of the nice things about SourceForge is that you can peek into the statistics and see what kind of activity has gone on, from last August there have been a bunch of downloads with a big peak last November…

owbland

Another utility that is there is one to generate OMB from a mapping definition, a bunch of useful stuff there - http://sourceforge.net/projects/owbland/files/

About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today