ODI 11g New Mapping and Interface Features - Part 2 - Load Plans

Yesterday I posted on the blog an overview of some of the new interface (mapping) features in the original, release of Oracle Data Integrator 11g. In today's posting, I'm going to look at the first of two new features introduced with the ODI that are particularly interesting: Load Plans.

The release of ODI is available for download on OTN and Edelivery, and you can either do a fresh install, or upgrade from either the 10g or version of ODI. The 10g to 11g upgrade process, like OBIEE, is carried out using the Upgrade Assistant, whilst the to upgrade, again like OBIEE, is the awful "Fusion Middleware Patching" process that involves running lots of scripts, upgrading repositories and so on. Once you're there though, one of the first major new features you'll see in is Load Plans.

As a quick recap, ETL routines using ODI are made up of Interfaces, which are typically organized into packages. You can either create packages, like this, where you drag individual interfaces onto the canvas, and each interface is then executed serially, one after the other as one session:

Sshot 10

Or you can compile the individual interfaces into "scenarios", and then use the scenarios in a package instead.

Sshot 11

The advantage of using scenarios rather than interfaces in a package, apart from the fact you're working with a fixed, compiled version of the interface that in theory won't change from run-to-run, is that each scenario can be run in it's own session, and execute asynchronously, with an ODI tool at the end that checks their statuses and fails over if one of the child sessions errors.

In fact running interfaces and packages in parallel, something fairly common in large ETL routines, was something fairly tricky in ODI before Typically you'd use the compiled scenarios trick to get things running asynchronously, and you'd also set up and run several agents each of which handled a certain number of sessions before load-balancing off to another agent. Oracle did try and tackle this problem, along with sequencing and combining of sets of interfaces, with the release of the BI Apps which used ODI, along with a tool called the Configuration Manager, to create load routines out of subject areas each of which had dependencies, run orders and so on. It wasn't as powerful as the DAC, but combined with packages it went some of the way to meeting the BI Apps requirements, albeit in a pretty inflexible way.


ODI introduces a new concept to handle this requirement, called Load Plans. Load Plans are executable objects that you create in your work repository, can be compiled themselves and imported into an Execution Work Repository, can run from the command-line, and are a way of adding nesting, sequencing, parallelism, error handling and restarting to your ODI project.

Going on the same example as before, where we are first loading some staging data and then insert/updating into some warehouse tables. a typical ODI Load Plan would look like this:

Sshot 12

When you create a load plan, individual steps are added in which can either be a Serial Step, a Parallel Step, a step to run an individual Scenario, and a Case step for conditional logic.

Sshot 13

In the example above, we have a serial step at the start to refresh some project variables, then we load in parallel the staging tables used to feed the dimensions. Later on, we have a CASE step that checks a project variable and then only executes the rest of the plan if DW_REFRESH=1.

Something that will interest Stewart Bryson, is the ability to define resume/restart settings for individual steps and groups of steps. The default action for an interface scenario failing is for the whole load plan to fail, but you can define "exception", procedures and other steps to, for example, flashback a table or set of tables, or run some kind of clean-up routine.

Sshot 14

These exceptions can then be associated with steps and groupings, so that the exception is run after a step failure which will then either re-run, re-start from the parent-process or similarly for the whole load plan.

Sshot 15

Presumably this is the sort of new feature that's making it's way into ODI because of requirements coming from the BI Apps, but it should be useful for most developers working with ODI. It doesn't replace the concept of packages - packages are typically "transaction-style' ETL routines that don't get edited after deployment and can also take advantage of loops, which load plans don't have (but then again, load plans have conditional execution using CASE ... WHEN ... ELSE). Uli Bethke, who uses ODI extensively, came up with some initial limitations that he found with load plans which are worth taking a look at, and this posting by FX from the ODI team goes into the feature in a bit more detail.

To wrap-up this mini-series, tomorrow I'll take a look at a new ODI feature that'll also be interesting to OBIEE developers - Oracle Business Intelligence Enterprise Edition Data Lineage.