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. 

 Example XML fragment:

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

Resulting ODI datastore:

xsd:list

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"/>
</xs:simpleType>

Example XML fragment:

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

Resulting ODI datastore:

substitutionGroup

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:sequence>
      <xs:element name="name" type="xs:string"/>
   </xs:sequence>
</xs:complexType>

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

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

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

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

Example XML Fragment:

<pants>
<name>Humdinger</name>
   <inseam>30</inseam>
   <waist>36</waist>
</pants>

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:sequence>
         <xs:element name="name" type="xs:string"/>
         <xs:element name="order" type="xs:positiveInteger"/>
         <xs:element name="date" type="xs:date"/>
      </xs:sequence>
   </xs:complexType>
</xs:element>

Example XML Fragment:

<email>
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>.
</email>

Resulting ODI datastore:

Annotations

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:annotation>
      <xs:appinfo>
         <version>17.1.4.1</version>
         <lastChange>2013-12-05</lastChange>
      </xs:appinfo>
      <xs:documentation>Formal customer record for retail clothing orders.</xs:documentation>
   </xs:annotation>
   <xs:complexType>
      <xs:sequence>
         <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"/>
      </xs:sequence>
   </xs:complexType>
</xs:element>

Example XML fragment:

<customer>
   <id>12321</id>
   <firstName>Estella</firstName>
   <lastName>Havisham</lastName>
   <birthDate>1961-12-18</birthDate>
</customer>

Annotation as datastore description:

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

• ODI Driver for XML Reference
• Oracle-By-Example Tutorial: Developing an ODI XML to Database Transformation

• odi12_1_2.xsd
• odi12_1_2.xml

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!

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 java.net 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

Topology Objects

Project Objects

Load Plans and Scenarios

Model Objects

Operator Objects

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)

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 java.net for the examples (here). Have fun, happy coding

by Julien Testut and David Allan

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 (11.2.0.4) 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;

      · MIGRATION_OBJECTS=PROJECT.FERRARI_DEMO

      · ODI_MASTER_USER=DEV_ODI_REPO

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

      · ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver

      · ODI_USERNAME=SUPERVISOR

      · ODI_WORK_REPOSITORY_NAME=WORKREP

      · OWB_WORKSPACE_OWNER=OWB_REPOS

      · OWB_URL=localhost:1521:orcl11204

      · OWB_WORKSPACE_NAME=OWB_WKSP

      · MIGRATION_MODE=RUN

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;

You can then generate the code and in simulation mode see the Oracle multi table insert generated processing the external table (also created in the mapping) as source.

I mentioned that the report details information on the objects which cannot be migrated. An example is a pure row based PL\SQL mapping (for example where a PL\SQL procedure was used), this kind of mapping would result in a mapping not automatically migrated, you would see errors such as the following in the migration report;

      · MAP_ROW_BASED

o [ERROR][Migration][MU-5003]Unable to migrate mapping with mapping operator TRANSFORMATION:GEN_DATA_PROC with OUT parameter DATA_NAME.

o [ERROR][Migration][MU-5003]Unable to migrate mapping with mapping operator TRANSFORMATION:GEN_DATA_PROC with OUT parameter DATA_VALUE.

o [ERROR][Migration][MU-5006]Unable to migrate mapping with mapping operator TRANSFORMATION:GEN_DATA_PROC that does not define return attribute.

With these kinds of cases one approach to moving forward to is wrap the PL\SQL procedure in a function if possible. The utility will serve multiple purposes – along with migrating the metadata, for OWB users it will also serve as a way of understanding how concepts map from OWB to ODI.

Summary

In this post we discussed how Oracle Warehouse Builder customers can easily migrate to Oracle Data Integrator 12c thanks to the run time integration and metadata migration features introduced recently.

That’s a little taster of the capabilities with the migration, more detailed information on specific components will keep coming. There are now a lot of new exciting capabilities in each. These components are now no longer Oracle only but also can be used for many different systems so there is a lot of fun and exciting possibilities, stay tuned!

contributed by Ayush Ganeriwal

In this post we shall discuss the new in-session parallelism introduced in the ODI12c release. ODI12c now comes with the intelligence to concurrently execute part of the mappings that are independent of each other. For example the data load into C$ tables from two disparate data source is done in parallel as these operations are independent of each other and can be done simultaneously. Similarly, such parallelism can be achieved for flow control and target loads too in different use cases. ODI12c automatically identifies the session tasks that can be executed concurrently and generates code for their parallel execution. Users can visually see what part of the mapping would be executed in parallel and if needed this behavior can be changed to execute tasks sequentially if the business logic demands so.

Parallelism Display in Deployment Plan

A typical deployment plan is shown above where the data from a file and relations data store are first loaded into C$ tables in staging area and then joined and loaded into target table. Here different source stores and their corresponding C$ tables, join components and, the target data store are organized in separate blue and yellow boxes which are called execution units and execution unit groups respectively. Execution unit group contains one or more execution units which are independent of each other and can be executed concurrently. Execution unit contains the set of operations that need to be executed serially. In above example the SOURCE_GROUP contains two execution units which are independent of each other and are executed in parallel. The TARGET_GROUP contains only one execution unit indicating absence of parallelism.

How is parallelism achieved within a session?

The session tasks within a step are now generated in a hierarchical manner similar to a load plan steps hierarchy. There are a couple of new type of tasks introduced namely Serial task and Parallel task. These are container tasks which can have one or more child tasks under them. As the name suggests the child tasks under a serial container task would be executed serially whereas the children tasks of parallel container task would be executed in parallel. These container tasks can be nested within each other resulting in multiple levels in the task hierarchy. Depending upon the deployment plan, ODI12c generates the hierarchy of these serial and parallel tasks to achieve in session parallelism. Here is example of a serial and parallel task hierarchy.

Mapping segments candidate for Parallelism

Following below are some of the common mapping parts under which tasks are usually independent of each other and can be performed simultaneously.

1. Loading source data into staging area

2. Loading data into multiple target tables

3. Performing flow control on I$ table

These are some of the common candidate area for parallelism and depending upon the mapping flow there may be other areas that could be executed in parallel. Let’s look at an example of one of the above common parallelism candidates and see how the behavior can be altered by tweaking the deployment plan.

Loading source data into staging area

In this mapping a file and DB table are joined and then loaded in the target table. ODI generates following deployment plan for it so that the sources can be loaded in collector tables in staging are concurrently and then join and target load related tasks performed sequentially.

On executing the mapping with this deployment plan the tasks hierarchy is generated as follows. We can notice here that following set of tasks are organized under parallel container task

a. LKM tasks for dropping and recreating C$ tables for each data sources

      b. LKM task to load data into each of the C$ tables

Suppose we do not want these C$ tables to be loaded in parallelly and rather want them to load sequentially then we can achieve that by simply modifying deployment plan by dragging and dropping one of the execution unit outside of the execution unit group. This would create a separate execution unit group for each of the source dataset forcing all the execution units to run serially.

On executing the task hierarchy of it is generated as follows showing serial execution of all tasks.

Threads configuration for parallel tasks

ODI12c runtime spawns a separate thread for each of the parallel tasks in a session. This effectively means that execution of a highly parallelize mapping may take high number of threads exhausting all system resources. ODI provide two level of configuration to control the number of such parallel threads in the physical agent configuration. One to control the maximum number of threads in the agent and other is to control max thread count within a session. A parallel task would be started only if a new thread can be spawned according to these thread configurations. Therefore, these should be kept at sufficiently large levels.

Connection management for parallel task

Since each of the parallel tasks is executed by a separate thread, they should not work on the common connection for any non-transactional behavior. So for performing the non-transactional operations each of the parallel tasks acquires its own parallel connection from the connection pool. Therefore, for highly parallel mappings the connection pool size should be configured appropriately according to the level of parallelism.

One other point to keep in mind here is that since each parallel task acquires a new auto-commit connection closes it on task completion thus the OnConnect/OnDisconnect tasks would be executed while creating and closing of such connection. Thus you may see multiple OnConnect/OnDisconnect tasks entries for a mapping having parallel tasks.

Another implication of such parallelism is that any parallel tasks cannot rely on any alteration in database connection done by the earlier tasks because each of the parallel tasks would get a new connection which would be unaware of any operations performed by earlier tasks.

For operations performed on a transaction connection the parallel tasks do not have much flexibility and has to perform operations one by one. For such transactional connections synchronization is maintained so that only one parallel task can perform operation on the connection. Thus having parallel tasks participating in such transactions may actually degrade performance due to the extra locking/unlocking for such synchronization.

The take-away

ODI12c allows parallelizing parts of a mapping to achieve extreme performance. It has the intelligence to automatically identify parallelizable parts within a mapping and also provide flexibility to achieve serial behavior for special business needs. Such parallelism would result in an improved performance and for even higher levels of parallelism the connections pool and thread count can be further fine tuned.

For more uptodate Oracle Data Integration news also follow the twitter feeds from @ORCLGoldenGate and @nmadhu2k3.

Last week we had a great video webcast for Oracle GoldenGate 12c and Oracle Data Integrator 12c. Our development executives, Brad Adelberg and Tim Hall,  talked about the new features and how the new release helps with delivering future-ready data integration solutions with extreme performance and high IT productivity. 

If you missed the webcast you can watch it on demand via the following page:
On-Demand Video Webcast: Introducing 12c for Oracle Data Integration

In previous blogs we have talked about the new features of Oracle GoldenGate 12c and Oracle Data Integrator 12c, so I am not going to repeat them here. But worth repeating are what our customers and partners say about the new 12c release.

SolarWorld’s Senior Database Administrator, Russ Toyama was in the studio with our executives for the launch webcast, and Russ discussed their GoldenGate implementation for SolarWorld's manufacturing process. SolarWorld is the largest U.S. solar panel manufacturer for more than 35 years and needed operational intelligence to continuously improve the quality of its products, while ensuring the systems operate with high performance and stability.

SolarWorld uses GoldenGate to move data from multiple manufacturing databases into a single decision support system database in real time, freeing up the OLTP systems for transaction processing, which improves their performance and stability. The DSS database is very flexible in meeting reporting needs, and provides a comprehensive view of multiple manufacturing processes. This provides the traditional roles of reporting and engineering analysis to continuously improve product quality, yield and efficiency, and enables real time monitoring of the production manufacturing process. Using this real-time monitoring capability SolarWorld is able to detect the deviations from the norm right away, and take action to remedy or understand the situation. This not only improves production quality, but also improves cost management. The manufacturing process is a series of steps building upon the previous, so if there is an issue, it needs to be corrected as soon as possible to prevent waste and reduce manufacturing costs.

In the launch webcast we also heard from Mark Rittman, CTO of Rittman Mead Consulting. Mark talked about the Oracle Data Integrator 12c new features in great depth, given that he was closely involved with the Beta testing program. Mark shared his opinion that the new flow-based design interface is the most critical feature of this 12c release and will bring major productivity gains for developers. He added that interoperability with Oracle Warehouse Builder for easier migration and tighter integration with Oracle GoldenGate are very valuable for customers. Mark also discussed the new release of Oracle BI Applications and how its use of Oracle Data Integrator for data movement and transformations simplifies life for Oracle BI Apps customers.  The complete interview with Mark Rittman is available for you as well.

If you missed the launch webcast last week, I hope you take the time to watch it on demand and discover how Oracle has changed the data integration and replication technology space with Oracle Data Integrator 12c and Oracle GoldenGate 12c. For more information including white papers and podcasts you can also download free resources.

Today is another big day for ODI and OWB! Get your hands on the much anticipated functionality covering everything from new components added into the ODI 12c mapping designer, to the ETL design migration utility (announced here) to move designs from OWB 11.2.0.4 into ODI 12c which all complement the integration and auditing of OWB jobs from ODI which was included in 12c.

The patches for ODI and OWB are great news on many fronts, not only in being a major point in the roadmap. As well as the OWB migration this is great for the ODI the product, the new components provide a lot of great capabilities (and will help with a lot of those 'how do I do this' questions).

Included in the ODI patch you'll find a component bundle including transformation components from pivot/unpivot through subquery filtering on to table functions and the like. All great examples of the product's extensibility - a fusion of declarative design and technology specific knowledge modules in harmony. Lots of good stuff that you will hear much more of.

There are multiple patches - if you are only interested in ODI features and enhancements then all you need is the ODI patch below;

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

If you want the OWB migration utility then you will need the following patch to OWB as well as the ODI patch above;

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

Customer requirements for data replication are reaching very high levels.   The demand is for continuous, uninterrupted flow of data in real-time while lowering IT costs and improving efficiencies. This part of the market constitutes the middle to upper end of the data replication market and customers are broadly educated and well versed in replication technologies as never before. As a result, they demand well-designed, well-engineered, and well-crafted products – possessing advanced features that compliment their evolving IT systems.

Vendors offer a wide variety of replication products and services, but with the exception of Oracle these other vendors rely on brute force log scraping, inefficient fetching of data types not supported or encrypted, and heavy reliance of manual configuration and tuning when dealing with highly tolerant RAC systems or high data volumes.    Oracle GoldenGate 12c is at the forefront and differentiates itself in the market by providing a truly well-designed, well-engineered and smart solution for the Oracle database.

What are the key new features of Oracle GoldenGate 12c for the Oracle database?

• Support for Database 12c Multitenant feature which allows smooth transitions to private cloud deployments.
• The new Integrated Delivery for Oracle Database. This feature enables simpler and more powerful configurations with a streamlined process model that coordinates multiple parallel apply processes, which improve performance for high volume implementations. 
• Enhanced high availability via integration with Data Guard Fast Start Failover
• Improved security with tighter integration with the Oracle wallet

Oracle GoldenGate is at the vanguard of extreme performance and the one standout feature of the 12c release is Integrated Delivery.    Oracle GoldenGate now leverages a lightweight streaming API built into Database 12c for Oracle GoldenGate. The Smart Product Design of the Oracle GoldenGate 12c Integrated Delivery feature enables simpler and more powerful configurations that coordinate multiple parallel apply processes to improve performance for high volume implementations. 

This high performance feature leverages numerous database parallel apply servers for automatic dependency aware, parallel apply. What this means is that Integrated Delivery will automatically balance transactions across these apply processes and coordinate the transactions to maintain order and insure referential integrity, while delivery data at speeds not feasible by other vendors.

Oracle GoldenGate 12c has many other new features for the Oracle database. You can learn more about GoldenGate 12c by downloading our new resources and watching the replay of the executive video webcast with customer and partner speakers.

If an execution failed in ODI 11g then temporary tables would have been left around and cleaned up in the subsequent run. In ODI 12c, KM tasks can now have a cleanup-type task which is executed even after a failure in the main tasks. These cleanup tasks will be executed even on failure if the property 'Remove Temporary Objects on Error' is set.

2.3 Name length generation 

It is time to get started with Oracle Data Integrator 12c!

We would like to highlight for you a great place to begin your journey with ODI.  Here you will find the Getting Started section for ODI, which provides a few options.

Step 1 –

Start by downloading the Getting Started (PDF). The document provides general background information and detailed examples to help you learn how to use Oracle Data Integrator. This document guides you through a first project with Oracle Data Integrator 12c, and the instructions in this document are required for using the Getting Started Demonstration Environment.

If you would like to run the Getting Started Demonstration Environment on your system go to Step 2 A.

If you would like to run the Getting Started Demonstration Environment installed and pre-configured in a Virtual Image, go to Step 2 B.

Step 2 -

A-   A -  If you would like to run the Getting Started Demo Environment on your system, you will want to then download the Demo Environment for Getting Started (ZIP) archive containing ODI metadata, configuration scripts and sample data.

B-   B -  Alternatively, a pre-configured virtual machine is available with the Getting Started installation and configuration. The Virtual Machine platform uses Oracle Virtual Box technology, and use of this configuration would necessitate the download/installation of Virtual Box and the Getting Started virtual machine. If you prefer this route and would rather run the Getting Started Demonstration Environment installed and preconfigured in a Virtual Machine, please download the ODI 12c Getting Started Virtual Machine.

Step 3 –

In continuation with the Getting Started Demonstration Environment and the Getting Started Guide, you will now be able to run through a first project with Oracle Data Integrator.

Now that you have successfully done the exercises above, you may want to play on your own, and develop with Oracle Data Integrator on your environment...  here you can download a full version of Oracle Data Integrator 12c. 

Enjoy, and happy developing!

Learn more about Oracle Data Integrator; including FAQ, the Oracle by Example Series, Sample Code, and White Papers.

As you may have seen from our recent blog posts on Oracle Data Integrator 12c and Oracle GoldenGate 12c, we are very excited to share with you the great new features the 12c release brings to Oracle’s data integration solutions. And, fortunately we are not alone in this sentiment. Since the press announcement October 17th, which incorporates our customers' and experts' testimonials, we have seen positive comments in leading technology publications and social media as well. Here are some examples:

• In CIO and PCWorld you can find Joab Jackson’s article, Oracle Data Integrator 12c ready for real-time analysis, where wrote about the tight integration between Oracle Data Integrator and Oracle GoldenGate . He noted “Heeding the call from enterprise customers who clamor for more immediacy in their data-driven reports, Oracle has updated its data-integration software portfolio so that it can more rapidly deliver data to data warehouses and analysis applications.”
• Integration Developer News’ Vance McCarthy wrote the article Oracle Ships ‘Future Proofs’ Integration Tools for Traditional, Cloud, Big Data, Real-Time Projects and mentioned that “Oracle Data Integrator 12c and Oracle GoldenGate 12c sport a wide range of improvements to let devs more easily deliver data integration for cloud, analytics, big data and other new projects that leverage multiple datasets for business.“
• InformationWeek’s Doug Henschen gave a great overview to several key features including the new flow-based UI in Oracle Data Integrator. Doug said “Oracle Data Integrator 12c introduces a complete makeover of the job-building experience, while real-time oriented GoldenGate 12c introduces performance gains “.
• In Database Trends and Applications’ article Oracle Strengthens Data Integration with Release of Oracle Data Integrator 12c and Oracle GoldenGate 12c highlighted the productivity aspect of the new solution with his remarks: “tight integration between Oracle Data Integrator 12c and Oracle GoldenGate 12c enables developers to leverage Oracle GoldenGate’s low overhead, real-time change data capture completely within the Oracle Data Integrator Studio without additional training”.

If you have not registered for this free event yet, you can do so via this link. We will run the live event at 8am PT/4pm GMT, followed by a replay of the event with live chat for Q&A  at 10am PT/6pm GMT. The replay will be available on-demand for those who register but cannot attend either session on November 12^th.

This posting will look at the aggregation component that was introduced in ODI 12c. For many ETL tool users this shouldn't be a big surprise, its a little different than ODI 11g but for good reason. You can use this component for composing data with relational like operations such as sum, average and so forth. Also, Oracle SQL supports special functions called Analytic SQL functions, you can use a specially configured aggregation component or the expression component for these now in ODI 12c. In database systems an aggregate transformation is a transformation where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning - that's exactly the purpose of the aggregate component.

In the image below you can see the aggregate component in action within a mapping, for how this and a few other examples are built look at the ODI 12c Aggregation Viewlet here - the viewlet illustrates a simple aggregation being built and then some Oracle analytic SQL such as AVG(EMP.SAL) OVER (PARTITION BY EMP.DEPTNO) built using both the aggregate component and the expression component.

In 11g you used to just write the aggregate expression directly on the target, this made life easy for some cases, but it wan't a very obvious gesture plus had other drawbacks with ordering of transformations (agg before join/lookup. after set and so forth) and supporting analytic SQL for example - there are a lot of postings from creative folks working around this in 11g - anything from customizing KMs, to bypassing aggregation analysis in the ODI code generator.

The aggregate component has a few interesting aspects.

1. Firstly and foremost it defines the attributes projected from it - ODI automatically will perform the grouping all you do is define the aggregation expressions for those columns aggregated. In 12c you can control this automatic grouping behavior so that you get the code you desire, so you can indicate that an attribute should not be included in the group by, that's what I did in the analytic SQL example using the aggregate component.

2. The component has a few other properties of interest; it has a HAVING clause and a manual group by clause. The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate, in 11g the filter was overloaded and used for both having clause and filter clause, this is no longer the case. If a filter is after an aggregate, it is after the aggregate (not sometimes after, sometimes having). 

3. The manual group by clause let's you use special database grouping grammar if you need to. For example Oracle has a wealth of highly specialized grouping capabilities for data warehousing such as the CUBE function. If you want to use specialized functions like that you can manually define the code here. The example below shows the use of a manual group from an example in the Oracle database data warehousing guide where the SUM aggregate function is used along with the CUBE function in the group by clause.

The SQL I am trying to generate looks like the following from the data warehousing guide;

1. SELECT channel_desc, calendar_month_desc, countries.country_iso_code,
2.       TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
3. FROM sales, customers, times, channels, countries
4. WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND
5.   sales.channel_id= channels.channel_id
6.  AND customers.country_id = countries.country_id
7.  AND channels.channel_desc IN
8.   ('Direct Sales', 'Internet') AND times.calendar_month_desc IN
9.   ('2000-09', '2000-10') AND countries.country_iso_code IN ('GB', 'US')
10. GROUP BY CUBE(channel_desc, calendar_month_desc, countries.country_iso_code);

I can capture the source datastores, the filters and joins using ODI's dataset (or as a traditional flow) which enables us to incrementally design the mapping and the aggregate component for the sum and group by as follows;

In the above mapping you can see the joins and filters declared in ODI's dataset, allowing you to capture the relationships of the datastores required in an entity-relationship style just like ODI 11g. The mix of ODI's declarative design and the common flow design provides for a familiar design experience. The example below illustrates flow design (basic arbitrary ordering) - a table load where only the employees who have maximum commission are loaded into a target. The maximum commission is retrieved from the bonus datastore and there is a look using employees as the driving table and only those with maximum commission projected.

Hopefully this has given you a taster for some of the new capabilities provided by the aggregate component in ODI 12c. In summary, the actions should be much more consistent in behavior and more easily discoverable for users, the use of the components in a flow graph also supports arbitrary designs and the tool (rather than the interface designer) takes care of the realization using ODI's knowledge modules.

Interested to know if a deep dive into each component is interesting for folks. Any thoughts? 

contributed by Mike Eisterer

On October 17th, 2013, Oracle announced the release of Oracle Data Integrator 12c (ODI12c).  This release signifies improvements to Oracle’s Data Integration portfolio of solutions, particularly Big Data integration.

Why Big Data = Big Business

Organizations are gaining greater insights and actionability through increased storage, processing and analytical benefits offered by Big Data solutions.  New technologies and frameworks like HDFS, NoSQL, Hive and MapReduce support these benefits now. As further data is collected, analytical requirements increase and the complexity of managing transformations and aggregations of data compounds and organizations are in need for scalable Data Integration solutions.

ODI12c provides enterprise solutions for the movement, translation and transformation of information and data heterogeneously and in Big Data Environments through:

• The ability for existing ODI and SQL developers to leverage new Big Data technologies.
• A metadata focused approach for cataloging, defining and reusing Big Data technologies, mappings and process executions.
• Integration between many heterogeneous environments and technologies such as HDFS and Hive.
• Generation of Hive Query Language.
  

Working with Big Data using Knowledge Modules

 ODI12c provides developers with the ability to define sources and targets and visually develop mappings to effect the movement and transformation of data.  As the mappings are created, ODI12c leverages a rich library of prebuilt integrations, known as Knowledge Modules (KMs).  These KMs are contextual to the technologies and platforms to be integrated.  Steps and actions needed to manage the data integration are pre-built and configured within the KMs. 

The Oracle Data Integrator Application Adapter for Hadoop provides a series of KMs, specifically designed to integrate with Big Data Technologies.  The Big Data KMs include:

• Check Knowledge Module
• Reverse Engineer Knowledge Module
• Hive Transform Knowledge Module
• Hive Control Append Knowledge Module
• File to Hive (LOAD DATA) Knowledge Module
• File-Hive to Oracle (OLH-OSCH) Knowledge Module 

Nothing to beat an Example:

To demonstrate the use of the KMs which are part of the ODI Application Adapter for Hadoop, a mapping may be defined to move data between files and Hive targets. 

The mapping is defined by dragging the source and target into the mapping, performing the attribute (column) mapping (see Figure 1) and then selecting the KM which will govern the process. 

In this mapping example, movie data is being moved from an HDFS source into a Hive table.  Some of the attributes, such as “CUSTID to custid”, have been mapped over.

Before the proper KM can be assigned to define the technology for the mapping, it needs to be added to the ODI project.  The Big Data KMs have been made available to the project through the KM import process.   Generally, this is done prior to defining the mapping.

Figure 2  Importing the Big Data Knowledge Modules

Following the import, the KMs are available in the Designer Navigator.

Figure 3  The Project View in Designer, Showing Installed IKMs

Once the KM is imported, it may be assigned to the mapping target.  This is done by selecting the Physical View of the mapping and examining the Properties of the Target.  In this case MOVIAPP_LOG_STAGE is the target of our mapping.

Figure 4  Physical View of the Mapping and Assigning the Big Data Knowledge Module to the Target

Alternative KMs may have been selected as well, providing flexibility and abstracting the logical mapping from the physical implementation.  Our mapping may be applied to other technologies as well.

The mapping is now complete and is ready to run.  We will see more in a future blog about running a mapping to load Hive.

To complete the quick ODI for Big Data Overview, let us take a closer look at what the IKM File to Hive is doing for us.  ODI provides differentiated capabilities by defining the process and steps which normally would have to be manually developed, tested and implemented into the KM.  As shown in figure 5, the KM is preparing the Hive session, managing the Hive tables, performing the initial load from HDFS and then performing the insert into Hive.  HDFS and Hive options are selected graphically, as shown in the properties in Figure 4.

Figure 5  Process and Steps Managed by the KM

What’s Next

Big Data being the shape shifting business challenge it is is fast evolving into the deciding factor between market leaders and others.

Now that an introduction to ODI and Big Data has been provided, look for additional blogs coming soon using the Knowledge Modules which make up the Oracle Data Integrator Application Adapter for Hadoop:

• Importing Big Data Metadata into ODI, Testing Data Stores and Loading Hive Targets
• Generating Transformations using Hive Query language
• Loading Oracle from Hadoop Sources
  

For more information now, please visit the Oracle Data Integrator Application Adapter for Hadoop web site, http://www.oracle.com/us/products/middleware/data-integration/hadoop/overview/index.html

Do not forget to tune in to the ODI12c Executive Launch webcast on the 12th to hear more about ODI12c and GG12c.

Do you have applications running on the cloud that you need to connect with the on premises systems. The most likely answer to this question is an astounding YES!  If so, then you understand the importance of keep the data fresh at all times across the cloud and on-premises environments. This is also one of the key focus areas for the new GoldenGate 12c release which we announced couple of week ago via a press release.

Most enterprises have spent years avoiding the data “silos” that inhibit productivity. For example, an enterprise which has adopted a CRM strategy could be relying on an on-premises based marketing application used for developing and nurturing leads. At the same time it could be using a SaaS based Sales application to create opportunities and quotes. The sales and the marketing teams which use these systems need to be able to access and share the data in a reliable and cohesive way. This example can be extended to other applications areas such as HR, Supply Chain, and Finance and the demands the users place on getting a consistent view of the data.

When it comes to moving data in hybrid environments some of the key requirements include minimal latency, reliability and security:

• Data must remain fresh. As data ages it becomes less relevant and less valuable—day-old data is often insufficient in today’s competitive landscape.

• Reliability must be guaranteed despite system or connectivity issues that can occur between the cloud and on-premises instances.

• Security is a key concern when replicating between cloud and on-premises instances. There are several options to consider when replicating between the cloud and on-premises instances.

Option 1 – Secured network established between the cloud and on-premises

A secured network is established between the cloud and on-premises which enables the applications (including replication software) running on the cloud and on-premises to have seamless connectivity to other applications irrespective of where they are physically located.

Option 2 – Restricted network established between the cloud and on-premises

A restricted network is established between the cloud and on-premises instances which enable certain ports (required by replication) be opened on both the cloud and on the on-premises instances and white lists the IP addresses of the cloud and on-premises instances.

Option 3 – Restricted network access from on-premises and cloud through HTTP proxy

This option can be considered when the ports required by the applications (including replication software) are not open and the cloud instance is not white listed on the on-premises instance. This option of tunneling through HTTP proxy may be only considered when proper security exceptions are obtained.

Oracle GoldenGate

Oracle GoldenGate is used for major Fortune 500 companies and other industry leaders worldwide to support mission-critical systems for data availability and integration. Oracle GoldenGate addresses the requirements for ensuring data consistency between cloud and on-premises instances, thus facilitating the business process to run effectively and reliably.

In the above scenario, Oracle GoldenGate is installed and configured on both the cloud and the on-premises instances. On the cloud instance Oracle GoldenGate is installed and configured on the machine where the database instance can be accessed. Oracle GoldenGate can be configured for unidirectional or bi-directional replication between the cloud and on premises instances.

The specific configuration details of Oracle GoldenGate processes will depend upon the option selected for establishing connectivity between the cloud and on-premises instances. The knowledge article (ID - 1588484.1) titled ' Replicating between Cloud and On-Premises using Oracle GoldenGate' discusses in detail the options for replicating between the cloud and on-premises instances. The article can be found on My Oracle Support.

To learn more about Oracle GoldenGate 12c register for our launch webcast where we will go into these new features in more detail.   You may also want to download our white paper "Oracle GoldenGate 12c Release 1 New Features Overview"

I would love to hear your requirements for replicating between on-premises and cloud instances, as well as your comments about the strategy discussed in the knowledge article to address your needs. Please post your comments in this blog or in the Oracle GoldenGate public forum - https://forums.oracle.com/community/developer/english/business_intelligence/system_management_and_integration/goldengate

post by David Allan

ODI is renowned for its declarative designer and minimal expression based paradigm. The new ODI 12c release has extended this even further to provide an extended declarative mapping designer. The ODI 12c mapper is a fusion of ODI's new declarative designer with the familiar flow based designer while retaining ODI’s key differentiators of:

• Minimal expression based definition,
• The ability to incrementally design an interface and to extract/load data from any combination of sources, and most importantly
• Backed by ODI’s extensible knowledge module framework.

The declarative nature of the product has been extended to include an extensible library of common components that can be used to easily build simple to complex data integration solutions. Big usability improvements through consistent interactions of components and concepts all constructed around the familiar knowledge module framework provide the utmost flexibility.

Here is a little taster:

So what is a mapping? A mapping comprises of a logical design and at least one physical design, it may have many. A mapping can have many targets, of any technology and can be arbitrarily complex. You can build reusable mappings and use them in other mappings or other reusable mappings.

In the example below all of the information from an Oracle bonus table and a bonus file are joined with an Oracle employees table before being written to a target. Some things that are cool include the one-click expression cross referencing so you can easily see what's used where within the design.

The logical design in a mapping describes what you want to accomplish  (see the animated GIF here illustrating how the above mapping was designed) . The physical design lets you configure how it is to be accomplished. So you could have one logical design that is realized as an initial load in one physical design and as an incremental load in another. In the physical design below we can customize how the mapping is accomplished by picking Knowledge Modules, in ODI 12c you can pick multiple nodes (on logical or physical) and see common properties. This is useful as we can quickly compare property values across objects - below we can see knowledge modules settings on the access points between execution units side by side, in the example one table is retrieved via database links and the other is an external table.

In the logical design I had selected an append mode for the integration type, so by default the IKM on the target will choose the most suitable/default IKM - which in this case is an in-built Oracle Insert IKM (see image below). This supports insert and select hints for the Oracle database (the ANSI SQL Insert IKM does not support these), so by default you will get direct path inserts with Oracle on this statement.

In ODI 12c, the mapper is just that, a mapper. Design your mapping, write to multiple targets, the targets can be in the same data server, in different data servers or in totally different technologies - it does not matter. ODI 12c will derive and generate a plan that you can use or customize with knowledge modules. Some of the use cases which are greatly simplified include multiple heterogeneous targets, multi target inserts for Oracle and writing of XML.

Let's switch it up now and look at a slightly different example to illustrate expression reuse. In ODI you can define reusable expressions using user functions. These can be reused across mappings and the implementations specialized per technology. So you can have common expressions across Oracle, SQL Server, Hive etc. shielding the design from the physical aspects of the generated language. Another way to reuse is within a mapping itself. In ODI 12c expressions can be defined and reused within a mapping. Rather than replicating the expression text in larger expressions you can decompose into smaller snippets, below you can see UNIT_TAX AMOUNT has been defined and is used in two downstream target columns - its used in the TOTAL_TAX_AMOUNT plus its used in the UNIT_TAX_AMOUNT (a recording of the calculation). 

You can see the columns that the expressions depend on (upstream) and the columns the expression is used in (downstream) highlighted within the mapper. Also multi selecting attributes is a convenient way to see what's being used where, below I have selected the TOTAL_TAX_AMOUNT in the target datastore and the UNIT_TAX_AMOUNT in UNIT_CALC.

You can now see many expressions at once now and understand much more at the once time without needlessly clicking around and memorizing information.

Our mantra during development was to keep it simple and make the tool more powerful and do even more for the user. The development team was a fusion of many teams from Oracle Warehouse Builder, Sunopsis and BEA Aqualogic, debating and perfecting the mapper in ODI 12c. This was quite a project from supporting the capabilities of ODI in 11g to building the flow based mapping tool to support the future.

I hope this was a useful insight, there is so much more to come on this topic, this is just a preview of much more that you will see of the mapper in ODI 12c.