Creating ODI Interface: Complex Transformations

In the previous practices, you learned how to create a simple ODI Interface.In this practice, you create a more complex interface with several sources to load the TRG_CUSTOMER datastore in the Oracle Sales Application model with the content of
SRC_CUSTOMER table and the SRC_SALES_PERSON files from different models. You apply filtering to retrieve only customers with CUST_ID < 105. In addition, you populate the update date (UPD_DATE) column with the System date in the mapping implementation field.
Note: Completing this practice is critical for all the following practice sessions.


Your Tasks:
1. Create an interface called INT_9-1, loading the TRG_CUSTOMER datastore in the Oracle Sales Application model.
a. In ODI Designer, click the Projects tab, and then the Interfaces node. Right-click and select New Interface.

b. In the “Interface: NEW” window, enter INT_9-1, set Optimization Context to Development, and then click the Mapping tab.


c. In the Designer Navigator, click the Models tab, and then in the tree view expand the Oracle Sales Application model. Drag the TRG_CUSTOMER datastore from the tree view to the Target Datastore zone. The datastore appears in this zone.




d. In the Design Navigator Models tab, expand the FLAT_FILE_SRC model. Drag the
SRC_SALES_PERSON datastore to the Sources zone of your diagram.


e. In the Design Navigator Models tab, expand the HSQL Orders Application model and drag the SRC_CUSTOMER datastore to the Sources zone of your diagram. Click Yes to perform Automatic mapping. If necessary, rearrange the datastores. Your diagram
should resemble the following:


f. Drag the SALES_PERS_ID column from the SRC_CUSTOMER source datastore onto the ID column of the SRC_SALES_PERSON datastore. A join appears between these two sources.


g. Select and drag the CUSTID column from the SRC_CUSTOMER source datastore to the CUST_ID column in the TRG_CUSTOMER target datastore. Select the CUST_ID column in the Target Datastore zone. The Mapping Properties panel shows the mapping.

Note: Ensure that the Active Mapping check box is selected for the CUST_ID column of the target datastore.
h. Select the CUST_NAME column in the Target Datastore zone. The Mapping Properties panel changes to show an empty mapping.
Note: You may find it useful to select the Freeze View button before you perform the next step.
i. Drag the FIRST_NAME and LAST_NAME columns from the SRC_CUSTOMER source into the Mapping: CUST_NAME Implementation Tab field, and then edit the mapping to have the following mapping expression:
INITCAP(SRC_CUSTOMER.FIRST_NAME) || ' ' ||
INITCAP(SRC_CUSTOMER.LAST_NAME)
Note: Ensure that the Active Mapping check box is selected for the CUST_NAME column of the target datastore. 


j. Scroll down and select the Staging Area button on the Execute on panel.
Note: If the Thumbnail window is open, close it to extend the Mapping panel


k. Drag the LAST_NAME column from the SRC_SALES_PERSON source datastore to the SALES_PERS column in the target datastore. Click the SALES_PERS column in the target datastore, and then edit the mapping to have the following mapping expression:
UPPER(TES.LASTNAME). Click the Staging Area option from the Execute on panel.

l. Select the UPD_DATE column in the Target Datastore zone and enter the SYSDATE in the Mapping implementation field. Click the UPD_DATE column again, and then select the Staging Area option from the Execute on panel.
Note: Ensure that the Active Mapping check box is selected for the UPD_DATE column of the target datastore.
m. Repeat step l for the CRE_DATE column. Because the value of the CRE_DATE column should not be changed later, deselect the Update check box. Ensure that the Staging Area option is selected from the Execute on panel.
 

n. In the diagram, drag the CUSTID column from the SRC_CUSTOMER source to the workbench (the gray background). A filter appears with the CUST_ID column name.