Using Temporary ODI Interfaces 1

In this practice, you perform the following steps:
1. Create a temporary interface, INT_12-1.

•  Use datastores SRC_ORDERS and SRC_ORDER_LINES from the HSQL_SRC model,joined on ORDER_ID.
•  Use SRC_CUSTOMER as a lookup table.
•  Create a temporary target table, TEMP_AGG_ORDERS.
•  Aggregate some of its columns by using the MIN, MAX, and AVG functions.

2. Create interface INT_12-1-2

•  Use the temporary interface INT_12-1 as a source.
•  Use the datastore TRG_SALES as the target.

3. Execute INT_12-1-2 and examine the rows inserted into TRG_SALES.

Your Tasks:
1. Develop an ODI Temporary interface.

a. If not connected, connect to the DEV_ODI_REPO work repository (User:SUPERVISOR, Password: SUNOPSIS). Click the Designer tab.

b. In the Projects tab, expand: HandsOnLoads > HandsOn. Right-click Interfaces, and then select New Interface. Name the new interface INT_12-1. In the Optimization Context field, select Development. For Staging Area, select Hypersonic SQL:
HSQL_DEMO_SRC. Click the Mapping tab.

c. Open the Models tab. Expand the HSQL_SRC model, and drag SRC_ORDERS and SRC_ORDER_LINES datastores from the model to the Source area.

d. Drag the ORDER_ID column from the datastore SRC_ORDERS to the datastore SRC_ORDER_LINES to create a Join.

e. Click the Lookup icon to start the Lookup Wizard . Ensure that your Driving table is SRC_ORDERS. Select the Lookup table, SRC_CUSTOMER, in the HSQL_SRC model. For the Lookup table, edit the Alias to read: LKUP_SRC_CUSTOMER as
shown in the following screen. Click Next.

f. Select the CUST_ID column in the Source table and the CUSTID column in the Lookup table, as shown in the screen, and then click Join. Click Finish.

g. Now you need to create a temporary target datastore. Drag the column CUST_ID from the SRC_ORDERS table to the Target area. Click <Temporary Target DataStore>. In the Temporary Target Properties, enter Name: TEMP_AGG_ORDERS, as shown in the following screen.

h. Drag the column ORDER_ID from SRC_ORDERS to the Target area. Edit the mapping to read: MIN(SRC_ORDERS.ORDER_ID). Scroll down to the Target Column Properties section and rename this column to FIRST_ORDER_ID. Click the interface
tab and view the result.

i. Again, drag the column Order_ID from SRC_ORDERS to the Target area. Edit the mapping to read MAX(SRC_ORDERS.ORDER_ID). Scroll down and rename this column LAST_ORDER_ID.

j. Drag the column ORDER_DATE from SRC_ORDERS to the Target area. Edit the mapping to read MIN(SRC_ORDERS. ORDER_DATE). Scroll down and rename this column FIRST_ORDER_DATE.

k. Similarly, drag the column ORDER_DATE again from SRC_ORDERS to the Target area. Edit the mapping to read MAX(SRC_ORDERS. ORDER_DATE). Scroll down and
rename this column LAST_ORDER_DATE.