Wednesday Dec 11, 2013

GoldenGate 12c - What is Coordinated Delivery?

In Oracle GoldenGate 12c, the parallel apply is improved with two features: Integrated Delivery and Coordinated Delivery. Different from the Integrated Delivery, Coordinated Delivery is not limited to Oracle Database.This blog discusses the new Coordinated Delivery feature by answering two questions:

  • Why do I need Coordinated Delivery?

  • What is Coordinated Delivery?
  • [Read More]

    Sunday Dec 08, 2013

    ODI 12c - XML improvements

    ODI has added support to a number of advanced XML Schema constructs in the latest 12.1.2 release: union, list, substitution groups, mixed content, and annotations. XML is a core exchange format in SOA and B2B environments; it is essential for data integration to read and write XML documents in a data load process. ODI supports XML files as sources and targets and allows access to nested XML structures by mapping them to relational tables. This is accomplished through the ODI XML JDBC driver. This entry focuses on the new 12.1.2 features that build on top of XML support from previous ODI releases. 

    Let’s take a look at the improvements in detail with concrete examples for each type.


    The union construct allows a simple type to be based on a union of multiple different simple types, for example a clothing size that is either numeric or based on words. ODI maps a simple type based on union into a VARCHAR column in the resulting relational table, containing the XML string representation of the simple type.

    Example XSD:

    <xs:element name="shirtSize">
          <xs:union memberTypes="sizeNum sizeString" />

    <xs:simpleType name="sizeNum">
    <xs:restriction base="xs:positiveInteger">
          <xs:maxInclusive value="42"/>

    <xs:simpleType name="sizeString">
    <xs:restriction base="xs:string">
          <xs:enumeration value="small"/>
          <xs:enumeration value="medium"/>
          <xs:enumeration value="large"/>

     Example XML fragment:

    <shirtSize >41</shirtSize>
    <shirtSize >small</shirtSize>
    <shirtSize >medium</shirtSize>

    Resulting ODI datastore:


    The list construct defines an XML Schema simple type that consists of a list of values delimited by space. ODI will load a list type into a VARCHAR column regardless of XML type and retain the space-separated value. The user would need to use SQL functions in ODI to extract the appropriate values from the list string.

    Example XSD:

    <xs:element name="availableDates" type="valuelist"/>

    <xs:simpleType name="valuelist">
       <xs:list itemType="xs:integer"/>

    Example XML fragment:

    <availableDates>1 4 5 6 8 11 19 23 30</availableDates>

    Resulting ODI datastore:


    The XML Schema substitution group mechanism helps to extend a complex type into one or more subtypes. Elements of the supertype can be of any of the types in the substitution group, allowing polymorphism. In our example this allows to create a list that contains both shoes or pants types with different structures. ODI treats sequences based on substitution group elements much like it treats xsd:choice based sequences; it generates different tables for each substitution group member and loads the elements into the respective tables. Order and parent FK are maintained.

    Example XSD:

    <xs:element name="clothing" type="ClothingType"/>

    <xs:complexType name="ClothingType">
          <xs:element name="name" type="xs:string"/>

    <xs:element name="shoe" type="ShoeType" substitutionGroup="clothing"/>

    <xs:complexType name="ShoeType">
          <xs:extension base="ClothingType">
                <xs:element name="size" type="xs:string"/>

    <xs:element name="pants" type="PantsType" substitutionGroup="clothing"/>

    <xs:complexType name="PantsType">
          <xs:extension base="ClothingType">
                <xs:element name="inseam" type="xs:integer"/>
                <xs:element name="waist" type="xs:integer"/>

    Example XML Fragment:



    Resulting ODI datastores:

    Mixed Content

    Mixed content allows an element to have arbitrary text between the child elements, much like XHTML. In the example the element email has acomplex type with attribute mixed=”true”. Because of this, ODI will map the element into a single VARCHAR column containing the entire markup with child elements and text. If the user needs to get to individual content inside this element, an external parser needs to be used.

    Example XSD:

    <xs:element name="email">
    <xs:complexType mixed="true">
             <xs:element name="name" type="xs:string"/>
             <xs:element name="order" type="xs:positiveInteger"/>
             <xs:element name="date" type="xs:date"/>

    Example XML Fragment:

    Dear <name>Estella Havisham</name>!
    We have processed your oder number <order>345424356</order>
    The estimated date of delivery will be <date>2013-12-23</date>.

    Resulting ODI datastore:


    XML Schema annotations are used in XML Schema to provide metadata for schema constructs. Annotations have been allowed in ODI XML JDBC driver before, but they have been discarded. Now annotations on elements and attributes are stored in the ODI datastore description. Other annotations, such as those referring to the entire schema or to types, groups, sequences, and others, are still discarded. This information can be viewed and modified in ODI Studio and by the SDK, but modifications are not written back to the XSD file.

    Example XSD:

    <xs:element name="customer">
          <xs:documentation>Formal customer record for retail clothing orders.</xs:documentation>
             <xs:element name="id" type="xs:integer"/>
             <xs:element name="firstName" type="xs:string"/>
             <xs:element name="lastName" type="xs:string"/>
             <xs:element name="birthDate" type="xs:date"/>

    Example XML fragment:


    Annotation as datastore description:

    The following are great resources if you need more information on ODI’s XML support in general: 

    The files for this example can be downloaded here:

    Thursday Dec 05, 2013

    Are You Ready for the Future?

    Have you wondered where you and your organization will be in the future as the information technology advances in an unprecedented way? Have you wondered whether your company, or team, is equipped with the right tools and strategy to be ahead of competition, as everything around us changes? If yes, you are not alone. If not, you should. 

    When it comes to leveraging data and creating opportunities for innovation and growth, data integration is a critical technology that sits in the center of many key initiatives: big data analytics, cloud integration, modernization to name a few. Many industry leaders that excel and continue to innovate are the ones that keep an eye on how they collect and distribute their data.They know the value of turning data into an asset, rather than a cost driver.

    With the new release of Oracle Data Integrator 12c and Oracle GoldenGate 12c, Oracle established itself as the leader in the data integration and replication space.  And how Oracle differentiates its offering is now highlighted in an interactive e-book: The Path to the Future.

    In this e-book we told Oracle Data Integration product family's story through the words of our customers; including Starwood Hotels and Resorts, BT, Herbalife, SK Telecom, MediSwitch, Aria Systems, MegaFon and others.  The e-book reveals how Oracle Data Integration products help you stay ahead of the curve by integrating any data, anywhere, at the speed of thought, with maximum performance and availability,  and full trust in its high quality.  You will also find out how the latest 12c release helps you keep pace with the latest trends while improving developer productivity and performance. 

    Take a look at our new interactive e-book and let us know what you think!

    Tuesday Dec 03, 2013

    ODI 12c - Mapping SDK the ins and outs

    The ODI 12c SDK provides a mechanism to accelerate data integration development using patterns and the APIs in the SDK. With OWB many customers automated mundane tasks - tasks that were repeated and consistent. I've uploaded a bunch of examples covering many characteristics of the 12c mapping SDK, the examples are primarily slanted towards the logical side right now. You can find examples of the components and images for each on the site (download ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy). This is an open community area where you too can contribute.

    The mappings I have covered include (click on hyperlink for an image of each);

    1. basic datastore to datastore mapping with configuration of the LKM and IKM on the deployment spec which options set (datastore component)
    2. simple filter example writing to a target (filter component).
    3. datastores being joined, with a left outer join property set then written to a target (join component).
    4. data being looked up from a reference table using the (lookup component).
    5. data being aggregated, SUM aggregation used and data automatically grouped (aggregate component).
    6. defining expressions which may be reused across many map parts, also useful for explicitly seeing expressions in canvas (expression component).
    7. unifying data using the relational set component to UNION/MINUS data (set component).
    8. if then/else capabilities using the split component to define branches (split component).
    9. defining a dataset to source data from, defines joins and filters (dataset component)
    10. deduplicating data with distinct (distinct component).

    These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. I plan on more examples, plenty more to show including some of the nitty gritty details on the physical deployment specification side. I'd also like to show the mapping accelerators, I posted some while back this kind of capability for 11g (see here).

    If you are brand new to the ODI SDK, I tend to break down the labyrinth of classes into the following sections (I blogged about this for 11g here);

    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
    Mapping IMappingFinder Mapping
    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;

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

    project = new OdiProject("Project For Demo", "PROJECT_DEMO")

    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;

    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");


    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;

    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)

    Hopefully these examples will get you on your way. 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. The mappings examples are a good start, more to come on those, remember and check out the ODI area in for the examples (here). Have fun, happy coding

    Monday Dec 02, 2013

    Planning the journey from Oracle Warehouse Builder to Oracle Data Integrator

    by Julien Testut and David Allan


    In this post we are going to discuss how Oracle Warehouse Builder (OWB) customers can easily migrate to Oracle Data Integrator 12c (ODI12c) thanks to two key new features introduced recently.

    As mentioned in our statement of direction Oracle Data Integrator is Oracle's strategic product for heterogeneous data integration. recognizing that many Oracle Database customers have a significant investment in OWB, ODI now provides mechanisms to support a phased migration, specifically from OWB 11gR2 ( to ODI 12c (12.1.2).

    The following features are provided to make the transition to ODI easier:

          · ODI 12c supports the execution and administration of OWB 11gR2 jobs directly within ODI Studio, ODI Console and Enterprise Manager. This provides a single orchestration and monitoring solution and allows companies to continue running OWB Mappings and perform a phased migration to ODI.

           · A new migration utility is provided that will automatically translate many OWB objects and mappings into their ODI equivalents. The migration utility is a command-line tool and requires two patches: one for OWB (17547241) and one for ODI (17053768, both can be found on My Oracle Support.

    Phased Migration using OWB and ODI run time Integration

    The run time integration of Oracle Warehouse Builder and Oracle Data Integrator can be used a first step towards aligning with Oracle’s data integration strategy. You can start new developments in ODI 12c while continuing to run side-by-side the two products and start migrating OWB Mappings into ODI Mappings using the migration utility.

    In Oracle Data Integrator 12c it is now possible to define a connection to an Oracle Warehouse Builder workspace in Topology Navigator. Storing the connection and credential details in the ODI repository allows developers to invoke OWB processes in Packages with the OdiStartOwbJob tool (OdiStartOwbJob documentation). It also allows developers and operators to monitor the execution of OWB processes in ODI Studio, ODI Console or Enterprise Manager along with the rest of the ODI jobs.

    The Technical Details

    Invoking OWB processes in ODI 12c is simple. You have to first add the OdiStartOwbJob tool to a Package. Then point the tool to the OWB workspace entry in Topology. This then allows you to browse the content of the OWB workspace and select the specific processes you’d like to invoke as you can see in the following screenshot:

    Once the Package design is complete, you can execute it and monitor its execution in ODI. The OWB and ODI logs have been integrated allowing end users to centrally monitor their ETL processes.

    OWB to ODI Migration Utility

         In addition to the integration of OWB jobs there is also a metadata migration utility to migrate metadata from OWB 11gR2 to ODI 12c. A wide range of metadata can now be migrated with this initial release;(you can find a complete list of objects in the documentation). A large selection of core mapping operators are supported – In addition to the patch containing the utility for the OWB migration, there is also a patch for ODI 12c, and new Knowledge Modules to support some of the OWB mapping capabilities. The user documentation explores these considerations in great detail.

         1. The migration process itself, a good read to be prepared

         2. Details for preparing to migrate

         3. Information on using the migration utility

         4. Reviewing the migration – what to look for in the logs and so on

    Error reference guide and a list of migrated components

    The Technical Details

    Below you can see a project in OWB 11gR2; this has a number of modules including Oracle and flat file modules; the Oracle one has mappings for loading a data warehouse from external tables based on flat files.

    The migration utility is a command line tool that is executed from the OWB home. The utility uses a driver file to specify which modules and objects to migrate. It also has 3 modes of operation, this lets you discover how much of your project will get migrated. The modes are;

          · FAST_CHECK – performs a read only check, which is the fastest way to get an understanding of how much will be migrated.

          · DRY_RUN – a sanity check mode which will try creating the objects in-memory using the ODI 12c SDK but will not commit in the ODI repository.

          · RUN – performs the migration and commit the newly migrated objects in the 12c repository.

    The driver properties file can specify the specific objects to migrate, specify wildcards and lists of objects. The utility produces a report and a log file from its execution. For example, when I migrated the OWB project above, I used the following driver file;



          · ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl11204

          · ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver




          · OWB_URL=localhost:1521:orcl11204



    The utility driver parameters are fully document in the user documentation. Firstly I ran the fast check just to see if there were any issues reported. There were none which was good news and bad – I wanted to illustrate an example that wasn’t migrated, so I will create another to show you what happens.

    In the report file when I executed there is a convenient summary of what happened, you can quickly see what was migrated, the number of objects and also a column defining if any were not migrated;

    You can see the migration utility also has migrated external tables; As part of the patch for ODI there is a new component KM to define an external table on top of the flat file definition. This supports the external table capabilities that were in OWB (both bound and unbound external table). Below you can see the mapping loading the sales data that was shown above, it looks identical to what was in OWB, the datastore representing the external table is still the source, the data is split and written to multiple targets.

    The mapping has been created and configured to mimic the OWB behavior. The utility doesn’t configure the multi insert IKMs as this was an optimization within the OWB code generator, in ODI 12c you simply change the assignment of the IKM on each target. So in above my targets were each assigned IKM Oracle Insert, I changed them both to IKM Oracle Multi-Insert after the migration. You can see the physical configuration below;