2 Managing Load Plans
The tasks for managing load plans are usually performed by either Extract, Transform, and Load (ETL) developers or ETL operators using Oracle BI Applications Configuration Manager.
ETL Developer Tasks
Overview of Load Plan Life Cycle
A load plan life cycle comprises four phases.
-
Phase 1: Define load plan
In this phase, you define load plan properties in Configuration Manager, including selecting a data source and one or more fact groups. This selection determines which steps you must perform during the ETL process.
-
Phase 2: Generate load plan
In this phase, you launch a generation process from Configuration Manager that propagates the load plan properties to the Oracle Data Integrator (ODI) Repository, where the load plan is built.
-
Phase 3: Execute load plan
In this phase, you start a load plan run from Configuration Manager, which executes the steps of the load plan. Executing a load plan creates a load plan instance and a first load plan run. If a run is restarted, a new load plan run is created under this load plan instance. Each execution attempt of the load plan instance is preserved as a different load plan run in the log.
-
Phase 4: Monitor load plan
In this phase, you monitor the load plan run in the Load Plan Details page of Configuration Manager. The Load Plan Details page provides a view of the ODI Repository through ODI Console.
Defining Load Plans
Define load plan properties in Configuration Manager, including selecting a data source and one or more fact groups. This selection determines which steps you must perform during the ETL process.
- In the Tasks pane of Configuration Manager, select Manage Load Plans under Load Plans Administration.
The Manage Load Plans page is displayed.
Duplicating Load Plans
You can duplicate an existing load plan from the Manage Load Plans page of Configuration Manager. A duplicated load plan is displayed in the Load Plans master list immediately. You can generate the duplicated load plan instantly.
Editing Load Plans
You can edit details of an existing load plan, such as load plan name, load plan type, and the data sources from the Manage Load Plans page of Configuration Manager. An edited load plan is displayed in the Load Plans master list immediately. You can generate the edited load plan instantly.
Generating Load Plans
When you generate a load plan, the load plan is built in the ODI Repository. A load plan must be successfully generated before it can be executed.
Load plans must be generated serially, otherwise the process fails. Do not launch a second load plan generation if one is already underway. You must wait until the first generation process completes before you launch the next generation process.
To generate load plans:
- In the Load Plans toolbar, click the Generate icon.
The possible icons that are displayed in the Generation Status column of the Load Plan master list are described in the following table. Click the Refresh icon to refresh the display.
Generation Status Icon Description 
Starting
In Progress
Succeeded
Failed
You can execute a load plan or schedule it for execution after it has been successfully generated.
Scheduling Load Plans
You can schedule a load plan by defining its ODI context, ODI local agent, a level of logging information, status of the schedule, and frequency of occurrence in Configuration Manager.
Executing Load Plans
You can execute a load plan after you generate and schedule it.
-
You can have separate load plans for each source, but load plans should not run in parallel.
-
You can only execute a load plan if it was successfully generated. See Generating Load Plans.
To execute a load plan:
- Click OK.
The following icons show the possible execution statuses that are displayed in the Execution Status column in the Load Plan master list. Click the Refresh icon to refresh the display.
Generation Status Icon Description 
Starting
In Progress
Succeeded
Failed
Not Executed
About Diagnostics Health Check and ETL Diagnostics and Automatic Correction
Diagnostic Health Check is a preliminary ETL phase in which a diagnostic report is generated to identify problematic source data that might cause ETL failure or data loss or corruption in the data warehouse. In the event of a failure of an ETL task, diagnostics are run and error handling and automatic correction are performed to enable the load plan to restart and continue.
During ETL, when you run a load plan, the relevant source data is queried to generate a health check report. The health check queries are seed data XML files and can be found at {bi.oracle.home}/biapps/etl/data_files/src_files/BIA_11/metadata/healthcheck_xml. You can download a health check report from Configuration Manager and the health check report includes any problematic data, a description of detected issues, and actions to resolve health check failures.
Email Notifications
-
If the health check scenario fails, an email is sent with a summary of the health check results.
-
If the load plan execution fails, an email is sent with a list of failed tasks during that load plan execution.
-
If the load plan execution completes, an email is sent with a list of tasks that are in failed state in any of the load plan instance runs and a summary of auto-corrections performed (if any), during the ETL.
Automatic Correction of Data Errors
Some common data errors that can cause ETL tasks to fail are automatically diagnosed and corrected during the ETL, allowing for the load plan to restart and complete.
| Data Error Type | Automatic Correction |
|---|---|
| Duplicate rows being inserted into the target table causes a unique constraint violation. | One row is inserted and the duplicate rows are rejected. |
String values larger than allowed being inserted into varchar or char columns.
|
All varchar columns are truncated to the precision allowed by each column before insert. Exception: key columns defined in the interface are not truncated.
|
All other issues, including:
|
Row is rejected. For dimension load tasks, instead of rejecting rows, the row is inserted into the target table with the unspecified value for all the columns except the key columns (typically INTEGRATION_ID and DATASOURCE_NUM_ID). This is done to ensure that fact rows inserted during ETL which refer to a dimension row, don’t have a dangling foreign key. |
The following Knowledge Modules have been enhanced to support diagnostic features during load plan execution process:
-
IKM BIAPPS Oracle Control Append
-
IKM BIAPPS Oracle Incremental Update
-
IKM BIAPPS Oracle Fact Incremental Update
-
IKM BIAPPS Oracle Slowly Changing Dimension
-
IKM BIAPPS Oracle Event Queue Delete Append
-
IKM BIAPPS Oracle Fact Event Queue Delete Append
-
IKM BIAPPS CLOUD Oracle Incremental Update
-
CKM BIAPPS Oracle
Package Structure for Error Handling and Automatic Correction
All ODI packages that have a main interface step using one of the diagnostic-enabled IKMs include a loop as shown in the example here. Each interface step in the package that uses one of the IKMs has a ko flow. At a high level, if there is no failure in the interface step, the scenario execution completes successfully after executing the interface step. However, if there is a failure in the interface step, the control is transferred to the ko flow, which executes Refresh DIAG_EXEC_NUMBER and Evaluate DIAG_EXEC_NUMBER steps and then re-runs the interface step in diagnostic mode. The interface step now tries to capture Data Manipulation Language (DML) data errors (if any) in an error table and tries to auto-correct them based on a set of predefined rules as described, for example trimming string lengths in case of a string length overflow error.
If the diagnostic framework can’t automatically correct the error records, they are rejected in an error table so that the non-error rows can be loaded in the target and the scenario execution can complete successfully.
If the failure was caused by a system error or error in LKM steps, the interface step is more likely to fail again, in which case the control is transferred to Diagnostic Raise Exception procedure step and fail the scenario execution.
Enabling and Disabling Diagnostic Features
ETL diagnostics and automatic correction can be turned on or off using two parameters in Configuration Manager, both of which are set to Y by default:
-
DIAG_ERR_LOG_SUPPORTED — This parameter controls whether the ODI IKM code performs any auto-corrections for DML data errors or not. If disabled, ODI IKM does not attempt to perform any automatic corrections on data. Upon failure of a task, it still re-runs the interface step in diagnostic mode, but raises an error after attempting to capture the data errors.
-
DIAG_AUTOCORRECT— This parameter controls the ETL diagnostic and auto-correction feature. When disabled, failed interface steps are not re-run in diagnostic mode.
To reset these parameters, navigate to the Manage Data Loads Parameters page in Configuration Manager and set them to N. Your load plans are regenerated after resetting these parameters.
Downloading Diagnostic Health Check Reports
The health check report includes any problematic data, a description of issues, and actions to resolve health check failures. You can generate a health check report of your load plan by querying the relevant source data in Configuration Manager.
- Click Show Data Problems.
Downloading Error Data from Diagnostics
You can download error data from automatic ETL diagnostics in a zip file. In this zip file, you see some system columns to describe the error number, error message, and automatic correction done by the ETL program.
To download error data:
- Click Show Data Problems.
Monitoring Load Plan Runs
You can monitor a load plan run by viewing the execution status information on the Load Plan Execution Details page of Configuration Manager.
To monitor a load plan:
Restarting Load Plans
When you run ETL to load data from a source system into the Oracle Business Analytics Warehouse, you might need to restart the ETL load after a failure. This section explains the options available for restart and describes the implications of using each of those options.
Examples of circumstances and reasons for load plan failure include the following:
-
Issues identified in the health check ETL phase. Fix these issues before restarting the load plan.
-
Problem with access either to source or target database due to network failure or expired or otherwise incorrect user names and passwords.
-
Failure of ODI agent.
-
Problem with space or storage. You can connect to the source or target database, but the query fails to run due to lack of temp space, disk space, and so on. It might be due to inadequate space where files need to be placed.
-
Problem with data, for example incorrect data with lengths larger than the target column can hold, or null values in Not Null columns.
After such a failure during ETL, to avoid restarting the entire load plan after a failure, which requires inefficient re-runs of all ETL tasks, you must restart the load from the same point in its execution once the cause of failure has been diagnosed and resolved. Some of these circumstances for failure are automatically diagnosed and resolved by ETL diagnostics and auto-correction. See About Diagnostics Health Check and ETL Diagnostics and Automatic.
About Restartability Grain
When you restart a load plan after a failure, you might not restart again from the exact point of failure, depending on where it occurred and dependencies between load plan steps.
The point of restartability is that the end result of the load plan execution is the same regardless of any load plan failure. The following example describes one such dependency-driven requirement for re-running a step that has already completed: In a load plan with two steps, the first step truncates the table, and the second inserts records into the table, intermittently committing the records. The load plan is run and fails at the second step due to a space issue. After the issue is resolved, restarting the load plan from the second step is incorrect, because the target has some inserted rows. Restart should instead begin with the first step so that the target table is truncated again and newly inserted data does not cause any duplicates.
To maintain data integrity in the case of restart, the grain varies depending on the location in the step hierarchy of the failed step and on the Restart setting for the step in the Load Plan Manager.
Within the Steps Hierarchy in Load Plan Manager, you can view and edit the Restart setting of a step in the Restart column. The default settings for different steps in the hierarchy support data integrity in restarts:
-
Root steps are set to Restart From Failure if Serial and Restart from failed Children if Parallel.
-
Sub steps are set to Restart From Failure if Serial and Restart from failed Children if Parallel.
-
Scenario steps are set to Restart from Failed Step.
The following examples highlight the implications for each type of load plan step:
Serial Load Plan Step
Serial steps are represented by a horizontal icon in the Steps hierarchy in Load Plan Manager, and by default have a Restart setting of Restart from Failure. In a case where the load plan fails when running such a step to load a Dimension Group with multiple serial sub-steps loading individual dimensions, the load plan on restart, starts from the individual sub-step that failed. Any successfully completed serial sub-steps are run again.
Parallel Load Plan Step
Parallel steps are represented by a vertical icon in the Steps hierarchy in Load Plan Manager and by default have a Restart setting of Restart from Failed Children. In a typical run, a parallel step with five parallel sub-steps under it has all five sub-steps executed in parallel, subject to free sessions being available. If two of those five steps completed and then the load plan fails, when the load plan is restarted, all the steps that did not complete or failed are started again.
Scenario Step
At the lowest order in any load plan are the scenario steps. While the parent steps, whether serial or parallel, are used to set the dependencies, the scenario steps are those that load the tables. A scenario step, in turn, can have one or more sub-steps, corresponding to the number of steps inside the package.
In the case of a scenario step failure during execution, consider that the scenario step might have multiple steps, all under the same session in the Operator log but identified with different step numbers: 0, 1, 2, and so on. In the case of restart, the scenario executes from the failed parent scenario step, re-running all sub-steps.
Note:
If you use the Load Plan Generator to generate a load plan, it automatically conforms to the standard. If you are manually altering a generated load plan or creating a new load plan without using Load Plan Generator, then you should ensure that you conform to the standard.Restarting Load Plans Using ODI Studio
Use ODI Studio or ODI Console to restart a load plan.
Note:
When you restart the load plan, set the Log Level to 6. Log Level 6 enables you to see variable values resolved in session logs.To restart a load plan using ODI Studio:
Restarting Load Plans Using ODI Console
Note:
When you restart the load plan, set the Log Level to 6. Log Level 6 enables you to see variable values resolved in session logs.To restart a load plan using ODI Console:
Troubleshooting Load Plans
A load plan must be restarted when it has stopped with an error. An alternate case where restart might be required is when a load plan is not doing anything at all, for example, when a load plan is executed and nothing has changed after 30 minutes.
To troubleshoot a non-responsive load plan:
Alternate Options for Restarting Load Plans
You can restart failed load plans using these alternate methods.
Using Mark as Complete
In most cases the load plan restart method described earlier in this section is the recommended approach. This approach ensures data integrity and leaves no scope for manual error. However, at times you might want to run a load plan step manually. For example, if a step is inserting duplicate records which are causing failure, rerunning the step still inserts duplicates. In such a case, you might need to manually correct the data outside of the load plan and then skip that step when you restart the load plan. For this kind of situation, you can use the Mark as Complete option.
When you mark a load plan step as complete, it ensures that when the load plan is restarted, the marked step is not executed. It is then the responsibility of the person making this setting to ensure that the load for that step is carried out outside the load plan.
To mark a step as complete, right-click the step and select Mark As Complete. You can do this at the scenario step or at any step higher than that.
Marking a step complete at a higher level in the step hierarchy means that none of the child steps under that parent step are executed upon load plan restart, even if they are otherwise eligible. For this reason, you should treat marking a step as complete as an advanced task and must mark a step complete with a full understanding of its impact. There is no single recommendation that pertains in all cases. Hence, you should make the setting carefully and on a case-by-case basis.
Running a Scenario Standalone
When you are monitoring a load plan, you might not completely know how to fix a scenario step failure, but you might want to use the Mark as Complete option for the failed scenario step instead of waiting for complete resolution. This prevents a step failure from precluding an entire load plan completing, while allowing you to inform the ETL team about the failed scenario step and work on a resolution. The ETL team might then fix the scenario and want to run it standalone outside the load plan to complete the load.
As in marking a step as complete, you should treat running a scenario standalone as an advanced task and you must bear these guidelines in mind:
-
A scenario run outside of a load plan by itself invokes the Table Maintenance process. This can, depending on the setting, truncate the table before the load.
To understand this, consider that when a scenario is run inside a load plan, the table maintenance tasks are carried out as explicit steps (the parent step name can be either Initialize or Finalize). The scenario by itself does not invoke the Table Maintenance process when run from within the load plan. Rather, this is controlled by the EXECUTION_ID variable, which is set to the load plan instance ID. If this variable has a value greater than 0 when a scenario is run, the Table Maintenance process is not invoked. This is the case when a scenario is run from within a load plan with an instance ID. However, if this variable does not have a value greater than 0, then the scenario invokes the Table Maintenance process. This is the case when a scenario is run outside the load plan. If you set a value for the EXECUTION_ID when invoking the scenario from outside a load plan, the table maintenance steps are not executed.
-
A scenario step can have many variable values set, either dynamically in the case of a refresh variable, or explicitly by overriding its value at that scenario step in the load plan. When running a scenario outside the load plan, all the scenario variables must have only their default values. For this reason, you should set the variables appropriately before executing a scenario from outside the load plan. You can check the variable values that are present in the load plan by looking at the Operator log, provided the log level was set to 6 when the load plan ran. Configuration Manager uses Oracle Diagnostic Logging. See Managing Log Files and Diagnostic Data in Oracle Fusion Middleware Administrator's Guide.
Related Features and Considerations
These features of Oracle Business Intelligence Applications are related to restartability and describe some other related considerations.
Using CKM to Filter Erroneous Data
If a scenario step is failing due to bad source data, you can enable the CKM option to load the valid records and route the error records to a separate table. Examples of situations where this might be appropriate are the load of null values when they should have a value or data lengths longer than allowed target column lengths. Once the load completes, you can correct the erroneous data and have it automatically picked up in a subsequent load.
Note:
Use of CKM can slow the load considerably because every record and column might be checked before loading. For this reason, this is not an option that you want to turn on across the entire load plan.Note:
Error handling and logging of erroneous data is now automatically handled by the ETL process. See About Diagnostics Health Check.Regenerating a Scenario During a Load Plan Execution
Consider a case where a load plan is started and fails at a scenario step. You fix the issue and regenerate the scenario, then restart the load plan and might expect it to pick the new scenario, but this is not what happens. If a load plan has been started and a scenario regenerated, the regenerated scenario code is not picked up when the load plan is restarted. To force the regenerated scenario to be picked up, you have two options:
-
Start a new load plan run, accepting the overhead associated with restarting the load from the beginning.
-
Run the regenerated scenario as stand-alone outside the load plan, marking that scenario step as complete in the load plan before restarting the load plan.
See Alternate Options for Restarting Load Plans for implications of using these options.
Restarting Long Running Jobs
Consider a case where you have a scenario that takes two hours to run. The scenario fails at the insert new rows step, after loading the C$ and I$ steps. On restart, the scenario attempts to reload the C$ again. You instead want it to restart from the insert new rows steps only. This use is not supported. The restartability mechanism has been put in place in such a way that restarting a load plan is all you need to do. You do not need to clean up any data in between load plan executions, because the data is committed to the target table only after all the Knowledge Module steps are successful. If the load fails before complete success, no data is committed to that specific target table as part of the failed session.
Note:
New C$ and I$ tables are created on restart and hence data to these tables is committed in between load plan start and restart.Stale C$ and I$ Tables
On restart, new C$ and I$ tables are created, but since the previous load did not complete, these tables from the previous session are not dropped. The load plan generated using Load Plan Generator has a step at the end of the load plan executed Clean Stale Work Tables which takes a variable called ETL_DTOP_STG_OLDER_THAN_DAYS whose default value is 30 days. When this step runs, it drops any C$ and I$ tables that are older than the specified variable value.
Note:
The C$ and I$ tables are useful tables when you want to debug a failed scenario. It might not be advisable to set the ETL_DTOP_STG_OLDER_THAN_DAYS value as too small—for example, one day—as you might lose valuable information for debugging.Stopping Load Plans
You can stop a load plan run from by accessing the Load Plan Execution page in Configuration Manager (click Show Execution Status Details in the toolbar) or from ODI Studio.
To stop a load plan:
To stop load plan runs from ODI Studio, see Stopping a Load Plan from ODI Studio in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator .