This chapter describes how to execute the Load Sales Administration Package you have created in Chapter 7, "Working with Packages" and the intergration interfaces Pop. TRG_CUSTOMER and Pop. TRG_SALES you have created in Chapter 6, "Working with Integration Interfaces". This chapter also describes how to follow the execution and how to interpret the execution results.
This chapter includes the following sections:
This section contains the following topics:
To run the Load Sales Administration Package:
In Designer Navigator, expand the Packages node under the Sales Administration node.
Select the Load Sales Administration Package.
Right-click and select Execute.
In the Confirm Dialog click Yes.
In the Execution Dialog, leave the default settings and click OK.
The Session Started Information Dialog is displayed. Click OK.
Oracle Data Integrator now starts an execution session.
Through Operator Navigator, you can view your execution results and manage your development executions in the sessions.
To view the execution results of the Load Sales Administration Package:
In the Session List accordion in Operator Navigator, expand the All Executions node.
Refresh the displayed information by clicking Refresh in the Operator Navigator toolbar. The Refresh button is:
The log for the execution session of the Load Sales Administration Package appears as shown in Figure 8-1.
This section describes how to determine the invalid records detected by the Pop. TRG_CUSTOMER interface. These are the records that do not satisfy the constraints and have been rejected by the flow control of the Pop. TRG_CUSTOMER interface.
This section includes the following topics:
To determine the number of records that have been processed by the Pop. TRG_CUSTOMER interface (this is the number of inserts, updates, deletes, and errors):
In the Session List accordion in Operator Navigator, expand the All Executions node.
Refresh the displayed information clicking Refresh in the Operator Navigator toolbar menu.
Expand the Load Sales Administration Package Session and open the Session Step Editor for the Pop. TRG_CUSTOMER step. This is step 4.
On the Definition tab of the Session Step Editor, you can see in the Record Statistics section that the loading of the TRG_CUSTOMER table produced 25 inserts and isolated 9 errors in an error table.
Figure 8-2 shows the Record Statistics section of the Session Step Editor:
In this example, the resulting data are the 25 rows that have been inserted in the TRG_CUSTOMER table during the interface execution.
To view the data resulting of your interface execution:
In Designer Navigator, expand the Models accordion and the Sales Administration - HSQL model.
Select the TRG_CUSTOMER datastore.
Right-click and select View Data to view the data in the target table.
Note that you can also select Data... to view and edit the data of the target table.
The View Data Editor is displayed as shown in Figure 8-3.
You can access the invalid records by right-clicking on the datastore in your model and selecting Control > Errors...
To review the error table of the TRG_CUSTOMER datastore:
In Designer Navigator, expand the Sales Administration - HSQL model.
Select the TRG_CUSTOMER datastore.
Right-click and select Control > Errors...
The Error Table Editor is displayed as shown in Figure 8-4.
The interface that you have executed has identified and isolated 9 invalid records in an error table that was automatically created for you.
In this error table, you can see that the interface rejected:
2 records that did not satisfy the FK_CUST_CITY constraint (for example, the CITY_ID value does not exist in the table of cities SRC_CITY table).
7 records that did not satisfy the business rule acting on customers under 21 (AGE > 21 constraint).
The invalid records were saved into an error table and were not integrated into the target table.
To rectify invalid data:
In Designer Navigator, expand the Orders Application - HSQL model.
Select the SRC_CUSTOMER datastore.
Right-click and select Data.
The Data Editor is displayed as shown in Figure 8-5.
In the Data table, search for the client row having a CUSTID equal to 203
.
Note that you can sort the table by clicking on the column headers. If the customer 203 is not visible, click Refresh data in the menu toolbar to refresh the display.
The CITY_ID value of this customer is 208
. This CITY_ID is not listed in the SRC_CITY table. Double-click on the value of the CITY_ID column for this customer in order to modify it. Enter 107
in the CITY_ID field.
Press Enter to validate your entry.
The Data Editor is displayed as shown in Figure 8-6.
In the menu toolbar, click Post changes to current row.
In the Projects accordion, select the Pop. TRG_CUSTOMER interface in the Sales Administration model.
Right-click and select Execute. This executes only the Pop. TRG_CUSTOMER interface.
In the Execution Dialog and in the Information Dialog click OK.
The Pop. TRG_CUSTOMER interface is executed.
To review the processed records:
In Operator Navigator, open the Session Step Editor for the Pop. TRG_CUSTOMER step.
If required, click Refresh in the Operator Navigator menu toolbar.
On the Definition tab of the Session Step Editor, you can see in the Record Statistics section that the loading of the TRG_CUSTOMER table produced 1 insertion (this is the record that you have modified in Section 8.1.3.4, "Correcting Invalid Data") and isolated 8 errors in an error table.
Figure 8-8 shows the Record Statistics section of the Session Step Editor.
This section contains the following topics:
The Pop. TRG_SALES integration interface has already been executed by the Load Sales Administration package in Section 8.1.1, "Run the Package". This section describes how to execute only the Pop. TRG_SALES interface.
To run the Pop. TRG_SALES integration interface:
In Designer Navigator, expand the Interfaces node under the Sales Administration node.
Select the Pop. TRG_SALES Interface.
Right-click and select Execute.
In the Confirm Dialog click Yes. The interface is saved and the Execution Dialog is displayed.
In the Execution Dialog, leave the default settings and click OK.
The Session Started Information Dialog is displayed. Click OK.
Oracle Data Integrator now starts an execution session.
To view the execution results of your integration interface:
In the Session List accordion in Operator Navigator, expand the All Executions node.
Refresh the displayed information clicking Refresh in the Operator Navigator toolbar menu.
The log for the execution session of the Pop. TRG_SALES interface appears as shown in Figure 8-8.
This section describes how to determine the invalid records. These are the records that do not satisfy the constraints and has been rejected by the flow control.
This section includes the following topics:
To determine the number of processed records:
In Operator Navigator, open the Session Step Editor for the Pop. TRG_SALES step.
If required, click Refresh in the Operator Navigator menu toolbar.
On the Definition tab of the Session Step Editor, you can see in the Record Statistics section that the loading of the TRG_SALES table produced 5 inserts and isolated 32 errors in an error table.
Figure 8-9 shows the Record Statistics section of the Session Step Editor:
These 5 inserts are the 5 rows that have been inserted because of the changes you have performed in Section 8.1.3.4, "Correcting Invalid Data". Changing the CITY_ID of the customer with the CUST_ID = 203 to a CITY_ID that is listed in SRC_CITY table, adds the sales performed by the customer 203 to the TRG_SALES table. These 5 sales operations are highlighted in Figure 8-12.
Note that the customer with the CUST_ID = 203 actually performed 7 sales operations. You can identify these 7 operations as follows:
In the SRC_CUSTOMER table, determine the ORDER_IDs of the customer with the CUST_ID = 203. Figure 8-10 shows that this customer has two ORDER_IDs: 10 and 42.
As shown in Figure 8-11, the SRC_ORDER_LINES table lists 7 order lines for the ORDER_IDs 10 and 42.
The highlighted order lines have the same PRODCT_ID and are merged into one line (line 23) in the TRG_SALES table shown in Figure 8-12.
Note that the Pop. TRG_SALES interface has already been executed in the Load Sales Administration package. This is why the TRG_SALES table now contains 62 rows and not only 5 inserts as shown in Figure 8-9.
To view the data resulting of your interface execution:
In Designer Navigator, expand the Models accordion and the Sales Administration - HSQL model.
Select the TRG_SALES datastore.
Right-click and select View Data to view the data in the target table.
Note that you can also select Data... to view and edit the data of the target table.
The View Data Editor is displayed as shown in Figure 8-12.
You can access the invalid records by right-clicking on the datastore in your model and selecting Control > Errors...
To review the error table of the TRG_SALES datastore:
In Designer Navigator, expand the Sales Administration - HSQL model.
Select the TRG_SALES datastore.
Right-click and select Control > Errors...
The Error Table Editor is displayed as shown in Figure 8-13.
The interface that you have executed has identified and isolated 32 invalid records in an error table that was automatically created for you.
In this error table, you can see that the interface rejected:
31 records in violation of the FK_SALES_CUST constraint (for example, have a customer number that does not exist in the table of customers)
1 record in violation of the FK_SALES_PROD constraint (has a product number that does not exist in the table of products)
The invalid records were saved into an error table and not integrated into the target table.