Using Load Plans Use Case Scenario:

Earlier, John created the ODI procedure to create a relational table and populate it with data. In the last practice, John created an ODI scenario to run the procedure.

In this practice, John will use the load plan editor to create a simple load plan with a set of parallel and serial steps.

Background:

When ODI is used to populate very large data warehouses, it is common to have thousands of tables being populated by using hundreds of scenarios. The execution of these scenarios has to be organized in such a way that the data throughput from the sources to the target is the most efficient within the batch window. Load Plans help organize the execution of scenarios in a hierarchy of sequential and parallel steps for these types of use cases.

Load Plans are objects that organize the execution of packages and scenarios at a high level.

Load Plans provide features for parallel, sequential, and conditional scenario execution, restartability, and exception handling.

In this practice, you will use the load plan editor to create a very simple load plan with a set of steps running in parallel, followed by a set of steps running sequentially.

First, define a load plan.

Then, add a node for independent steps to run in parallel. Create two procedures for loading dimensions, and add them as steps to execute in parallel.

Next, add a node for interdependent steps to run in a specific sequence. Add several of your previously defined objects as steps to run in the following order:

1. DELETE_TARGET procedure, which deletes records from the TRG_SALES, TRG_CUSTOMER, TRG_CITY, TRG_REGION, and TRG_COUNTRY tables.

2. INT_8-1, which adds records to the TRG_COUNTRY table.

3. INT_8-2, which adds records to the TRG_REGION table.

4. INT_8-3, which adds records to the TRG_CITY table.

5. INT_9-1, which adds records to the TRG_CUSTOMER table.

Note: These five steps are the same set of operations you used in creating the PKG_14-1 package.

Oracle University and (Oracle Corporation) use only.

Your Tasks

1. Create a simple load plan to execute two steps in parallel, and then five steps serially.

a. In the Designer Navigator, click the Load Plans and Scenarios tab. From the tab’s menu, select New Load Plan.

Oracle University and (Oracle Corporation) use only.

b. Enter Data Warehouse Load Plan in the Name field, and click the Steps tab.

c. The load plan editor opens with the root step node. Click the Add Step button, and select Parallel Step.

Oracle University and (Oracle Corporation) use only.

d. The Parallel step node appears under the root step. You will define two new procedures and place them under the parallel node so they will execute in parallel.

Note that the default restart type for parallel steps is Restart all children, and the restart type for root_step is Restart from failure. Click Save to save your load plan.

2. Define two procedures to load PRODUCT and TIME dimensions, and add them to the load plan Parallel step node.

a. In the Projects tab of the Designer navigator, expand the HandsOnLoads > HandsOn folder. Right-click the Procedures node and select the New Procedure option.

Oracle University and (Oracle Corporation) use only.

b. Enter Load_PRODUCT_dimension in the name field. Do not enter any other

information, because this will be a nonfunctioning skeletal procedure that you create solely for the purpose of seeing how load plans work. Later, when you execute the load plan, this procedure will run, but do nothing. Click Save and close the

Load_PRODUCT_dimension editing tab.

c. Repeat the previous step to create the Load_TIME_dimension procedure. Click Save and close the Load_TIME_dimension editing tab.

d. Return to the Data Warehouse Load Plan editor. With the Parallel step highlighted, click the Add Step button and select Run Scenario Step.

Oracle University and (Oracle Corporation) use only.

You now add the two procedures you just defined as steps under the Parallel node of the load plan.

In the Add Run Scenario Step screen, click the Lookup Scenario button.

f. In the Executable Object Type field, select Procedure. Select Load_PRODUCT_dimension. Click OK.

Oracle University and (Oracle Corporation) use only.

g. In the New Scenario window, accept the name LOAD_PRODUCT_DIMENSION as the name of the scenario that ODI will create to run this procedure, and click OK.

h. ODI returns to the Add Run Scenario Step window.

Note that LOAD_PRODUCT_DIMENSION is the name of the new scenario that ODI will generate for the procedure, as well as the name for the step you are adding to the

load plan. Click Finish.

Oracle University and (Oracle Corporation) use only.

i. The first of your two parallel steps appears. Click the Parallel node of the Steps Hierarchy, and click the Add Step button again. Select Run Scenario Step.

j. Repeat steps d. through g. to add your other new procedure, Load_TIME_dimension, to the Parallel node of the Steps Hierarchy. The result should look like the following screenshot.

Oracle University and (Oracle Corporation) use only.

3. Add ODI objects to the load plan as serial steps. These are the same objects you used earlier to create the PKG_14-1 package.

a. Click root_step in the Steps Hierarchy. Click the Add Step button and select Serial Step.

b. The Serial node appears in the Steps Hierarchy. With Serial node selected, Click the Add Step button and select Run Scenario Step.

c. In the Add Run Scenario Step window, click Lookup Scenario .

Oracle University and (Oracle Corporation) use only.

d. Select Procedure as the executable object type. Select DELETE_TARGET. Click OK.

In the New Scenario window, accept the name DELETE_TARGET as the name of the scenario that ODI will create to run this procedure, and click OK.

Oracle University and (Oracle Corporation) use only.

e. ODI returns to the Add Run Scenario Step window. Click Finish.

f. The first of your five serial steps appears. Click the Serial node of the Steps Hierarchy, and click the Add Step button again. Select Run Scenario Step.

g. Click Lookup Scenario and select Interface.

Oracle University and (Oracle Corporation) use only.

h. You will add four interfaces to the Serial node: INT_8-1, INT_8-2, INT_8-3, INT_9-1.

First, select INT_8-1 and click OK. In the New Scenario window, accept the name INT_8-1 as the name of the scenario that ODI will create to run this interface, and click OK. ODI returns to the Add Run Scenario Step window. Click Finish.

Oracle University and (Oracle Corporation) use only.

i. Repeat steps f. through h. to add the remaining three interfaces: INT_8-2, INT_8-3, and INT_9-1. Your results should look like the following screen. Note the default restart values for the different types of steps. Click Save to save your load plan.

4. Now, execute the load plan and examine the list of executed steps in the Operator Navigator.

a. Click to execute the Load plan. Click OK in the Start Load Plan window, and then click OK when the “Load Plan started” message appears.

Oracle University and (Oracle Corporation) use only.

b. Open the Operator Navigator, click Refresh, and examine the results of executing the load plan. The seven steps in the load plan appear as a grouping of seven executions.

For each load plan step, ODI generated a scenario for execution.

First, the procedures for loading the TIME and PRODUCT dimensions executed in parallel. Then, the DELETE_TARGET procedure and the four interfaces executed in the sequence you defined.

Oracle University and (Oracle Corporation) use only.