Tuesday Jan 14, 2014

OWB to ODI Migration Utility now available for OWB 11.2.0.3 +CP3 Linux and Windows 64-bit standalone installation.

The OWB to ODI Migration now supports migration from OWB version 11.2.0.3+CP3 to ODI 12c.

It is available as "Patch 17224695: OWB-ODI MIGRATION PATCH FOR 11.2.0.3 64BIT STANDALONE INSTALL" and can be downloaded from the support website.

This patch(17224695) supports migration from Linux 64-bit and Windows 64-bit standalone OWB 11.2.0.3 + CP3( Patch 16568042) to ODI 12.1.2.0.0 + Patch 17053768.

More information about the migration utility is here http://docs.oracle.com/middleware/1212/odi/ODIMG/index.html

Wednesday Dec 11, 2013

OWB to ODI Migration Utility released for Windows 64 bit.

The OWB to ODI Migration Utility is now available for Windows 64-bit platforms. It can be downloaded from the Oracle support site. It is available as Patch number17830453. It needs to be applied on top of  a OWB 11.2.0.4 standalone install.

More information about the Migration Utility is available here.

OWB to ODI Migration Utility Webcast - Thu 12th December

On Thursday 12th December there is a webcast on the OWB to ODI 12c migration utility, there will be a demo and drill down into the utility. Check the meeting URL here - its at 10am PST on 12th December. Check out the blog post here on getting the utility. Good chance to get the inside scoop on the utility and ask questions to the PM and development team.


Monday Dec 02, 2013

Planning the journey from Oracle Warehouse Builder to Oracle Data Integrator

Check out the blog post here on the OWB integration and migration capabilities in ODI 12c. Julien and myself give an overview of what you can see and expect in the recent release. This is an overview which will give you some insights and pointers to the documentation. 

Friday Nov 15, 2013

OWB to ODI Migration Utility released.

The much awaited OWB to ODI Migration utility has been released.

The migration utility is a command-line tool that enables you to migrate design-time metadata from OWB 11.2.0.4 to ODI 12c. The migration utility is provided in patch number 17547241 for your OWB 11gR2 (11.2.0.4) installation.

Migration is supported on Linux 64-bit x86 systems only. Before migrating, ensure that the following requirements are met:

  • OWB 11.2.0.4 installed (OWB 11.2.0.4 plus patch number 17547241)

  • ODI 12.1.2.0.0 installed (ODI 12.1.2.0.0 plus patch number 17053768)

ODI is Oracle's strategic product for heterogeneous data integration. Because many Oracle Database customers have significant investment in OWB, Oracle supports a phased migration from OWB 11gR2 (11.2.0.4) to ODI 12c (12.1.2).

  • ODI 12c supports an easier mapping between OWB 11gR2 concepts and objects and their ODI 12c counterparts. A migration utility is provided that automatically translates many OWB objects and mappings into their ODI equivalents. More information about the migration utility is here

http://docs.oracle.com/middleware/1212/odi/ODIMG/index.html

In addition to enabling the migration, the ODI 12c patch also comes with
new  components such as Pivot, Unpivot, Subquery filter  and Table Functions.
They're documented in the following appendix:
http://docs.oracle.com/middleware/1212/odi/ODIDG/app_12c_patch.htm


Thursday Oct 17, 2013

ODI 12c is GA

Great news today, ODI 12c is GA! See the Welcome Oracle Data Integration 12c posting from Irem. Great to see the hard work from our development teams come to light. Looking forward to MUCH MUCH MORE TOO!

Happy Birthday ODI 12c!


Thursday Sep 26, 2013

OWB - Making the Move to ODI

Yesterday's session on Making the Move to Oracle Data Integrator at OOW13 after the America's Cup finale was a great way to round off the day (the garlic at the Stinking Rose was even better but we will stick with the technology here), the session was hosted by Julien Testut from product management and was under the Oracle safe harbor statement below. There was an overview by Jyotin Gautam the ODI development director and then some demonstrations from Oracle partners Stewart Bryson and Holger Friedrich.

The session lay down the options for OWB customers moving forward; 

  • execute and monitor OWB jobs within ODI
  • migrate all or parts of OWB mappings with the migration utility
  • develop new ETL in ODI

...or a mixture of all of these. Stewart demonstrated the execution of OWB jobs from within ODI and highlighted the immediate value-add of doing this. ODI load plans provide a restartability capability that is a key part of an ETL infrastructure (this had to be manually constructed in OWB). Its great hearing the genuine excitement and enthusiasm Stewart presents with and using words like 'beautiful' are icing on the cake (I can still hear his comments a few years ago of 'all I want is a mapper' ringing in my ears). Holger demonstrated the migration utility that migrates the OWB design metadata into ODI, migrating a complex mapping with many operators and multiple targets into a ODI 12c mapping and like many was surprised with the simplicity of the solution and the maintainability of the new design in ODI.

There was a bunch of tweeting going on in the session and Mark Rittman quick succinctly summarized the session below;

It was a well attended session with a mix of OWB and ODI customers and customers looking to move to ODI in general. The guys in development were happy to see this finally getting their. Sure and steadfast ;-)

The session was under Oracle's safe harbor statement...'The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.'

Thursday Jun 07, 2012

ODI 11g – Faster Files

Deep in the trenches of ODI development I raised my head above the parapet to read a few odds and ends and then think why don’t they know this? Such as this article here – in the past customers (see forum) were told to use a staging route which has a big overhead for large files. This KM is an example of the great extensibility capabilities of ODI, its quite simple, just a new KM that;

  1. improves the out of the box experience – just build the mapping and the appropriate KM is used
  2. improves out of the box performance for file to file data movement.

This improvement for out of the box handling for File to File data integration cases (from the 11.1.1.5.2 companion CD and on) dramatically speeds up the file integration handling. In the past I had seem some consultants write perl versions of the file to file integration case, now Oracle ships this KM to fill the gap. You can find the documentation for the IKM here. The KM uses pure java to perform the integration, using java.io classes to read and write the file in a pipe – it uses java threading in order to super-charge the file processing, and can process several source files at once when the datastore's resource name contains a wildcard. This is a big step for regular file processing on the way to super-charging big data files using Hadoop – the KM works with the lightweight agent and regular filesystems.

So in my design below transforming a bunch of files, by default the IKM File to File (Java) knowledge module was assigned. I pointed the KM at my JDK (since the KM generates and compiles java), and I also increased the thread count to 2, to take advantage of my 2 processors.

For my illustration I transformed (can also filter if desired) and moved about 1.3Gb with 2 threads in 140 seconds (with a single thread it took 220 seconds) - by no means was this on any super computer by the way. The great thing here is that it worked well out of the box from the design to the execution without any funky configuration, plus, and a big plus it was much faster than before,

So if you are doing any file to file transformations, check it out!

Tuesday Mar 27, 2012

ODI 11g – Oracle Multi Table Insert

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result.

When designing this to exploit the IKM you must split the problem into the reusable parts – the select part goes in one interface (I named SELECT_PART), then each target goes in a separate interface (INSERT_SPECIAL and INSERT_REGULAR).

So for my statement below…

/*INSERT_SPECIAL interface */ insert  all
when 1=1 And (INCOME_LEVEL > 250000) then
into SCOTT.CUSTOMERS_NEW
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
values
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)

/* INSERT_REGULAR interface */ when 1=1  then
into SCOTT.CUSTOMERS_SPECIAL
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
values
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
/*SELECT*PART interface */ select   
    CUSTOMERS.EMAIL EMAIL,
    CUSTOMERS.CREDIT_LIMIT CREDIT_LIMIT,
    UPPER(CUSTOMERS.NAME) NAME,
    CUSTOMERS.USER_MODIFIED USER_MODIFIED,
    CUSTOMERS.DATE_MODIFIED DATE_MODIFIED,
    CUSTOMERS.BIRTH_DATE BIRTH_DATE,
    CUSTOMERS.MARITAL_STATUS MARITAL_STATUS,
    CUSTOMERS.ID ID,
    CUSTOMERS.USER_CREATED USER_CREATED,
    CUSTOMERS.GENDER GENDER,
    CUSTOMERS.DATE_CREATED DATE_CREATED,
    CUSTOMERS.INCOME_LEVEL INCOME_LEVEL
from    SCOTT.CUSTOMERS   CUSTOMERS
where    (1=1)

Firstly I create a SELECT_PART temporary interface for the query to be reused and in the IKM assignment I state that it is defining the query, it is not a target and it should not be executed.

Then in my INSERT_SPECIAL interface loading a target with a filter, I set define query to false, then set true for the target table and execute to false. This interface uses the SELECT_PART query definition interface as a source.

Finally in my final interface loading another target I set define query to false again, set target table to true and execute to true – this is the go run it indicator!

To coordinate the statement construction you will need to create a package with the select and insert statements. With 11g you can now execute the package in simulation mode and preview the generated code including the SQL statements.

Hopefully this helps shed some light on how you can leverage the Oracle MTI statement. A similar IKM exists for Teradata. The ODI IKM Teradata Multi Statement supports this multi statement request in 11g, here is an extract from the paper at www.teradata.com/white-papers/born-to-be-parallel-eb3053/

Teradata Database offers an SQL extension called a Multi-Statement Request that allows several distinct SQL statements to be bundled together and sent to the optimizer as if they were one. Teradata Database will attempt to execute these SQL statements in parallel. When this feature is used, any sub-expressions that the different SQL statements have in common will be executed once, and the results shared among them.

It works in the same way as the ODI MTI IKM, multiple interfaces orchestrated in a package, each interface contributes some SQL, the last interface in the chain executes the multi statement.

Friday Mar 02, 2012

ODI 11g – Insight to the SDK

This post is a useful index into the ODI SDK that cross references the type names from the user interface with the SDK class and also the finder for how to get a handle on the object or objects. The volume of content in the SDK might seem a little ominous, there is a lot there, but there is a general pattern to the SDK that I will describe here.

Also I will illustrate some basic CRUD operations so you can see how the SDK usage pattern works. The examples are written in groovy, you can simply run from the groovy console in ODI 11.1.1.6.

Entry to the Platform

Object Finder SDK
odiInstance odiInstance (groovy variable for console) OdiInstance

Topology Objects

Object Finder SDK
Technology IOdiTechnologyFinder OdiTechnology
Context IOdiContextFinder OdiContext
Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
Data Server IOdiDataServerFinder OdiDataServer
Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

Project Objects

Object Finder SDK
Project IOdiProjectFinder OdiProject
Folder IOdiFolderFinder OdiFolder
Interface IOdiInterfaceFinder OdiInterface
Package IOdiPackageFinder OdiPackage
Procedure IOdiUserProcedureFinder OdiUserProcedure
User Function IOdiUserFunctionFinder OdiUserFunction
Variable IOdiVariableFinder OdiVariable
Sequence IOdiSequenceFinder OdiSequence
KM IOdiKMFinder OdiKM

Load Plans and Scenarios

Object Finder SDK
Load Plan IOdiLoadPlanFinder OdiLoadPlan
Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

Model Objects 

Object Finder SDK
Model IOdiModelFinder OdiModel
Sub Model IOdiSubModel OdiSubModel
DataStore IOdiDataStoreFinder OdiDataStore
Column IOdiColumnFinder OdiColumn
Key IOdiKeyFinder OdiKey
Condition IOdiConditionFinder OdiCondition

Operator Objects

Object Finder SDK
Session Folder IOdiSessionFolderFinder OdiSessionFolder
Session IOdiSessionFinder OdiSession
Schedule OdiSchedule

 

How to Create an Object?

Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

project = new OdiProject("Project For Demo", "PROJECT_DEMO")
odiInstance.getTransactionalEntityManager().persist(project)
tm.commit(txnStatus)

How to Update an Object?

This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class);
project = prjFinder.findByCode("PROJECT_DEMO");

project.setName("A Demo Project");

odiInstance.getTransactionalEntityManager().persist(project)
tm.commit(txnStatus)

How to Delete an Object?

Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.

import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;
import oracle.odi.domain.runtime.session.OdiSession;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class);
sessc = sessFinder.findAll();
sessItr = sessc.iterator()
while (sessItr.hasNext()) {
  sess = (OdiSession) sessItr.next()
  odiInstance.getTransactionalEntityManager().remove(sess)
}
tm.commit(txnStatus)

This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. For details of how specific complex objects are created via the SDK, its best to look at postings such as the interface builder posting here. Have fun, happy coding!

Tuesday Feb 28, 2012

ODI 11g – Expert Accelerator for Model Creation

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have experts for capturing user input, with the groovy console we open up opportunities to build UI around the scripts in a very easy way – even I can do it;-)

After a little googling around I found some useful posts on SwingBuilder, the most useful one that I used for the dialog below was this one here. This dialog captures user input for the technology and context for the model and logical schema etc to be created. You can see there are a variety of interesting controls, and its really easy to do.

The dialog captures the users input, then when OK is pressed I call the functions from the earlier post to create the logical schema (plus all the other objects) and model. The image below shows what was created, you can see the model (with typo in name), the model is Oracle technology and references the logical schema ORACLE_SCOTT (that I named in dialog above), the logical schema is mapped via the GLOBAL context to the data server ORACLE_SCOTT_DEV (that I named in dialog above), and the physical schema used was just the user name that I connected with – so if you wanted a different user the schema name could be added to the dialog.

In a nutshell, one dialog that encapsulates a simpler mechanism for creating a model. You can create your own scripts that use dialogs like this, capture input and process.

You can find the groovy script for this is here odi_create_model.groovy, again I wrapped the user capture code in a groovy function and return the result in a variable and then simply call the createLogicalSchema and createModel functions from the previous posting. The script I supplied above has everything you will need. To execute use Tools->Groovy->Open Script and then execute the green play button on the toolbar.

Have fun.

Friday Feb 24, 2012

ODI 11g - Getting Scripting with Groovy

The addition of the groovy interpreter to the ODI designer now let’s you easily script any tasks that you repeatedly perform. The documentation has illustrations here, so using the ODI 11g SDK you can encapsulate common tasks in simple groovy functions.

Groovy can be executed by executing a script, you can create a new one or open an existing groovy script;

You will then see a new groovy window appear in the IDE plus the execute green button is enabled on the toolbar.

I have taken the script defined here and shown below in its more minimal groovy form and parameterized the script in a groovy function ‘createProject’. I can then call createProject with whatever values for the project and folder I wish to create.

import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.OdiFolder;

def createProject(projectName, projectCode, folderName) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)
  project = new OdiProject(projectName, projectCode)
  folder = new OdiFolder(project, folderName)
  odiInstance.getTransactionalEntityManager().persist(project)
  tm.commit(txnStatus)
}

createProject("EDW Staging", "EDW", "Initialization")

So in the UI if I execute as follows;

After executing the script I refresh the Designer tree and see my new project.

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 Jan 24, 2012

ODI Time Generation – SQL as a Source

Came across a nice use of the earlier SQL as a Source KM posting where the source was a time dimension generator. The forum entry is here, so the temporary interface is a data generator which when nested can be used in an interface to merge or load into a target.

Click on the image to see more …

 

Nice way to capture within tool and leverage different integration KMs on the target.

Tuesday Jul 05, 2011

ODI 11g - Pipelines

There’s a write up of some pushing the envelope on ODIEE 11g capabilities here that I presented parts of last week at ODTUG KScope 11 in Long Beach, California.

The ODTUG Kaleidescope/KScope is certainly an awesome conference with gurus on all fronts willing to talk about their cherished areas. Next year its headed for San Antonio, Texas – Long Beach was fun and it was great to meet all the folks that I did!

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