Using Temporary Indexes

In the previous practice, TRG_PROD_FAMILY was a target table. In this practice,TRG_PROD_FAMILY serves as one of the source tables.First you create a new ODI interface, INT-11-2 to load data into the TRG_PRODUCT targetdatastore table in the Oracle Sales Application model. You specify the source tables as SRC_PRODUCT from the HSQL_SRC model, and TRG_PROD_FAMILY from the Oracle Sales Application model.
TRG_PROD_FAMILY will be used as a lookup table to obtain the ID number generated by the previous lesson’s interface that used a sequence to populate the ID number.
For the lookup, you create a temporary index to join SRC_PRODUCT and TRG_PROD_FAMILY on their FAMILY_NAME column.
Finally, you execute the interface, view the source code, and examine the inserted rows in the TRG_PRODUCT target datastore.

 
 Note: Completing this practice is critical for all the following practice sessions.
Your Tasks:
1. Create a new ODI Interface with a temporary index on the source tables.
a. Expand the project HandsOnLoads > HandsOn > Interfaces. Right-click Interfaces and select New Interface. Name this interface INT_11-2. Click the Mapping tab.

 
 b. In the Models tab expand the Oracle Sales Application and HSQL_SRC models. Drag the TRG_PRODUCT datastore from the Oracle Sales Application model to the target area. Drag the TRG_PROD_FAMILY datas tore from the Oracle Sales Application to the Source mapping area. Click Yes to perform automatic mapping. Drag the SRC_PRODUCT datastore from the HSQL_SRC model to the source mapping area, as shown next. Click Yes to perform automatic mapping
 

 c. Create a join between the two source tables by dragging FAMILY_NAME in the TRG_PROD_FAMILY datastore to FAMILY_NAME in the SRC_PRODUCT datastore,as shown in the following screen.
 
 
 d. Click the Join and scroll down to view the Join Property inspector. In the Temporary Indexes section, set the index on SRC_PRODUCT(SRC_PRODUCT) to “Non-Unique.”
Click the Flow tab.

 
 
 e. Click the Flow tab. In the flow tab, click the target datastore. Verify that the DELETE_ALL parameter is set to “false.” Save your interface.
 
 2. Execute the Interface and verify the results.
a. In the Projects tab, execute your interface INT_11-2 as shown in the following screen. Click OK. Click OK again.

 
 
 
 b. Open ODI Operator, and verify that your interface executed successfully. Expand the session to view steps as shown in the following screen.
 
 c. Double-click the step Integration – INT_11-2 – Create Temp. Indexes on Work. In the Session Task, select the Code tab. View the source code.


d.Scroll down, and then double-click the step Integration – INT_11-2 – Insert new rows, as shown in the screen. View the number of inserted rows.
 
 
 e. Open the Designer tab. In the Interface INT_11-2 tab, select the Mapping tab. Right click the target datastore, and then select Data to view inserted rows. Close Data Editor window and the tabs.
 
 

No comments:

Post a Comment