D64974GC20_ag2_Oracle_Data_Integrator_Admin_2012.pdf
270
0
0
Full text
(2) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Richard Green. Disclaimer. Technical Contr ibutors and Reviewer s Alex Kotopoulis Denis Gray Christophe Dupupet Julien Testut David Allan Sachin Thatte Viktor Tchemodanov Gerry Jurrens Veerabhadra Putrevu. Editor s Rashmi Rajagopal Vijayalakshmi Narasimhan Aju Kumar Kumar. Gr aphic Designer s Satish Bettegowda Seema Bopaiah. Publisher s Giri Venugopal Sumesh Koshy Srividya Rameshkumar. This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract. Trademark Notice Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Author.
(3) Practices for Lesson 1: Introduction to Integration and Administration ................................................. 1-1 Practices for Lesson 1: Overview............................................................................................................. 1-2 Practice 1-1: Starting the ODI Client ........................................................................................................ 1-3 Practices for Lesson 2: Administering ODI Repositories ........................................................................ 2-1 Practices for Lesson 2: Overview............................................................................................................. 2-2 Practices for Lesson 2: Flow of Data........................................................................................................ 2-3 Practice 2-1: Creating and Connecting to ODI Master and Work Repositories ........................................... 2-4 Practices for Lesson 3: ODI Topology Concepts ..................................................................................... 3-1 Practices for Lesson 3: Overview............................................................................................................. 3-2 Practice 3-1: Setting Up and Installing an ODI Agent ................................................................................ 3-3 Practices for Lesson 4: Describing the Physical and Logical Architecture ............................................ 4-1 Practices for Lesson 4: Overview............................................................................................................. 4-2 Practice 4-1: Working with Topology ........................................................................................................ 4-3 Practices for Lesson 5: Setting Up a New ODI Project ............................................................................ 5-1 Practices for Lesson 5: Overview............................................................................................................. 5-2 Practice 5-1: Setting Up a New ODI Project ............................................................................................. 5-3 Practices for Lesson 6: Oracle Data Integrator Model Concepts............................................................. 6-1 Practices for Lesson 6: Overview............................................................................................................. 6-2 Practice 6-1: Creating a Model ................................................................................................................ 6-3 Practices for Lesson 7: Organizing ODI Models and Creating ODI Datastores ....................................... 7-1 Practices for Lesson 7: Overview............................................................................................................. 7-2 Practice 7-1: Checking Data Quality in the Model ..................................................................................... 7-3 Practices for Lesson 8: ODI Interface Concepts ...................................................................................... 8-1 Practices for Lesson 8: Overview............................................................................................................. 8-2 Practice 8-1: Creating ODI Interface: Simple Transformations .................................................................. 8-3 Practices for Lesson 9: Designing Interfaces .......................................................................................... 9-1 Practices for Lesson 9: Overview............................................................................................................. 9-2 Practice 9-1: Creating ODI Interface: Complex Transformations ............................................................... 9-3 Practice 9-2: Creating ODI Interface: Implementing Lookup ...................................................................... 9-22 Practices for Lesson 10: Interfaces: Monitoring and Debugging ............................................................ 10-1 Practices for Lesson 10: Overview ........................................................................................................... 10-2 Practice 10-1: Creating ODI Interface: Exporting a Flat File to a Relational Table ...................................... 10-3 Practices for Lesson 11: Designing Interfaces: Advanced Topics 1 ....................................................... 11-1 Practices for Lesson 11: Overview ........................................................................................................... 11-2 Practice 11-1: Using Native Sequences with ODI Interface ....................................................................... 11-3 Practice 11-2: Using Temporary Indexes ................................................................................................. 11-18 Practice 11-3: Using Data Sets with ODI Interface.................................................................................... 11-28 Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 ....................................................... 12-1 Practices for Lesson 12: Overview ........................................................................................................... 12-2 Practice 12-1: Using Temporary ODI Interfaces ....................................................................................... 12-3 Practice 12-2: Developing a New Knowledge Module ............................................................................... 12-38 Practices for Lesson 13: Using ODI Procedures...................................................................................... 13-1 Practices for Lesson 13: Overview ........................................................................................................... 13-2 Practice 13-1: Creating an ODI Procedure ............................................................................................... 13-3 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Oracle Data Integrator 11g: Integration and Administration Table of Contents i. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Table of Contents.
(4) Practices for Lesson 15: Managing ODI Scenarios.................................................................................. 15-1 Practices for Lesson 15: Overview ........................................................................................................... 15-2 Practice 15-1: Creating and Scheduling an ODI Scenario ......................................................................... 15-3 Practices for Lesson 16: Using Load Plans ............................................................................................. 16-1 Practices for Lesson 16: Overview ........................................................................................................... 16-2 Practice 16-1: Using Load Plans.............................................................................................................. 16-3 Practices for Lesson 17: Managing ODI Versions.................................................................................... 17-1 Practices for Lesson 17: Overview ........................................................................................................... 17-2 Practice 17-1: Working with ODI Versions................................................................................................ 17-3 Practices for Lesson 18: Enforcing Data Quality with ODI ...................................................................... 18-1 Practices for Lesson 18: Overview ........................................................................................................... 18-2 Practice 18-1: Enforcing Data Quality with ODI Interface .......................................................................... 18-3 Practices for Lesson 19: Working with Changed Data Capture ............................................................... 19-1 Practices for Lesson 19: Overview ........................................................................................................... 19-2 Practice 19-1: Implementing Changed Data Capture ................................................................................ 19-3 Practices for Lesson 20: Advanced ODI Administration.......................................................................... 20-1 Practices for Lesson 20: Overview ........................................................................................................... 20-2 Practice 20-1: Setting Up ODI Security .................................................................................................... 20-3 Practice 20-2: Integration with Enterprise Manager and Using ODI Console.............................................. 20-22 Practices for Lesson 21: Extending Oracle Data Integrator with SDK, Web Services, and SOA ............ 21-1 Practices for Lesson 21: Overview ........................................................................................................... 21-2 Practice 21-1: Executing an ODI Scenario Through the ODI Public Web Service....................................... 21-3. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Oracle Data Integrator 11g: Integration and Administration Table of Contents ii. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 14: Using ODI Packages......................................................................................... 14-1 Practices for Lesson 14: Overview ........................................................................................................... 14-2 Practice 14-1: Creating an ODI Package.................................................................................................. 14-3 Practice 14-2: Using ODI Package with Variables and User Functions ...................................................... 14-14.
(5) Chapter 12. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 1. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2.
(6) Practices Overview In Practice 12-1, you use a temporary ODI interface for data transformation. In Practice 12-2, you duplicate an existing knowledge module and modify the duplicate knowledge module to use with an interface.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 12: Overview. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 2.
(7) Use Case Scenario: John created interfaces to load data in the target models from different sources and performed data transformation and filtering, and implemented a lookup to fetch additional information for the data loading in the target. John also created an interface with multiple data sets to add in a bulk feed of customer data from another system. Now, John needs to create a temporary interface, which is used for loading data to a sales target datastore.. Background: In this practice, you perform the following steps: 1. Create a temporary interface, INT_12-1.. 2.. •. 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. Create interface INT_12-1-2 •. 3.. Use the temporary interface INT_12-1 as a source.. • Use the datastore TRG_SALES as the target. Execute INT_12-1-2 and examine the rows inserted into TRG_SALES.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 3. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practice 12-1: Using Temporary ODI Interfaces.
(8) Your Tasks: 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.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 4. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 1..
(9) 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.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 5.
(10) Drag the ORDER_ID column from the SRC_ORDERS datastore to the SRC_ORDER_LINES datastore to create a Join.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. d.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 6.
(11) 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.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. e.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 7.
(12) 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.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 8.
(13) 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:. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. g.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 9.
(14) Drag the column ORDER_ID from SRC_ORDERS to the Target area. Then click this SRC_ORDERS column in the target data store. In the Mapping Properties panel at the bottom of the window, edit the Implementation tab to read: MIN(SRC_ORDERS.ORDER_ID). Scroll down to the Target Column Properties section and rename this column to FIRST_ORDER_ID. Scroll back up the Property Inspector and examine your results thus far.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. h.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 10.
(15) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 11. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(16) Again, drag the column Order_ID from SRC_ORDERS to the Target area. In the Mapping Properties panel at the bottom of the window, edit the Implementation tab to read: MAX(SRC_ORDERS.ORDER_ID). Scroll down and rename this column LAST_ORDER_ID.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. i.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 12.
(17) Drag the column ORDER_DATE from SRC_ORDERS to the Target area. In the Mapping Properties panel at the bottom of the window, edit the Implementation tab to read: MIN(SRC_ORDERS.ORDER_DATE). Scroll down and rename this column FIRST_ORDER_DATE.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. j.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 13.
(18) Similarly, drag the column ORDER_DATE again from SRC_ORDERS to the Target area. Edit the mapping properties to read MAX(SRC_ORDERS. ORDER_DATE). Scroll down and rename this column LAST_ORDER_DATE.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. k.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 14.
(19) In the Source area, expand the lookup datastore by clicking the plus sign in the upper left corner of the lookup datastore, and then stretching the datastore longer until you see all of its columns. Drag the FIRST_NAME column from the lookup table to the Target area. In the Target area, click the FIRST_NAME column. Start the Expression editor , and then drag the LAST_NAME column from the left panel of the Expression Editor to the “expression” area.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. l.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 15.
(20) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 16. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(21) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 17. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(22) LKUP_SRC_CUSTOMER.LAST_NAME, as shown next. Click OK. Click the button to validate the expression. An ODI Information message indicates that the SQL expression is valid. Click OK.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. m. Edit the expression to read LKUP_SRC_CUSTOMER.FIRST_NAME || ‘ ‘ ||. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 18.
(23) Scroll down, and in the Target Column Properties, rename this column CUST_NAME.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. n.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 19.
(24) Drag the column QTY from the SRC_ORDER_LINES source datastore to the Target area. Edit the mapping expression to read SUM(SRC_ORDER_LINES.QTY).. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. o.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 20.
(25) Drag the column AMOUNT from the SRC_ORDER_LINES source datastore to the Target area. Edit the mapping expression to read AVG(SRC_ORDER_LINES.AMOUNT). In the Target Column Properties, rename this column AVG_AMOUNT.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. p.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 21.
(26) Drag the column AMOUNT again from the SRC_ORDER_LINES source datastore to the Target area. Edit the mapping expression to read SUM(SRC_ORDER_LINES.AMOUNT). Rename this column SUM_AMOUNT.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. q.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 22.
(27) Drag the column PRODUCT_ID from the SRC_ORDER_LINES source datastore to the Target area.. s.. In the Target datastore, click the SUM_AMOUNT column. In the Execute on section, select Staging Area. Repeat this step for the six other target columns in which you defined a function in the mapping expression. Refer to the following table to verify the execution location. Your Target datastore should now look as shown in the screen. Click the Flow tab.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. r.. Column. Execution Location. SUM_AMOUNT. Staging Area. AVG_AMOUNT. Staging Area. CUST_ID. Source. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 23.
(28) Source. FIRST_ORDER_DATE. Staging Area. FIRST_ORDER_ID. Staging Area. LAST_ORDER_DATE. Staging Area. LAST_ORDER_ID. Staging Area. PRODUCT_ID. Source. QTY. Staging Area. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. CUST_NAME. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 24.
(29) Verify the flow, and then save your temporary interface. Verify that your temporary interface appeared in the tree view. Close your interface tab. Note the yellow color of your newly created temporary interface in the Designer Navigator’s Projects tree view.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 25. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. t..
(30) Create a new interface, which uses the newly created temporary interface as a source. a. In the HandsOnLoads project, create a new ODI interface, INT_12-1-2, as shown next. In the Optimization Context field, select Development. Open the Mapping tab.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 26. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 2..
(31) Drag the temporary interface, INT_12-1, from the Projects tab to the Source area.. c.. From the Models tab, drag the datastore TRG_SALES from the Oracle Sales Application model to the Target Datastore area. Click Yes to perform automatic mapping. Note that only three of the nine columns were automatically mapped.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 27. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. b..
(32) In the Target Datastore, select the FIRST_ORD_DATE column. Drag the FIRST_ORDER_DATE column from the temporary interface in the source area to the Implementation tab in the Mapping Properties area.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 28. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. d..
(33) Repeat the previous two-step process to map the remaining target columns to their corresponding columns in the temporary interface in the Source area. Use the following table and results screen as your guide: 1. First click this column in the Target Datastore TRG_SALES:. 2. Then drag this column from the Source area down to the Implementation tab:. FIRST_ORD_ID. FIRST_ORDER_ID. LAST_ORD_ID. LAST_ORDER_ID. LAST_ORD_DATE. LAST_ORDER_DATE. AMOUNT. SUM_AMOUNT. PROD_AVG_PRICE. AVG_AMOUNT. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 29. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. e..
(34) In the Source panel, click INT_12-1 (TEMP_AGG_ORDERS), scroll down to the Source Properties section, and then select the Use Temporary Interface as Derived Table (Sub-Select) check box.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 30. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f..
(35) To view data derived from the temporary interface, right-click the temporary interface and select Data. Close the Data Editor window.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 31. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. g..
(36) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 32. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(37) Select the Flow tab, click Target datastore, and verify the flow of your interface. In the Target Properties, set the DELETE_ALL option to “true”. Save the interface, and then close the interface tab.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. h.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 33.
(38) Execute the interface INT_12-1-2 and verify the execution results. a. Execute the interface INT_12-1-2.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 3.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 34.
(39) Open ODI Operator Navigator and verify that the interface INT_12-1-2 executed successfully. Scroll down and open the task Integration - INT_12-1-2 - Insert new rows. Verify the number of inserts.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. b.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 35.
(40) In the Models tab in the Oracle Sales Application model, right-click the TRG_SALES datastore, and select View Data. View the rows inserted in the target datastore.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 36. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c..
(41) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 37. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(42) Use Case Scenario: To develop the integration project, John created the ODI project and the interface to export data from a flat file and load this data into a relational table. Now, he decides to modify the existing KM to create an audit table each time the interface runs.. Background: After the interface is created and tested, you may need to create a new knowledge module to use with the interface. In this practice, you duplicate an existing knowledge module, IKM SQL Incremental Update, naming the new knowledge module IKM Oracle UI - Audit. You add two commands to the knowledge module, “Create Audit Table” and “Insert Audit Records,” by using command syntax provided in text files. Next, you duplicate an existing interface, INT-Exp-FF-RT, naming the new interface INT-EXPFF-RT-AUDIT. You change the new interface’s IKM selection to use the new knowledge module you just created, IKM Oracle UI - Audit. Finally, you execute interface INT-EXP-FF-RT and examine the audit records inserted into the audit table created by your knowledge module.. 2. 1. Your Tasks: 1.. Create the new knowledge module with new functionality to create an audit table and insert audit records. a. If not connected, connect to the DEV_ODI_REPO Work Repository (User: SUPERVISOR, Password: SUNOPSIS). Click the Designer tab.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 38. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practice 12-2: Developing a New Knowledge Module.
(43) Open ODI Designer and click the Projects tab. Select Projects > Export-FF-RT > Knowledge Modules > Integration (IKM). Right-click IKM SQL Incremental Update and select Duplicate Selection. In the window that follows, click Yes.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. b.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 39.
(44) Double-click Copy of IKM SQL Incremental Update to edit it. Rename this KM IKM Oracle UI – Audit. Click the Details tab. Note: If the message for Object Locking/Unlocking is displayed, click OK.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 40.
(45) On the Details screen, review the commands in the current KM. Select Insert new. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. rows and then click the Add. e.. icon to add the new command.. Name this command Create Audit Table. Select the Ignore Errors check box. In the Command on Target tab, set the Technology to Oracle. Enter the following command to create the audit table. Verify that the check boxes in the Journalizing section are not selected. If necessary, deselect them.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 41. Oracle University and (Oracle Corporation) use only.. d..
(46) create table <%=odiRef.getTable("L", "TARG_NAME", "A")%>_H ( <%=odiRef.getColList("", "[COL_NAME]\t[DEST_CRE_DT]NULL", ",\n\t", "", "PK")%>, AUDIT_DATE DATE, AUDIT_INDICATOR VARCHAR2(1) ). Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Note: You can find this command in the file 12-2.txt, provided in the following location: c:\Labs\Text.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 42.
(47) Click the Details tab. Scroll down and select the Create Audit Table command. Click the icon several times to move the command up and place it right after the Insert new rows step, as shown below. Verify that the Ignore Errors check box is selected for your new Create Audit Table command. With the Create Audit Table command still selected, click the Add icon again .. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f.. Similarly, create the command that inserts audit records in the audit table, as shown in the screen. Name this command Insert Audit Records. Select the Ignore Errors check box. In the Command on Target tab, set the Technology to Oracle. Enter the command provided below. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 43.
(48) Insert into <%=odiRef.getTable("L", "TARG_NAME", "A")%>_H ( <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "PK")%>, AUDIT_DATE, AUDIT_INDICATOR ) select <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "PK")%>, sysdate, IND_UPDATE from <%=odiRef.getTable("L","INT_NAME","W")%> Note: In these substitution methods, you use the following parameters: GetTable: • “L”: Local naming convention. For example, in Oracle that would be schema.table (versus “R” for remote: schema.table@server). •. “A”: Automatic. It enables ODI to determine which physical schema to use (the Data schema [“D”] or the Staging schema [“W”]).. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 44. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Note: You can find this command in the file 12-2.txt, provided in the following location: c:\Labs\Text..
(49) Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. getColList: • Notice the “PK” parameter. If it is used, only the columns that are part of the primary key are included. `. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 45.
(50) Click the Details tab. Scroll down and select the Insert Audit Records command. Click the icon several times to move the command up and place it right after the Create Audit Table step, as shown in the next screen. Click Save, and then close the tab.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. g.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 46.
(51) 2.. After clicking Save, verify that your new knowledge module, IKM Oracle UI – Audit, appears in the Knowledge Modules tree.. You will create a modified copy of the interface INT-EXP-FF-RT, to execute with your newly created knowledge module. You will need to duplicate the existing interface, rename it, and then change the IKM entry to use your new IKM Oracle UI-Audit. a. On the Projects tab, right-click the interface INT-EXP-FF-RT and select Duplicate Selection. Click Yes on the Confirmation screen.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 47. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. h..
(52) Double-click the interface Copy of INT-EXP-FF-RT. Change the name of the interface to INT-EXP-FF-RT-Audit, as shown in the screenshot. Click the Flow tab.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 48. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. b..
(53) On the Flow tab, click Target (ODI_STAGE) in the diagram to open the Target Area – Property Inspector below the diagram. In the IKM Selector drop box, select IKM Oracle UI - Audit. Ensure that in the Options list, FLOW_CONTROL and STATIC_CONTROL options are set to “false”, as shown in the screenshot. Click the Save button, and then close the tab.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 49. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c..
(54) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 50. .. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. To execute your new interface, select the interface and click the Execute button Click OK. Click OK again in the Information window to start the execution..
(55) Click the Operator tab to open ODI Operator. Click to open the All Executions node. If necessary, click the Refresh icon session steps.. , find your new interface session, and view the. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 51. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. d..
(56) Scroll down and double-click the Create Audit Table step. View the content of the Definition and Code tabs for this step. Close the tab.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. e.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 52.
(57) Similarly, view the Insert Audit Records step information. Refer to the following screens. Click Close.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 53.
(58) Open Oracle SQL Developer and expand the ODI_STAGE connection (for the password, enter ODI_STAGE). Select the Tables (Filtered) node and if necessary click the Refresh button . Click the Audit table TRG_SALES_PERSON_H, click the Data tab, and verify that your Audit table was created and populated with audit records, as shown next.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 54. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. g..
(59) In addition, you create an option for your new knowledge module to make your KM more user friendly. This option will enable an end user to choose when to generate audits. a. In Designer, click the Projects tab, right-click IKM Oracle UI – Audit, and select New Option. Name this option AUDIT_CHANGES, set Type to Check Box, and Default Value to “true”. Click Save and close the AUDIT_CHANGES option tab.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 3.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 55.
(60) Now, you need to link this option to your steps in the KM. Double-click IKM Oracle UI Audit, and click the Details tab. Double-click your new step, Create Audit Table, to edit it. Scroll to the bottom and click the Options plus sign [+] to show its contents.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. b.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 56.
(61) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 57. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(62) Deselect the Always Execute check box. Select AUDIT_CHANGES. This will condition the execution of these steps with the value set by the end user. Click Save. Click the Details tab.. d.. Repeat the previous step for the Insert Audit Records command, as shown in the following screens. Click Save to save your KM and close the tab.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 58. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c..
(63) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 59. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(64) Double-click INT-Exp-FF-RT-Audit to edit it. Click the Flow tab, click Target (ODI_STAGE), and verify that the option AUDIT_CHANGES is set to “true”. Execute the interface. .. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. e.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 60.
(65) If not opened, open ODI Operator, select All Executions, and then click the Refresh button and view the execution results. Both Create Audit Table and Insert Audit Records steps should be present.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 61.
(66) In Designer, edit the INT-Exp-FF-RT-Audit interface again. In the Flow tab, click Target (ODI_STAGE) and set the option AUDIT_CHANGES to “false”. Save your interface and then execute your interface again. .. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. g.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 62.
(67) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 63. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(68) In ODI Operator, click the Refresh button and view the execution results. The new KM steps to create and populate an audit table are not executed.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. h.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 12: Designing Interfaces: Advanced Topics 2 Chapter 12 - Page 64.
(69) Chapter 13. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 1. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 13: Using ODI Procedures.
(70) Practices Overview In this practice, you define an ODI procedure to create a relational table and populate it with data.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 13: Overview. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 2.
(71) Use Case Scenario: John created the new ODI project and an interface to perform the transformations described in the previous practices. Now, he needs to modify his project by adding an ODI procedure to create a relational table and populate it with data.. Background: A common task that is performed by using ODI is creating and populating a relational table. This practice walks you through the steps that are needed to create a project and a procedure that will create and populate a relational table with data. You also execute the procedure and verify the execution within ODI Operator. In this practice, you use the same RDBMS schema, ODI dataserver, and ODI physical schema– all named ODI_STAGE–which you created in Practice 10-1. You create the project Procedure-CRT-TBL, and you create the procedure PRD-createpopulate-table. Next, you add commands to drop, create, and populate a table, by using the syntax provided in text files. Finally, you execute the procedure and verify that the table was created and populated with records. 1. Create the project Procedure-CRT-TBL. 2. Create the procedure PRD-create-populate-table. 3. Add commands to drop, create, and populate a table.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 3. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practice 13-1: Creating an ODI Procedure.
(72) Your Tasks: Create the new project in ODI Designer. a. Switch to the Designer Navigator. Click the Designer tab. Select the Projects tab, click the New Project. b.. icon, and select New Project.. Enter the name of the project: Procedure-CRT-TBL. Click the Save button to create the project and close the tab. The project appears in the tree view. Expand the Procedure-CRT-TBL project node.. Note: In this practice, you use the same RDBMS schema, ODI data server, and physical schema named ODI_STAGE that you created in Practice10-1. If you have not performed Practice 10-1, complete Practice 10-1, steps 5 and 7 to create the RDBMS schema, the ODI source data server, and the physical schema for your new RDBMS model.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 4. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 1..
(73) Open the ODI Topology Navigator, select Physical Architecture. Expand Technologies > Oracle > ODI _STAGE. Verify that you have the physical schema ODI_STAGE created under ODI_STAGE data server.. d.. Open ODI Designer. In the Projects tab, select Procedure-CRT-TBL > First Folder. Right-click Procedures and select New Procedure.. e.. Enter the procedure name as PRD-create-populate-table. Set Target Technology to Oracle. Click the Details tab. Click the icon procedure.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 5. to add a step in the. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c..
(74) In the command window, enter the parameters of the procedure and the command on the target provided in the following table. You need this command to drop the table in case a table with this name exists. Select the Ignore Errors check box. Parameter. Value. Name. Drop Table. Technology. Oracle. Schema. ODI_STAGE. Ignore Errors. Selected. DROP table ODI_STAGE.SRC_SALES_PERSON. g.. Click the Details tab, and then click the icon to add another step in the procedure. In the command screen, if not selected, click the Command on Target tab. Enter the parameters of the procedure and the command provided below.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 6. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f..
(75) Parameter. Value. Name. Create Table. Technology. Oracle. Schema. ODI_STAGE. Ignore Errors. Unchecked. CREATE table "SRC_SALES_PERSON" ( "SALES_PERSON_ID" NUMBER(8,0) NOT NULL, "FIRST_NAME" VARCHAR2(80), "LAST_NAME" VARCHAR2(80), "DATE_HIRED" VARCHAR2(80), "DATE_UPDATED" DATE NOT NULL, constraint "SRC_SALES_PERSON_PK" primary key("SALES_PERSON_ID") ). Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 7. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Note: You can copy this command from the 13-1.txt file, which is provided in your Lab folder in the following location: c:\Labs\Text..
(76) Click the Details tab. Click the icon SRC_SALES_PERSON table.. to enter the step to populate the. In the command screen, click the Command on Target tab. Enter the parameters and the command shown below. Click the Details tab to view your steps. Note: You can copy this command from the file 13-1.txt, which is provided at the following location: c:\Labs\ Text. i.. Parameter. Value. Name. Populate Table. Technology. Oracle. Schema. ODI_STAGE. Ignore Errors. Deselected. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 8. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. h..
(77) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 9. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. begin insert into ODI_STAGE.SRC_SALES_PERSON values (11,'Andrew','Andersen','22/02/1999',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (12,'John','Galagers','20/04/2000',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (13,'Jeffrey','Jeferson','32422',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (20,'Jennie','Daumesnil','28/02/1988',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (21,'Steve','Barrot','24/09/1992',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (22,'Mary','Carlin','14/03/1995',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (30,'Paul','Moore','36467',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (31,'Paul','Edwood','18/03/2003',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (32,'Megan','Keegan','29/05/2001',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (40,'Rodolph','Bauman','29/05/2000',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (41,'Stanley','Fischer','37233',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (42,'Brian','Schmidt','25/08/1992',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (50,'Anish','Ishimoto','30/01/1992',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (51,'Cynthia','Nagata','28/02/1994',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values (52,'William','Kudo','28/03/1993',sysdate); end;.
(78) 2.. Select the Details tab and note the three steps. Click Save and close the tab.. Execute your newly created procedure and verify the results. a. Expand Procedure-CRT-TBL > Procedures, select the newly created procedure PRD-create-populate-table, and then click the Execution button to run the procedure . Click OK. Click OK again.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 10. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. j..
(79) Open ODI Operator. In ODI Operator, click the All Executions node, click the Refresh icon, find your session, and verify that your procedure executed successfully.. c.. Open Oracle SQL Developer and expand the ODI_STAGE connection. Select the Tables node and click the Refresh button . Double-click the newly created table SRC_SALES_PERSON, click the Data tab, and verify that your table SRC_SALES_PERSON was successfully created and populated with records as shown in the following screenshot.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 11. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. b..
(80) Additionally, you create an option to delete rows from the target audit table, which you created in Practice 12-2. To delete rows, you add a command that uses the value of the option in the code. If the value of this option is set to “Y”, the command to delete records will be executed. a. Right-click your procedure and select New Option. Name this option DELETE_AUDIT_RECORDS, and set Type to Value. Add the description as shown on the screen. Set the default value of this option to Y (no quotes). Click the Save icon.. 4.. Open your procedure, PRD-create-populate-table, and then click the Details tab. Click the Add icon to add a new command.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 12. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 3..
(81) 6.. Add the new command on Target to delete records in the audit table TRG_SALES_PERSON_H. Set the parameters as shown in the following table. Note: You can copy this command from the file 13-1.txt, which is provided at the following location: c:\Labs\Text. Parameter. Value. Name. DELETE AUDIT RECORDS. Technology. Oracle. Schema. ODI_STAGE. Ignore Errors. Checked. Click the Options tab and verify that the value of your newly created option is set to “Y”. Click the Save icon. Note: To view the inputs in the Options tab, you must click the Refresh button.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 13. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 5..
(82) Execute your procedure. In Operator, verify that the procedure executed successfully.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 7.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 14.
(83) Open SQL Developer and expand ODI_STAGE > Tables (Filtered). Open the Audit table TRG_SALES_PERSON_H, click the Data tab, and verify that the rows were deleted from the Audit table.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 8.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures Chapter 13 - Page 15.
(84) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 13: Using ODI Procedures. Chapter 13 - Page 16. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(85) Chapter 14. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 1. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 14: Using ODI Packages.
(86) Practices Overview In Practice 14-1, you create an ODI procedure and package to execute the sequence of transformations for the Interfaces created in previous practices. In Practice 14-2, you modify a package to use it with a variable and a user function that you define.. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practices for Lesson 14: Overview. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 2.
(87) Use Case Scenario: While working on his data integration projects, John created the ODI project and a number of interfaces for ELT processing. Now, he decides to build a procedure for handling repetitive operations and a package to use this procedure and the interfaces.. Background: A common task that is performed by using ODI is creating a package that executes a number of objects (Interfaces, Procedures) in the flow. In this practice, you create an ODI procedure and package to execute the sequence of transformations with the interfaces created in the previous practices. In this practice, you create a procedure, DELETE_TARGET, for deleting records from the TRG_SALES, TRG_CUSTOMER, TRG_CITY, TRG_REGION, and TRG_COUNTRY tables. Then, you create and execute a package, PKG_14-1. This package runs your new procedure, and then runs four interfaces you defined earlier in Lessons 8 and 9, which will use these same tables as targets: • The DELETE_TARGET procedure • Practice 8-1 interface • Practice 8-2 interface • Practice 8-3 interface • Practice 9-1 interface Finally, you run this package and review its execution in ODI Operator.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 3. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. Practice 14-1: Creating an ODI Package.
(88) Your Tasks: Create a DELETE_TARGET procedure that deletes the data in the TRG_SALES, TRG_CUSTOMER, TRG_CITY, TRG_REGION, and TRG_COUNTRY tables in the ORACLE_LOCAL_SALES logical schema. a. If not open, log in to DEV_ODI_REPO and enter SUPERVISOR for the username and SUNOPSIS for the password.. b.. In the Designer tab, click the Projects tab, and then select the Procedures node from the HandsOnLoads > HandsOn folder. Right-click the Procedures node and select the New Procedure option.. c.. Enter DELETE_TARGET in the Name field, and select Oracle as the Target Technology. Click the Details tab, and then click the Add icon. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 4. .. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 1..
(89) In the Name Field, enter DELETE TRG_SALES. In the Schema field, select ORACLE_ORCL_LOCAL_SALES. In the Context field, select Development. In the Command field, enter the following code: DELETE FROM <%=odiRef.getObjectName("L", "TRG_SALES", "ORACLE_ORCL_LOCAL_SALES", "", "D") %> This method returns the table name with run time-dependent information, such as the Oracle schema name that may vary depending on the context and topology. Select the Ignore Errors check box. Select the Details tab.. e.. Click the Add icon. In the Name Field, enter DELETE TRG_CUSTOMER. In the Schema field, select ORACLE_ORCL_LOCAL_SALES. In the Context field, select Development. In the Command field, enter the following code: DELETE FROM <%=odiRef.getObjectName("L", "TRG_CUSTOMER", "ORACLE_ORCL_LOCAL_SALES", "", "D") %> This method returns the table name with run time-dependent information, such as the Oracle schema name that may vary depending on the context and topology. Select the Ignore Errors check box. Select the Details tab. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 5. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. d..
(90) Repeat the two previous steps to create the commands DELETE TRG_CITY, DELETE TRG_REGION, and DELETE TRG_COUNTRY, which delete the tables TRG_CITY, TRG_REGION, and TRG_COUNTRY, respectively, as shown in the following screen. Note: To enter the commands, you may refer to the file 14-1.txt provided at the following location: c:\Labs\Text.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 6. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. f..
(91) Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages. Chapter 14 - Page 7. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED..
(92) 2.. Open the Details tab to view the commands. Click the Save button, and then close the procedure tab.. Create a package, PKG_14-1, that runs: • The DELETE_TARGET procedure • • •. The INT_8-1 interface The INT_8-2 interface The INT_8-3 interface. • The INT_9-1 interface Note: These interfaces were created in Practice 8-1 and Practice 9-1. a. In the Projects tab of the Designer Navigator, expand the HandsOnLoads > HandsOn folder. Right-click the Packages node and select the New Package option.. b.. Enter PKG_14-1 in the Name field, and then click the Diagram tab. In the Designer navigator tree view, expand the Interfaces node and Procedures node. Select the DELETE_TARGET procedure from the tree view, and then drag it to the diagram workbench. A DELETE_TARGET step appears in the package.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 8. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. g..
(93) Repeat the operation for the INT_8-1, INT_8-2, INT_8-3, and INT_9-1 interfaces. The package should appear as follows.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 9. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. c..
(94) Rename your steps with the names of the tables loaded by the step. In the Diagram window, click the step for the DELETE_TARGET procedure. Edit the Step name: DELETE TARGET TABLES, as shown in the following screenshot. In the diagram, click the empty space. The step is now renamed Delete Target Tables.. e.. Similarly edit the names of the other steps in the diagram, as follows: Rename INT_8-1 as Countries, INT_8-2 as Regions, INT_8-3 as Cities, and INT_9-1 as Customers.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 10. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. d..
(95) Click the Step After Success tool icon on the Package toolbar. Click the Delete Target Tables step. Press and hold the left mouse button and move the cursor over the Countries step. Then release the mouse button. A green arrow appears between these steps.. g.. Click the Countries step. Press and hold the left mouse button and move the cursor over the Regions step. Then, release the mouse button. A green arrow appears between these steps. Repeat this operation to link the steps in the correct order: Countries > Regions > Cities > Customers, as follows.. h.. Click the Save button to save this package.. Run this package and review its execution in ODI Operator. a.. Click the Execute package button to run this package. Click OK in the Execution window that appears, and then click OK again.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 14: Using ODI Packages Chapter 14 - Page 11. Oracle University and (Oracle Corporation) use only.. These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.. 3.. f..
Related documents
Skills: Battle 3, Craft: Armorsmithing 4, Craft: Weaponsmithing 3, Defense 4, Engineering (Siege) 5, Etiquette 2, Iaijutsu 3, Investigation 2, Kenjutsu 3, Know the School:
Measurement of sub-cellular iron content of both the wild-type and NMA61 mutant showed that the ferrous ion-uptake activity of NMA61 was lower than the wild-type when ferrous sulfate
Second, the purpose of this study was to determine by gender the effects of classes whose teachers utilized the Content Enhancement Routines (CERs) versus classes whose teachers
Enterprise administrators, web administrators, infrastructure architects, security professionals, systems engineers, network administrators, IT professionals, security
However, the present study employed pure tones rather than white noise, and it could be argued that as white noise is more aversive than tones (Graham & Slaby, 1973),
JEUNE FRENCH CONTEMPORARY UPHOLSTERED PANEL CRIB/TODDLER BED KIT ASSEMBLY INSTRUCTIONS Model No: /14
DO NOT place bed near windows where cords from blinds or drapes may strangle a child.. DO NOT place items with a string, cord, or ribbon around a child's neck, such as hood strings
Basic description that demonstrates some relevant knowledge and understanding of the theory but lacks detail and may be muddled. There is little evidence of selection of material
