Oracle Blog

Inside Oracle's Data Integration community

Monday Oct 27, 2014

Oracle's product strategy around the Oracle Business Intelligence Analytics (OBIA) has been published this October in the latest Statement of Direction.

Interesting points relative to the BI Applications around data integration:

Oracle’s strategic development
for ELT for BI Applications will focus on the Oracle Data Integrator and
related technologies. Since the fielding of the ODI compatible version of BI
Applications in the 11g series, customers have realized substantial financial
and operational benefits from reduced time to value and improved speed of
operations. Oracle continues to evolve and develop ODI, and Oracle’s BI
Applications will take advantage of the latest capabilities as they become
available.

Oracle will continue to support
the 7.9.6.x product series according to the Oracle Lifetime Support policy
including certifications of databases, operating systems, and enabling 3rd party technologies. However, Oracle will no longer develop new content for this series, nor extend the 7.9.6.x support or any series based on an ETL architecture with Informatica.

You can find the related blog entry with additional details from the BI Team here.

Monday May 12, 2014

As of May 8, 2014, Oracle Business Intelligence (BI)
Applications 11.1.1.8.1 is available on the Oracle Software Delivery Cloud(eDelivery), and on the Oracle BI
Applications OTN page. This is the second major release on the 11g code line leveraging the power of
Oracle Data Integrator (ODI), and certified with the latest version of Oracle
BI Foundation 11.1.1.7.For more details
on this release and what’s new – check it out!

In Oracle Business Applications 11.1.1.7.1 (OBIA), the Data
Warehouse load is performed using Oracle Data Integrator (ODI). In ODI, using packages and load plans, one can
create quite a complex load job that kicks off many scenarios in a coordinated
fashion. This complex load job may run for a long time and may fail before
completing the entire job successfully and will require restarts to recover
from failure and complete successfully.

This blog uses the complex load plan defined in Load Plan
for Oracle Business Applications 11.1.1.7.1 (OBIA) to illustrate the method of
recovery from failures. Similar methods can be used in the recovery of complex
load plans defined independently in Oracle Data Integrator (ODI). Note that
this post does not go into the details of troubleshooting a failed load plan
and only talks about the different restart parameters that affect the behavior
of a restarted job.

The failures can happen due to the following reasons:

Access failure – Source/Target DB down, network
failure etc.

Agent failure.

Problem with the Database – As in running out of
space or some other DB related issue.

It is important to find out the reason of failure and
address it before attempting to restart the load plan otherwise the same
failure may happen again. In order to recover from the failure successfully the
recover parameters in the load plan steps need to be selected carefully. These
parameters are selected during design time of the load plan by the developers.
The goal is to be able to make the restarts robust enough so that the
administrator can do restart without knowing the details of the failed steps.
This is why it is the developer’s responsibility to select the restart
parameters for the load plan steps in such a way which guarantees that the
correct set of steps will be re-run during restart to make sure that data
integrity is maintained.

In the case of OBIA, the load plans have appropriate restart
parameters in the generated load plans for out of the box steps. If you are
adding a custom steps then you need to choose similar restart parameters for
the custom steps.

Now let us look at a typical load plan and the restart
parameters at various steps.

Restart of a
serial load plan step:

SDE Dimension Group Step highlighted above is a serial step.
Let us say the Load plan failed when running the 3 SDE Dims GEO_DIM step. Since
this is a serial step and it has been set to “Restart from Failure”, the load plan on restart would start from 3
SDE Dims GEO_DIM only and not run the 3 SDE Dims USER_DIM again. This parameter
is widely used in the OBIA serial steps.

The other restart parameter for a serial steps is “Restart
all children”. This will cause all the children steps to be re-run during
restart even if only one failed and others succeeded. This parameter could be
useful in some case and developers will decide that.

Restart of a
parallel load plan step:

The Workforce Dependant Facts Step highlighted above is a
parallel step with restart parameter set to “Restart from failed children”. It means all the 5 parallel steps
under it would be kicked off in parallel (subject to free sessions being
available). Now amongst those 5 steps let us say 2 of them completed (indicated
by the green boxes above) and then the load plan failed. When the load plan is
restarted all the steps that did not complete/failed, will be started again (in
this example being Learning Enrollment Fact, Payroll Facts and Recruitment
Facts). This parameter is widely used in the OBIA parallel steps.

The other restart parameter for a parallel step is “Restart
all children”. This will cause all the children steps to be re-run during
restart even if only one failed and others succeeded. This parameter could be
useful in some case and developers will decide that.

Restart of the
scenario session:

At the lowest order in any load plan are the scenario steps.
While the parent steps (serial or parallel) are used to set the dependencies,
the scenario steps are what finally load the tables. A scenario step in turn
could have one or more steps (corresponding to number of steps inside the
package).

It is important to understand the structure of a session
that gets created for the execution of a scenario step to understand the
failure points and how the restart takes place.

The following diagram illustrates different components in a
session:

The restart parameters for the scenario steps in the load
plan are:

Restart from a new session – This creates a new
session for the failed scenario during restart and executed all the steps
again.

Restart from a failed task – This uses the old
session for the failed scenario during restart and starts from the failed task.

Restart from a failed step – This uses the old
session for the failed scenario during restart and re-executes all the tasks in
the failed step again. This is the most
common parameter used by OBIA and is illustrated below.

In the above example, scenario step 2 failed when running. It internally has 3
steps (all under the same session in Operator log but identified with different
step numbers 0,1,2 in above case).As
per the setting corresponding to OBIA Standard, the Scenario would execute from
Failed Step which is from Step number 2 Table_Maint_Proc (and the substep 3
Initialize Variables onwards as shown in diagram).

Note that the successful tasks such as “3 – Procedure –
TABLE_MAINT_PROC – Initialize variables” will be executed again during restart
since the scenario restart parameter is set to “Restart from failed step” in
the Load Plan.

Summary:

OBIA has certain coding standard for setting up restart
parameters as discussed above. For serial and parallel steps, the parameters
“Restart from failure” and “Restart from failed children” allow the completed
steps to be skipped. For scenario steps (which actually kick of the load
sessions) the restart parameter of “Restart from failed step” skips the
completed steps in the session and reruns all the tasks in the failed step,
allowing recovery of an incomplete step.

This standard allows a hands free approach to restart a
failed load plan by an administrator who has no knowledge of what the load plan
is doing.