[ODI 11g] What's New In ODI 11g

What's New in ODI 11g?

 Oracle Data Integrator 11gR1 includes a large number of features and 

enhancements that make ODI even better than 10g

There are major areas of changes in this release:

• New Architecture for Enterprise-Scale Deployment
• New Design-Time Experience
• New Run-Time Experience
• Core Enhancements to E-LT and Declarative Design

Enterprise Scale Architecture

The new architecture of ODI targets Enterprise-Scale deployments. Enterprise-scale means very high standards in terms of uptime availability, security, and horizontal scalability with load balancing.

Data Scalability at run-time was already ensured by the ODI E-LT architecture. In short, ODI E-LT scales linearly with your source/target data storage systems since it uses those CPUs for the data transformation processing. Yet, scaling the agents horizontally with load balancing was something not fully bullet-proof (some ODI 10g built-in load balancing was available, but without full fall-back capabilities).
High-Availability could be handled in 10g by mixing load balancing and by starting the agent Java processes as windows services.
Security was handled internally, and with some LDAP integration, but it did not encompass all ways that enterprises wanted to integrate with LDAP authentication procedures, including centralized security systems and single sign-on (SSO).

 Figure 1: ODI 11gR1 Component Architecture

Java EE vs. Standalone Agents

In 11g, the agents can optionally be installed as applications in a Oracle WebLogic Server, and automatically benefit from the clustering, load-balancing, datasources and connection pooling features available with the application server. They are by default scalable and highly available. 

Wait ! Did I say that the agent was deployed as a WLS application? Yes, but I said "optionally". The agent still (and will continue to) exists as a standalone process that can be deployed on top of a Java Machine and run on any system that has Java implementation.

Why? In some cases, the agent needs to be located close to the data, or on systems where WLS is not a viable option. In such situation, the architecture (and the performance) rely on this low footprint piece of code call the Agent.

Now, when speaking about ODI agents, we have to bear in mind that there are Standalone Agents (like in 10g) and Java EE Agents (in WLS). Well, they are (almost) the same piece of code. The main difference is where/how you install them (in WLS or on top of a JVM) and the features that you benefit from this installation.

Typically:

• The Standalone agent is easier to deploy anywhere, but does not have clustering or connection pooling. It can rely on Oracle Process Manager and Notification Server (OPMN) for being protected and monitored like a service and it can use the built-in ODI Load Balancing feature.
• The Java EE agent is slightly more complex to set up (you need to install WLS first, set up a domain, and so forth), but gives you access to a different world in terms of enterprise-scale deployment: clustering, load-balancing, centralized monitoring and so forth.

Note that the choice between the two type of agents is really a user choice, and it is easy to mix both these type of agents seamlessly into an ODI architecture. (yes, you read that correctly, the standalone and JEE agents can be used together in the same network topology!)

Other Java EE Components

The other components that require to be deployed in an application server have been also modified.

ODI Console and EM Plugin

ODI has a great history with web interfaces, in which we can find successively Repository Explorer, Metadata Navigator and Lightweight Designer.
All these interfaces are now consolidated into a single ADF-Faces application called ODI Console. This console provides features for setting up and monitoring an ODI environment, and browse the design and run-time metadata

 Figure 2: ODI Console, the ADF-Faces thin client for browsing ODI metadata.

 

Monitoring an enterprise-scale ODI infrastructure can be undertaken from the Oracle Fusion Middleware Control Console (a.k.a. Enterprise Manager - EM). ODI introduces a plugin that can be deployed in EM to monitor ODI resources (repositories, agents, ODI Console instances) and see their status, activities and notification. I will provide more details in the Part 3 of this series. 

 Web Services

Web Services to start and monitor sessions are now merged in the (Java EE and Standalone) agent. Web Service requests can directly be sent to the agent, and do not require the installation of a separate service. A dedicated service (Public Web Service) exists for web service calls that do not involve an agent (e.g. listing contexts).
The Data Services feature remains the same, but no longer require to be deployed in an Axis web service stack. A Jax-WS Stack is sufficient.

Deployment & Configuration
When you start working with Java EE applications and application servers, the deployment and configuration effort is sometimes tremendous and requires a different set of skills. To help any user to set up an enterprise scale deployment (or to deploy any of the Java EE components), ODI provides a set of cool features, including:

• Pre-packaged WLS Templates for Java EE components, including configuration and datasources ready for deployment
• Java EE Agent Template Generation, to create templates for agents based on ODI topology information.
• Remote WLS Datasource Creation from the ODI IDE. 

 

Figure 3: Template generation for Java EE agents is possible from the ODI Studio.

Your Own ODI Applications

ODI 11g provides a Java API to manipulate both the design-time and run-time artifacts of the product. This API allows you for example to create or modify interfaces programmaticaly, create your topology, perform import or export operations, launch or monitor sessions. This API can be used in any Java SE and Java EE applications, or in the context of Java-based scripting languages like Groovy or Jython.

For enterprise security systems. ODI 11g introduces support for:

• External Password Storage, to have source/target data servers (and contexts) passwords stored in an enterprise credential store.
• External Authentication, to have user/password information stored in an enterprise identity store (e.g.: LDAP, Oracle Directory, Active Directory), and ODI authenticating against this store.

These two features let you optionally store critical information in dedicated storages and not within the ODI repository. The ODI Console may also use Oracle's single-sign on systems with ODI.

Welcome to the ODI Studio !

The Oracle Data Integrator 11g client is called the Oracle Data Integrator Studio is based on the Fusion Client Platform (FCP is the platform under JDeveloper). ODI benefits from all the usability features that exist in JDeveloper-based IDEs.

One Window to Rule Them All...

The first thing that strikes when starting the the Oracle Data Integrator Studio (well, after the impression that it looks way nicer than the 10g Swing UI!), is that it merges in a single window what was before in four.

The 10g Graphical Modules (Designer, Topology, Operator and Security) now appear as Navigators within the ODI Studio Window.

These navigator include Accordions for sub-items, each accordions containing a tree views. Tree views and context menus have been reorganized in a more meaningful way and harmonized across the components.

 

Figure 1:The 10g Designer Window and 11g Designer Navigator in ODI Studio: 11g has the four "modules" shown as navigator (top tabs). Note that the Security Navigator is currently hidden). The sub-sections for the Design Navigator (Projects, Models, Others, Solution) appear as Accordions in the navigator. Notice also the menu reorganized with separators. 

Docking, Navigation & Window Management

The second thing that strikes is the organization and the management of the windows, panels and editors. All these items can be docked, un-docked or stacked (drag the title tab), maximized/minimized within seconds (double-click the title tab)

It becomes easy to navigate (CTRL-tab) between windows, and to perform Close/Save All operations on editors.

Reorganized Editors

Editors have been reorganized with finger tabs (on the left of the editor), and fields within tabs have been organized into expandable option groups.
The previous figure shows an example of this reorganization with the Technology editor.

Figure 2: A stack of editors (from the Designer and Topology Navigators) maximized in the ODI Studio window. Notice that the Save/Save all options are available from the toolbar, and the Close/Close All/Close Others are available from the context menu. The visible editor (Oracle Technology) shows example of finger tabs and option groups. Notice that the Data Handling option group in this editor has been reduced.

Interface Editor

The editor that made the biggest leap in 11g is the Interface Editor. It has been redesigned entirely !

New Diagramming

The JDeveloper diagramming framework was introduced for both the Mapping and Flow tabs. This new editor provides a better rendering of the diagrams, support for zooming and collapsible objects. Making the working through diagrams easier.

Simplified Edition

The edition was enhanced to support changes that make the developer's life easier.

• Multiple columns drag and drop from a Source into the Target Datastore to create mappings.
• Sortable and selectable columns for the target datastore.
• Searchable and reorganized Property inspectors with option groups.

 Figure 3:The mapping tab uses a new diagramming framework. The Thumbnail view (bottom left) shows a part of the source ER Diagram.

The same usability effort was made also in the Flow tab. The flow tab supports also zooming, the thumbnail, and allows viewing and edition. As shown in the screenshot below.

 

Figure 4:A filter can be edited from the flow tab.

 

Quick Editor

A major change in the interface window is the new Quick-Edit tab. The quick editor offers the possibility to view and edit the declarative rules of an interface in a tabular form. This form allows intuitive keyboard navigation, copy-paste operations (including on multiple cells), selecting and sorting displayed columns. It works like a spreadsheet !

Quick Editor is a wonderful tool for having a global view of large interfaces, or to perform maintenance on existing interfaces.

Figure 5:  Quick edit provides a tabular view of the interface mapping .

Troubleshooting Easier
In order to make the design-time experience better, several other enhancements have been made to help preventing and solving issues at design time.

Auto-Fixing/Issue Report
When clicking the Errors button in the interface editor toolbar, the list of detected design errors is displayed with meaningful messages and tips. Auto-fixes are suggested and can be applied with a single click. In addition, when saving and attempting to execute interfaces, a report is raised with all remaining issues. An example of suggested fixes is displayed below.

 Figure 6: Errors detected in interfaces are raised and automatic fixes suggested.

Code Simulation
It is possible by checking the "Simulation" option in the Execution window to generate the session, steps, tasks and code for an execution without actually running anything. The generate session is displayed as a Simulation that can be saved in XML or HTML format for troubleshooting or simply reviewing the code.

Figure 7:Review the generated code without running it!

Enhanced Errors
Errors raised in this new ODI release have been enhanced to provide more contextual information contextual information about the session, step, tasks and operation that was performed.

For example, if a server is down in 10g, the following error shows up:

java.sql.SQLException: [OWLS][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect
at

In 11g, the following error is returned, and indicates that the interface Load Customer loading target table NortwindEmps has failed because the source Microsoft SQL Server database declared as MICROSOFT_SQL_SERVER in my topology cannot be connected.

ODI-1217: Session Refresh Customer (119011) fails with return code 8000.
ODI-1226: Step Load Customer fails after 1 attempt(s).
ODI-1240: Flow Load Customer fails while performing a Loading operation. This flow loads target table NorthwindEmps.
ODI-1227: Task SrcSet0 (Loading) fails on the source MICROSOFT_SQL_SERVER connection MSSQL.
Caused By: java.sql.SQLException: [OWLS][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect
at

 

Better Session Management and Control

I have described the new architecture and how HA could be enabled in this architecture.

In this architecture, components are made highly available and will restart in case of failure; In addition to this, the following new features exist in 11g to make production environments more resilient and easier to manage:

• If an agent looses the connection to his repository and this repository is stored in a HA-enabled database - such as Oracle RAC - a Repository Connection Retry mechanism makes the agent continue his activity, including his running sessions on a different database node.
• If an agent crashes or looses the connection to the repository, crashed sessions no longer remain in running state. A Stale Session management mechanism moves these sessions to an error state.
• Users can force a Kill Immediate on a session. Currently long running database statements are aborted and the session stopped afterward.
• Session Purge with the OdiPurgeLog tool is now possible based on a number of sessions to keep in the log. This tool also supports automated archiving of purged sessions.

Better Errors and Notifications

Oracle Data Integrator now uses the Oracle Logging Framework. Logging in any component can be configured in a single way and provides simple to extremely detailed logging information.

In addition to this logging capability, agent components can now raise status and session information in the form of Java Management Extension - JMX Notifications that propagate to any administration console.

This logging and notification systems comes in addition to the Enhanced Error Messages that provide precise and contextual information, including:

• Component lifecycle Information and errors (startup, shutdown, schedule refresh, etc.)
• Session lifecycle information and errors (incorrect scenario version, load balancing issue, agent not available, etc.)
• Session Tasks/Steps information and errors (source/target not available, interface error).
• Standardized Oracle Data Integrator error codes.

ODI Console

ODI Console unifies in a single web interface all the features previously included in Metadata Navigator, Lightweight Designer and Repository Explorer.
ODI Console becomes the the thin client to:

• Browse Design-Time, Topology and Run-Time Metadata
• Set up and configure a production environment and the topology
• View the Data Lineage and Flow Map for the integration jobs.
• Monitor the run-time activity.

This web interface is redesigned using ADF-Faces components.

Figure 1: ODI Console is the thin client for accessing metadata in the repository.

In addition, ODI Console provides the capability to view and purge the content of the error tables created by the data quality check processes in the scenarios.

Fusion Middleware Control Plug-in

Fusion Middleware Control is the console used for monitoring Oracle and third-party components in an enterprise architecture. ODI Provides a plug-in in this console and allows administrators to:

• Monitor the status and metrics of the ODI Components (Repositories, Java EE and Standalone Agents, ODI Console).
• Receive the notifications raised by these components via JMX.
• Drill down into Oracle Data Integrator console
• Start and stop ODI Java EE components
• Monitor/search for session executions

 

Figure 2: Oracle Data Integrator provides a Fusion Middleware Control plug-in for monitoring ODI components.

In an enterprise-scale architecture, monitoring from a single place all the architecture components is critical. This plugin makes ODI components manageable along the other components of the Fusion Middleware stack.

Datasets and Set-Based Operators
Datasets are a big leap in interface design in ODI 11g: Imagine that you want to union in a target table information coming from a set flat files with information from a set of tables (with different transformations, of course). With ODI 10g, you start thinking about multiple interfaces, procedures and packages. Well, ODI 11g allows you to do this in a single interface.

In an ODI 11g interface, Datasets represent flows of data that are merged into a single target. Each flow corresponds to a set of sources and has its own set of transformations (mappings, joins, filters, and so forth). These different datasets are merged using set-based operators (UNION, INTERSECT and so forth).

ODI is able to generate the code corresponding to all the datasets and merge all these data flows into a single flow that can be checked and then integrated using any of the existing integration strategies.

Figure 1: Datasets can be added and managed from the mapping tab. Each dataset appears as a sub-tab of the mapping and will contain different sources, joins, filters, mappings and so forth).

Derived Select for Temporary Interfaces

When using a temporary interface as a source in another interface, it is possible not to persist the temporary datastore and generate instead a Derived Select (sub-select) statement. The temporary interface no longer needs to be executed to load the temporary datastore, and developments using temporary tables are greatly simplified. 

Figure 2: If the Use Temporary Interface as Derived Table option is selected, the temporary datastore SRC_CUSTOMER will not be persisted and turned into a sub-select instead.

Lookups
Oracle Data Integrator 11g introduces the concept of Lookup in the interfaces. Lookups are created using a wizard, have a specific graphical artifact and a dedicated property inspector in the interface window. Lookups are generated by ODI for processing in the database engine in the form of a Left Outer Join in the FROM clause or as an expression in the SELECT clause (in-memory lookup with nested loop).

Figure 3: The Lookup Wizard simplifies lookup creation.

Other Changes
In addition to these three major changes, other improvements have been made to the core product to support more databases capabilities. 

Partitioning
Partitioning information can be reverse-engineered in datastores, and specific partitions can be used when such datastore is used as a source or a target.

Figure 4: The partitions reverse-engineered with the TRG_CUSTOMER datastore can be selected when this datastore is used as a target (or a source) in an interface.

Temporary Indexing
Joins and filters in interfaces can be automatically indexed for better performances. By selecting index types on a join or a filter, the user can request that ODI creates temporary indexes on the columns participating to the join or index when this interface runs.

Figure 5: Temporary indexing for a join. An index type has to be specified for both sides of the join.

Native Sequences
ODI Sequences can now directly map to native sequences defined in a database. These sequence can be reverse-engineered. Such a sequence is used with an ODI sequence syntax (for example #PROJECT001.MYSEQUENCE) and is automatically converted to the database's syntax in the generated code.

Figure 6: A Native sequence is declared and selected from the list of sequences present in an Oracle schema.

Natural Joins
Joins in interfaces now support the Natural Join type. This join does not require any join expression, and is handled by the execution engine, which matches automatically columns with the same name.