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, 11.1.1.3 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 11.1.1.5 that are particularly interesting: Load Plans.

The 11.1.1.5 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 11.1.1.3 version of ODI. The 10g to 11g upgrade process, like OBIEE, is carried out using the Upgrade Assistant, whilst the 11.1.1.3 to 11.1.1.5 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 11.1.1.5 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:

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

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 11.1.1.5. 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 7.9.5.2 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 11.1.1.5 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 11.1.1.5 Load Plan would look like this:

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.

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.

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.

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 11.1.1.5 feature that'll also be interesting to OBIEE developers - Oracle Business Intelligence Enterprise Edition Data Lineage.