ODI 11G hand book

Full text

(1)Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. b a r e f. s. an r t n. no a Oracle Data sIntegrator 11g: a h ) and ฺ Administration e m Integration d o i eฺc t Gu l c ra den o @ Stu a m r is Activity Guide - Volume I a h t h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. D64974GC20 Edition 2.0 September 2012 D78956.

(2) Author. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Richard Green. Disclaimer. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 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. 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:. s. 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.. o. an s ha ฺ ) om uide c ฺ cle ent G Gr aphic Designer s a r Satish Bettegowda @o Stud a Seema Bopaiah rm this a h ฺs use g ฺ Publisher s l hu se to Giri Venugopal (ra a licen Sumesh Koshy m r a Rameshkumar Srividya h S ul Rashmi Rajagopal Vijayalakshmi Narasimhan Aju Kumar Kumar. an r t n. Trademark Notice. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.. h. Ra. le. b a r e f.

(3) Table of Contents. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 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. s. an r t n. 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. no a s a Practices for Lesson 6: Oracle Data Integrator Model Concepts............................................................. 6-1 h ) ฺ Practices for Lesson 6: Overview............................................................................................................. 6-2 om uide c Practice 6-1: Creating a Model ................................................................................................................ 6-3 ฺ e tG clODI a Practices for Lesson 7: Organizing ODI Models and Creating Datastores n ....................................... 7-1 r e o d Practices for Lesson 7: Overview............................................................................................................. 7-2 @ Stu a..................................................................................... Practice 7-1: Checking Data Quality in therModel 7-3 m s a e...................................................................................... hi t h Practices for Lesson 8: ODI Interface Concepts 8-1 s ฺ............................................................................................................. s g ฺ u l Practices for Lesson 8: Overview 8-2 u o t h Practice 8-1: Creating se Simple Transformations .................................................................. 8-3 (raODIeInterface: n a Practices for Interfaces .......................................................................................... 9-1 c mLesson 9:liDesigning r a Practices for Lesson 9: Overview ............................................................................................................. 9-2 h S l Practice 9-1: Creating ODI Interface: Complex Transformations ............................................................... 9-3 ahu. R. 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. le. b a r e f.

(4) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 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 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. s n a r -t 19-1 Practices for Lesson 19: Working with Changed Data Capture ............................................................... n o n Practices for Lesson 19: Overview ........................................................................................................... 19-2 a s Practice 19-1: Implementing Changed Data Capture ................................................................................ 19-3 a h ) ฺ Practices for Lesson 20: Advanced ODI Administration.......................................................................... 20-1 om uide c Practices for Lesson 20: Overview ........................................................................................................... 20-2 ฺ cle ent G Practice 20-1: Setting Up ODI Security .................................................................................................... 20-3 a r o Practice 20-2: Integration with Enterprise Manager and Using ODI @ StudConsole.............................................. 20-22 a Practices for Lesson 21: Extending Oracle Data Integrator 21-1 is with SDK, Web Services, and SOA ............ 21-2 arm h t Practices for Lesson 21: Overviews ........................................................................................................... h ฺScenario u se the ODI Public Web Service....................................... 21-3 g Practice 21-1: Executing an lODI Through ฺ hu se to a r ( en a m ic l r a Sh l u ah. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Oracle Data Integrator 11g: Integration and Administration Table of Contents ii. le. b a r e f.

(5) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. an r t n. no a s a h Practices for Lesson 1: ) ฺ e m Introduction uidIntegration ฺco Gto e l t n and racAdministration e o @ Chapter tu1 d a S rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 1.

(6) Practices for Lesson 1: Overview Practices Overview. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. In this practice, you will start up the ODI client, and examine the help system and Start Page.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 2.

(7) Practice 1-1: Starting the ODI Client Overview. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. In this practice, you start up the ODI client, explore the help system, and examine the links on the Start Page.. Assumptions None. Tasks 1.. Start Oracle Data Integrator and explore the help system: Step a.. Choices or Values. MS Windows. Start Oracle Data Integrator: Start > Programs > Oracle > Oracle Data Integrator > ODI Studio.. link to the Oracle Technology Network.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 3. le. b a r e f. s n a r -tFrom the main b. Oracle Data Integrator The ODI client window opens. n o menu, select Help > Table a n of Contents. s c. Oracle Data Integrator The Help Center Expand the haappears. ) ฺ navigation tree of the Contents DoubledeOnline Help.tab. om uthe i c click Opening Observe that ฺ e panel G opens to the right, with details clanother t a n r deyour chosen topic. Close the Help u @o Stabout a m his Center. r a h se t From the Help menu, select ODI Forum. d. Oracle Data Integrator s ฺ g u lฺIntegrator e. Oracle Data u If you have an Internet connection, the Oracle o t h a e r ODI Forum web page opens. Scroll through ( ens a the list of topics. Close the browser. m ic l r a f.h Oracle Data Integrator On the Help menu, note that there is also a ul S. h. Ra. Screen/Page Description.

(8) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to b. a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 4.

(9) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ d. cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 5.

(10) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. e.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. f.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 6.

(11) 2.. Examine the links on the Start Page:. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Step. Screen/Page Description. Choices or Values. a.. Oracle Data Integrator. On the Start Page, click Define the Topology.. b.. Oracle Data Integrator. On the Define the Topology tab, examine the five different categories of information. Click Create and Connect to your Repositories.. le. l u h a. b a r e f the Oracle Data Integrator The Help Center opens with two tabsson c. right side of the ODI client. tran -the navigation n On the Contents tab, expand o n tree to see the helpatopics. s a right of the Contents tab, On the tab tohthe ) ฺ for help on the topic notice m the detailed links e d o i c Gand u connecting to your ฺcreating eofrepositories. l c t ra den o @ Stu a m r is Click X to close the Help Center window. Oracle Data Integrator d. a h t h ฺs use Click X to close the Define the Topology tab, g ฺ l u which also closes the Start Page. If you want o h se t a r to re-open the Start Page later, you can ( en a select Start Page from the Help menu. c m i l r a h. S. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 7.

(12) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r b. @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 8.

(13) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. Sh l u ah d.. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 9.

(14) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 1: Introduction to Integration and Administration Chapter 1 - Page 10.

(15) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. an r t n. no a s a h Practices for Lesson 2: ) ฺ e m d Administering ฺco GuiODI e l Repositories rac dent o @ Chapter tu2 a S m s ar e thi h s ฺgฺ to us l u ah nse r ( a lice arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 1.

(16) Practices for Lesson 2: Overview Practices Overview. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. In this practice, you will use SQL Developer to create database users for Master and Work Repositories. You will then log in to ODI and connect to these repositories.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 2.

(17) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Practices for Lesson 2: Flow of Data. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 3.

(18) Practice 2-1: Creating and Connecting to ODI Master and Work Repositories. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Use Case Scenario: John works as a database administrator for FN Enterprise. In FN Enterprise, John is responsible for performing database management and integration tasks on various resources within the organization. In particular, John is responsible for data loading, transformation, and validation. To set his ODI environment, he needs to set up security and create Master and Work Repositories.. Background: To set up Oracle Data Integrator, first create database users for the Master and Work Repositories. Then, create the Master Repository, connect to the Master Repository, create the Work Repository, and then connect to the Work repository. In this practice, you first use SQL Developer to create the database users for the Master and Work Repositories. Then, in ODI, you create and connect to the ODI Master Repository and the ODI Work Repository. Note: These are practice repositories. In later lessons, you use other Master and Work Repositories that are partially predefined with source and target objects.. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a SQL Developer rm is a h t h ฺsuser for se g ฺ u l • Create SNPM1 database hu se to Master Repository a r ( en user for • Create SNPW1 database a m Repository ic l r Work a h data base play . png. l u h a. S. R. datab0 24.gif. •. •. Oracle Data Integrator. Create Master Repository • Create Master Repository connection “Master Repository Pr 2-1” with SNPM1 as the user Create Work Repository • Create WORKREP_PR2-1 Work Repository connection with SNPW1 as the user. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 4. le. b a r e f.

(19) Your Tasks:. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 1.. The RDBMS used in this practice is Oracle 11g. To connect to your RDBMS, perform the following steps: Step. Screen/Page Description. Choices or Values. a.. MS Windows. Start Oracle SQL Developer by selecting Start > Programs > Oracle-OraDb11g_home1 > Application Development > SQL Developer. When SQL Developer starts, close the Messages – Log tab at the bottom. Also close the Start Page.. b.. Oracle SQL Developer. In Oracle SQL Developer, click “+” to expand the connection Administrator. In the window that follows, enter system in the Username field and oracle1 in the Password field, and then click OK.. s. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. a.. an r t n. Sb.h l u ah. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 5. le. b a r e f.

(20) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 2.. Create the RDBMS schema or user (Oracle 11g) for the Master Repository. Step. a.. Screen/Page Description Oracle SQL Developer. Choices or Values Execute the following SQL command in the Worksheet: create user snpm1 identified by password default tablespace users temporary tablespace temp; Note: You can find this command in the text file 2-1.txt located in c:\LABS\Text folder. Enter this command and then click the green, arrowhead-shaped Run Statement button. Note: In this command, “password” is the value of password to connect to user “snpm1.”. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h b.. Oracle SQL Developer. Grant connect privileges to the newly created user account by executing the following SQL command: grant connect, resource to snpm1;. c.. Oracle SQL Developer. Expand the Other Users node to verify that the “snpm1” user account was successfully created. Minimize SQL Developer.. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 6. le. b a r e f.

(21) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. b a r e f. b.. s. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 7.

(22) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. le. b a r e f. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. 3.. Create the ODI Master Repository: Step. Screen/Page Description. Choices or Values. a.. MS Windows. Start Oracle Data Integrator: Start > Programs > Oracle > Oracle Data Integrator > ODI Studio.. b.. Oracle Data Integrator. Open the New Gallery window by choosing File > New. In the New Gallery, in the Categories tree, select ODI. From the Items list, select the Master Repository Creation Wizard. Click OK.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 8.

(23) The Master Repository Creation Wizard appears.. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. Master Repository Creation Wizard. In the Master Repository Creation Wizard, note that the JDBC Driver field has the correct selection to connect to an Oracle database: oracle.jdbc.OracleDriver. Edit the JDBC URL to read: jdbc:oracle:thin:@localhost:1521:orcl Specify the ODI client login username and the database username by performing the following: Enter the username as snpm1 and the password as password . Enter the DBA username as system and the DBA password as oracle1. Click the Test Connection button and verify successful connection. Click OK. Click Next on the Master Repository Creation Wizard screen.. le. b a r e f. s. an r t n. no a s a h ) ฺ window, enter the Master Repository Creation Wizard In the Authentication e m d. d o i u ฺc GPassword as SUNOPSIS. Enter eSupervisor l c t SUNOPSIS again to confirm the password. a n r Click e o d Next. @ Stu a Usernames and passwords are m r is Note: a h t h case-sensitive in ODI. s e ฺ s g lฺStorageto u Password In the Password Storage window, select u e. h a e Internal Password Storage, and then click r ( ens a Finish. When Master Repository is lic arm successfully created, you will see the Oracle. h. lS u h a. Data Integrator Information message. Click OK. The ODI Master Repository is now created.. R. f.. Message. Verify that ODI Master Repository was created successfully, and then click OK.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 9.

(24) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. an r t n. b.. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. b a r e f. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 10.

(25) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G c. a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 11.

(26) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. d.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 12.

(27) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. e.. le. b a r e f. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l u to h a e r f. ( ens a m ic l r a h ul S. h. Ra. 4.. Connect to the ODI Master Repository by creating a new ODI Master Login: Step. Choices or Values. Screen/Page Description. a.. Oracle Data Integrator. Open the New Gallery by selecting File > New. In the New Gallery, in the Categories tree, select ODI. From the Items list, select Create a New ODI Repository Login. Click OK.. b.. Repository Connection Information. Configure the repository connection with the parameters from this table. To enter the JDBC URL, click the button next to the URL field and select Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 13.

(28) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. jdbc:oracle:thin:@<host>:<port>:<sid>, as shown in the screen, then edit the URL. Select the Master Repository only button. Click the Test button. Verify successful connection and click OK. Click OK to save the connection. Oracle Data Integrator Connection Parameter. Value. Login Name. Master Repository Pr 2-1. User. SUPERVISOR. Password. SUNOPSIS. le. no a s a h snpm1 User ) ฺ e m d o i eฺc t Gu l Password password c ra den o @List Oracle tu JDBC Driver Driver a S m s ar e thi h s ฺgฺ to usDriver oracle.jdbc.OracleDriver l u Name h (ra ense a Url jdbc:oracle:thin:@localhost:1521:orcl lic arm Parameter Value. h. lS u h a. s. an r t n. Database Connection (Master Repository). b a r e f. Note. R. •. •. Do not copy and paste in the URL field. This may cause problems with entering a valid URL string. Instead, open the drop-down menu and select the correct driver from the list. Type the correct URL in the Url field. The ODI username (SUPERVISOR) is case-sensitive.. c.. Oracle Data Integrator. Click Connect to Repository. Select the newly created repository connection, Master Repository Pr 2-1, from the drop-down list. Click OK. You are now successfully logged in to the Master Repository Pr 2-1.. d.. Import Service Description. Click the Topology tab (the tab title might appear only partially as To… ). Click the Repositories tab in the left panel of the Topology Manager. Verify that your newly created Master Repository is in the Repositories window.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 14.

(29) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. b a r e f. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. b.. h. lS u h a. an r t n. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 15.

(30) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. c.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 16.

(31) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. d.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 17.

(32) 5.. Use SQL Developer to create the RDBMS schema/user for the Work repository. Step. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. Screen/Page Description Oracle SQL Developer. Choices or Values If SQL Developer is already opened, it might display a message about externally modified objects. If so, click OK to close the message. If an earlier command is in the worksheet, clear the worksheet by clicking the Clear button that looks like a pencil eraser. If not opened, open SQL Developer. Create the schema by executing the following SQL command: create user snpw1 identified by password default tablespace users temporary tablespace temp; Note: You can find this command in the text file 2-1.txt located in c:\Labs\Text folder. Click the Run Statement button.. s. an r t n. no a s a h ) ฺ e m d o i uprivileges to the newly created ฺc connect eGrant G l b. Oracle SQL Developer c t n executing the following SQL ra user by e o d @ Stucommand: a m r is grant connect, resource to snpw1; a h t h ฺs use g ฺ l hu se to a r ( SQL c. Oracle Run the following command to verify that the a enDeveloper c m i l r snpw1 user account was successfully a. h. lS u h a. R. created and shown among other user accounts in the list: select * from all_users;. a.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 18. le. b a r e f.

(33) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. b.. le. s. an r t n. c.. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. b a r e f. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 19.

(34) 6.. Now that you created the RDBMS schema/user, use ODI Topology Navigator to create the ODI Work repository:. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Step. Choices or Values. Screen/Page Description. a.. Oracle Data Integrator. In ODI, click the Topology Navigator tab and then click the Repositories panel. Right-click the Work Repositories node and select New Work Repository. The Create Work Repository Wizard opens.. b.. Specify ODI Work Repository Connection Properties. In the screen that follows, enter the parameters shown in the following table. Click the Test button. Verify successful connection and click OK. Click Next.. le. Parameter Value Technology Oracle. s. an r t n. no a s a h ) ฺ e m JDBC Url jdbc:oracle:thin:@localhost:1521:orcl d o i eฺc t Gu l c n ra snpw1 e User o d u the default snpm1) @ St(not a m s ar ePassword hi t h password s ฺ s g lฺ to u u h c. In the Specify ODI Work Repository properties panel, set the Specify ODI Work a properties e r s ( Repository ID to “1”. Set the Name to WORKREP_PR2-1. In the n a lice m r Password field, enter SUNOPSIS. In the Work Repository a h Type field, leave Development unchanged. Click Finish. ul S JDBC Driver. h. Ra. b a r e f. oracle.jdbc. OracleDriver. Verify that the newly created Work repository is now in the work repositories tree view. Note: The Development type of repository enables management of design-time objects such as data models and projects (including interfaces, procedures, and so on). A development repository also includes the run-time objects (scenarios and sessions). This type of repository is suitable for development environments.. d.. Create Work Repository Login. In the Confirmation window, click Yes. Enter the Login name: Work Repository Pr 2-1. Click OK.. e.. Oracle Data Integrator. Expand the Work Repositories node and verify that the Work repository WORKREP_PR2-1 was created.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 20.

(35) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. b a r e f. o. an r t n. an s b. ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 21.

(36) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 22.

(37) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G d. a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 23.

(38) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. e.. le. b a r e f. s. an r t n. no a s a h ) ฺ e m 7. Disconnect from the Master Repository and connect to o the Work repository: d i u eฺcChoices G l Step or Values c Screen/Page t ra den o Description tu a@ s Smenu a. Oracle Data Integrator rm Click the iODI and select Disconnect “Master a h t Repository Pr 2-1.” h ฺs use g ฺ l hu se to a r ( en a mOracle lData ic Integrator Click “Connect To Repository.” r a b. h ul S. h. Ra. c.. Oracle Data Integrator Login. Select “Work Repository Pr 2-1” from the Login Name drop-down list. In the Password field, enter SUNOPSIS. Click OK. Click the Designer tab. Collapse the Projects node. The following ODI Designer screen appears. You have now successfully created and connected to the ODI Work repository.. d.. Oracle Data Integrator. Click ODI menu and select Disconnect Work repository Pr 2-1. Note: You will not be using these practice Master and Work Repositories for the remainder of this course.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 24.

(39) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. b a r e f. o. an r t n. an s ha ฺ b. ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. c.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 25.

(40) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. d.. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. b a r e f. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 2: Administering ODI Repositories Chapter 2 - Page 26.

(41) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. an r t n. no a s a h Practices for Lesson 3: ODI ) ฺ e m Topology uid ฺco Concepts e G l rac 3 dent Chapter o @ Stu a m r is a h t h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 1.

(42) Practices for Lesson 3: Overview Practices Overview. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. In this practice, you set up and install an ODI Agent as a background service.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 2.

(43) Practice 3-1: Setting Up and Installing an ODI Agent. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Use Case Scenario: John is responsible for data loading, transformation, and validation. He created Master and Work Repositories. Now he continues setting up his ODI environment by installing an ODI Agent.. Background: A common task in ODI is to set up and install an ODI Agent as a service. After the ODI scenarios are created, they can be scheduled and orchestrated by using an ODI Agent, which is a lightweight Java process that orchestrates the execution of ODI scenarios. In this practice, you create and execute an ODI Agent, which will be used in subsequent practices for orchestration of the execution of ODI objects. First, you run encode <password> to generate an encrypted password. You copy and paste that string into the odiparams.bat file that contains ODI agent parameters. You then switch to ODI, and define a physical agent named localagent. Next, you define a logical agent also named localagent, mapping it to the same-named physical agent. You specify the Global context as the context in which to make this particular mapping of logical to physical agents. In the command window, you execute agent.bat to start the agent that you named localagent. In ODI, you test a connection to your newly created agent.. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. The directions on the following pages will take you through these activities: 1. Run encode oracle1 to generate an encrypted password string 2. Edit odiparams.bat, inserting the encrypted password 3. 4. 5. 6.. In ODI, define a physical agent named localagent In ODI, define a logical agent named localagent, mapping it to the physical agent named localagent for the Global context Execute agent.bat to start an agent named localagent. Verify connection to the newly created agent localagent in ODI. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 3. le. b a r e f.

(44) Your Tasks: 1.. Before executing the agent.bat file to create the ODI Agent, you must run the encode password command to generate an encrypted password and paste it into the odiparams.bat parameter file. To do so, perform the following steps:. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Step a.. Choices or Values. Screen/Page Description MS Windows and text editor. Open the Windows Explorer, and navigate to the ODI_HOME directory, where ODI is installed and open the agent\bin subdirectory: (C:\oracle\Middleware\Oracle_ODI1\oracledi\agen t\bin). Navigate to the odiparams.bat file, right-click this file, and select Edit to open it with a text editor. Leave this editor open. You will run a batch file from a Command window and return to this text editor to copy the results into the odiparams.bat file. Explanation: You need to edit the odiparams.bat file to set the repository connection information for the ODI Agent to use. The password information is stored as an encrypted string. You will generate the encrypted string in a Command window.. le. b a r e f. s. an r t n. b.. no a s a h ) ฺ e m d o i uopen the Command window eฺcopen,t and G l Command Prompt Leave the text c editor n the directory to the ra> cmd).dChange (Start > Run e o @ Stu directory: ODI_HOME\agent\bin a m rC:\oracle\Middleware\Oracle_ODI1\oracledi\agent is a h t h ฺs u\bin se g ฺ l to To generate the encrypted password string, you will be using Command huPrompt a e r ( ens the agent command, encode <password>. Because your a c password is “oracle1”, enter and execute the following li rm. c.. a. Ra. h. h ul S. command in the Command window and press ENTER: encode oracle1. Note: Keep the Command window open, because you will be returning to it after editing the odiparams.bat file.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 4.

(45) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. d.. Command Prompt and text editor. Copy the generated, encrypted password from the Command window (from the Command window’s drop-down menu, select Edit > Mark, then highlight the password with your mouse; then select Edit > Copy) and insert it into the odiparams.bat file as the value for the ODI_MASTER_ENCODED_PASS parameter (overwriting the existing password). Verify and, if necessary, edit settings for other parameters from the following table, save the odiparams.bat file, and then close it. The agent’s connectivity is now set up. Parameter. Value. ODI_MASTER_DRIVER. oracle.jdbc.OracleDriver. ODI_MASTER_URL. jdbc:oracle:thin:@localhost:1521:orcl. ODI_MASTER_USER. DEV_ODI_REPO. s. an r t n. no a s a h ) ฺ e m d o i ฺc Gu e l c ODI_SECU_WORK_REP ra dent WORKREP o @ Stu a m r ODI_SUPRVISOR_ENC is a h t h Leave the default value. ฺs uODED_PASS se g ฺ l hu se to a r ( en Leave the default value. ODI_USER a c m i l r a. ODI_MASTER_ENCODE Insert your encoded password. D_PASS (overwrite the existing password). h. R. lS u h a. ODI_ENCODED_PASS. Leave the default value.. ODI_JAVA_HOME. c:\oracle\Middleware\jdk160_29. Note • • •. •. DEV_ODI_REPO is an RDBMS schema/user (Oracle 11g) for the Master Repository. It was pre-created for this and subsequent practices. The Work Repository name is WORKREP. Because each time that you encode the password, it receives different values, your encoded password will differ from the one provided in the screen. Do not change the default value of the ODI_USER and ODI_ENCODED_PASS parameters. Those parameters were pre-coded during ODI installation.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 5. le. b a r e f.

(46) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ b. cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a ic c. rm l a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 6.

(47) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. d.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 7.

(48) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. b a r e f. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. R. 2. Now h that you have generated an encoded password and pasted it into the Sodiparams.bat l file, you need to create the ODI physical and logical agent definitions. To u ah create these ODI agent definitions, perform the following steps: Step. Choices or Values. Screen/Page Description. a.. MS Windows ODI Login. If it is not started, start Oracle Data Integrator. Click Connect To Repository. Select DEV_ODI_REPO from the Login Name drop-down list. Click OK.. b.. Topology navigator. Click the Topology navigator tab. In the Topology navigator, select the Physical Architecture panel. Right-click the Agents node. Select New Agent.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 8.

(49) Step. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. Choices or Values. Screen/Page Description Agent: localagent. Fill in the following fields: Name: localagent Host: Network name or IP address of the machine that the agent has been launched on. Verify that this parameter is set to localhost. Port: Listening port used by the agent. By default, this port is 20910. Leave the default value. Web Application Context: Name of the Web application corresponding to the Java EE agent deployed on an application server. For stand-alone agents, this field should be set to oraclediagent. Set Maximum number of sessions supported by this agent to 250. Click the Save button.. s. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a. a m ic l r a h ul S d.. Topology Navigator Local Agent: New. Now you have to insert a logical agent in Logical Architecture, and map it to the newly created Physical agent. At the bottom of the Topology navigator tab, click the Logical Architecture tab. Right-click Agents and select New Logical Agent. On the screen that follows, set the Name to localagent. For the Global context, set the Physical Agents column to localagent. From the File menu, click Save. Close the localagent tab that you just worked on, leaving the other localagent tab open.. h. Ra. an r t n. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 9. le. b a r e f.

(50) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. b.. c.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l d. hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 10.

(51) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 3.. s. Step. no a s you left open at the a. Command Prompt Switch back to the Command window a h ) ODI_HOME\agent\bin directory ฺ e m d o (C:\oracle\Middleware\Oracle_ODI1\oracledi\agen i u file by using the following etheฺcagent.bat G l t\bin). Execute c t a den command:or @-NAME=localagent tu a agent S m s ar e thi h s ฺ us agent is now starting. Verify that ODI Agent is successfully b. Services ulฺg The o t h Minimize (do not close) the window Command (ra ense started. Prompt – agent-NAME=localagent. a lic arm Note: This command window should remain open during all. h. lS u h a. R. Choices or Values. Screen/Page Description. an r t n. labs.. c.. Oracle Data Integrator. In ODI, in the localagent tab that remains open, click the Test icon to verify connection of the agent localagent. You successfully created and connected to ODI agent. Close the localagent tab. . Also, close the. Messages - Log. tab.. a.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 11. le. b a r e f. Now that you have created the agent and defined the ODI physical and logical agents, you can start the agent directly from the command line..

(52) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. b.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 12.

(53) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. c.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 13.

(54) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 3: ODI Topology Concepts Chapter 3 - Page 14.

(55) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. an r t n. no a s a h Practices for Lesson 4: ) ฺ e m Describing the uidPhysical and ฺco G e l nt Logical rac deArchitecture o @ Chapter tu4 a S m s ar e thi h s ฺgฺ to us l u ah nse r ( a lice arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 1.

(56) Practices for Lesson 4: Overview Practices Overview. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. In this practice, you define the work infrastructure in the topology by creating contexts, a data server, and physical and logical schemas.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 2.

(57) Practice 4-1: Working with Topology. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Use Case Scenario: John created the Master and Work Repositories and installed an ODI Agent as a background service. To complete setting up his ODI infrastructure, he needs to create contexts, a data server, and physical and logical schemas.. Background: Before you begin working on your ODI projects, you need to describe your ODI infrastructure in the topology. As a starting point of this practice, you will use the environment provided with the ODI installation. The infrastructure includes several servers. You need to define the following new data server and its attached physical schemas. Data Server: ORACLE_ORCL_LOCAL. Define two of three schemas available in this instance: Schema. Description. s. an r t n. no a s a h SALES_PROD Schema storing the production target ) ฺ tables e m d o i c to G u ODI temporary objects STAGING Schema thatle isฺused store c t (not used n ra in thisdepractice) o @ tu contain identical table structures Both the SALES_DEV and SALES_PROD physicalS schemas a m s r and correspond to the same logical called ORACLE_ORCL_LOCAL_SALES. The aschema hi context. t h s mapping for this logical schema depends on the e s ฺgฺtaskstois ucritical l u Note: Completing these for all subsequent practice sessions. h a e r ( ens a m ic l r a Sh l u h SALES_DEV. Schema storing the target tables for development purposes. Ra. This practice consists of the following six steps: 1. Define the Production context. 2. Define the Development context. (A third context, Global, is already preseeded for you.) 3. Define the ORACLE_ORCL_LOCAL data server. 4. Define the ODI physical schemas for the data server: SALES_DEV, SALES_PROD. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 3. le. b a r e f.

(58) 5. 6.. Define the ORACLE_ORCL_LOCAL_SALES ODI logical schema. Map the logical schema to the two physical schemas, in terms of the three contexts.. Your Tasks:. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 1.. If not started, start Oracle Data Integrator and open the DEV_ODI_REPO connection. If ODI remains open, skip to step 2. Note: The username and password are case-sensitive. a. Select Start > Programs > Oracle ODI11g-Home > Oracle Data Integrator > ODI Studio. Click Connect To Repository.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 4.

(59) b.. Log in with the following details. Click OK. Login name: DEV_ODI_REPO. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. User: SUPERVISOR Password: SUNOPSIS. le. 2.. s. an r t n. Create a new context: a. Click the Topology navigator tab, and then click the Contexts tab to expand it.. b a r e f. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. b.. Click the New Context icon. on the Contexts tab, and then select New Context.. c.. Enter your context parameters as shown below. The context window should appear as follows. Name: Production Code: PRODUCTION Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 5.

(60) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Password: Leave this field empty. Default: Select this check box, and click Yes to confirm in the pop-up window.. le. s. b a r e f. o. an r t n. Ra. h. an s ha ฺ ) d. Click the Save button. om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a e. Create another m ic new context , repeating the operations: l r a h Name: Development ul S Code: DEVELOPMENT Password: Leave this field empty. Default: Leave the check box deselected.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 6.

(61) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. The contexts should appear as follows:. 3.. Create an ORACLE_ORCL_LOCAL data server: a. Click the Physical Architecture tab. Expand the Technologies node, scroll down and select the Oracle node, and then right-click and select New Data Server.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. b.. Enter the following information on the Definition tab: • Name: ORACLE_ORCL_LOCAL • Instance / dblink (Data Server): ORCL Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 7.

(62) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. • •. User: ODI Password: ODI. le. b a r e f. s. an r t n. no a s a h ) ฺ e m d o i eฺc t Gu l c nof the JDBC Driver field. In the rato thedright c. Click the JDBC tab. Click the button e o @JDBCSDriver, window that appears, select Oracle tu and then click OK. a m s ar e thi h s ฺgฺ to us l u ah nse r ( ma lice r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 8.

(63) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. d.. Click the button to the right of the JDBC URL field. In the URL examples window, select the first URL in the Name list, and click OK.. le. s. b a r e f. Edit the JDBC URL to have the following: URL: jdbc:oracle:thin:@localhost:1521:ORCL The JDBC tab should now appear as follows:. h. lS u h a. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. e.. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 9. an r t n.

(64) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 4.. Test this data server: a. Click the Test Connection button. In the Confirmation dialog box, click OK to confirm saving your data before testing the connection. In the Information window, click OK.. le. s. b a r e f. an r t n. o. h. lS u h a. R. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. b.. In the Test Connection dialog box, click the Test button. In the Information dialog box that reports a successful connection, click OK.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 10.

(65) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 5.. Create a physical schema for the newly created ORACLE_ORCL_LOCAL data server. Name the physical schema ORACLE_ORCL_LOCAL.SALES_DEV with the following parameters: •. Data Schema: SALES_DEV. •. Work Schema: STAGING. •. Default check box: Selected. a.. Expand the Oracle node. Right-click the newly created data server ORACLE_ORCL_LOCAL, and then select New Physical Schema.. le. b a r e f. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. b.. In the new window that appears, select SALES_DEV from the Schema (Schema) drop-down list, and then select STAGING from the Schema (Work Schema) drop-down list. Select the Default check box. In the Confirmation window that appears,. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 11.

(66) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. click OK. Leave all the other fields unchanged. Click the Save button the Information window that appears.. . Click OK in. le. s. b a r e f. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r c. Expand: Oracle > ORACLE_ORCL_LOCAL. The d Physical schema u @o appears t ORACLE_ORCL_LOCAL.SALES_DEV a S in the tree view: m s r i ha se th s ฺ lฺg to u u h (ra ense a m ic l r a h ul S. h. Ra. 6.. Create a second physical schema for the ORACLE_ORCL_LOCAL data server. Name it ORACLE_ORCL_LOCAL.SALES_PROD: •. Data Schema: SALES_PROD. •. Work Schema: STAGING. •. Default Schema: Not selected Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 12.

(67) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. Select the ORACLE_ORCL_LOCAL data server in the tree view, and then right-click and select New Physical Schema.. b.. In the new window that appears, select SALES_PROD from the Schema (Schema) drop-down list, and then select STAGING from the Schema (Work Schema) drop-down list. Confirm that the Default check box is not selected, and leave all the. s. other fields unchanged. Click the Save button OK.. . In the Information window, click. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. o. an r t n. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 13. le. b a r e f.

(68) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 7.. Create a logical schema, ORACLE_ORCL_LOCAL_SALES, and map this schema to different physical schemas, for the different contexts. • Development Context: To the ORACLE_ORCL_LOCAL.SALES_DEV physical schema •. Production Context: To the ORACLE_ORCL_LOCAL.SALES_PROD physical schema. • a.. Global Context: To the ORACLE_ORCL_LOCAL.SALES_DEV physical schema In Topology Manager, select the Logical Architecture tab and expand the Technologies node. Select Oracle, right-click and select New Logical Schema.. le. b a r e f. s. an r t n. no a s a h ) ฺ e m d o i eฺc t Gu l b. Enter the name of the logical schema: ORACLE_ORCL_LOCAL_SALES. To map this c n from the drop-down ra dcontexts, e logical schema to physical schemas inodifferent lists, @ forSeach tu context, as shown in the following select the appropriate physical schema a rm this screen. Click the Save button. a h ฺs use g ฺ l hu se to a r ( en a m ic l r a h ul S. h. Ra. 8.. The logical schema FILE_DEMO_SRC is predefined for you and is mapped in the predefined Global context to a physical schema that represents a subfolder in a file system. Map FILE_DEMO_SRC to the same physical schemas in the new Development and Production contexts that you defined earlier.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 14.

(69) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. a.. In the Logical Architecture, expand the File node, and double-click the FILE_DEMO_SRC logical schema. Ensure that the logical schema FILE_DEMO_SRC is mapped to the physical schemas in the Development, Global, and Production contexts, as follows. Click the Save button.. le. b a r e f. s. an r t n. no a s a h ) ฺ e m d o i eฺc t Gu l c ra den o @ Stu a m r the following is logical schemas: a b. Repeat the previous step for h t h ฺs the logical se schema XML_DIM_GEO should be mapped to the g • In XML technology, ฺ u l u XML_GEO_DIM.GEO, to hschema physical as follows. Click the Save button. a e r s ( n ma lice r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 15.

(70) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. •. In Hypersonic SQL, the logical schema HSQL_DEMO_SRC should be mapped to the physical schema HSQL_LOCALHOST_20001_Default in all contexts.. le. b a r e f. s. an r t n. no a s a h ) ฺ e m d o i eฺc t Gu l c • In Hypersonic SQL, the logical schema should be mapped to n ra HSQL_DEMO_TARG e o d the physical schema HSQL_LOCALHOST_20002_Default, as follows. @ Stu a m s ar e thi h s ฺgฺ to us l u ah nse r ( a lice arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 16.

(71) Note: Ensure that these mappings are similar in the three contexts. Close all the tabs, as shown. If you are prompted to save your changes, click Yes.. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 9.. You have set up ODI agent localagent in Practice 3-1. Now, link the Logical agent to the Physical agent in all three contexts and test the connection. a. In the Physical Architecture, expand the Agents node, and open the Physical agent localagent. Similarly, open the Logical agent, localagent, in the Logical Architecture. Ensure that this Logical agent, localagent, is linked to Physical agent localagent in all three contexts as follows. Save your changes.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 17.

(72) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. b.. In the Physical agent’s localagent tab, click Test, and verify successful connection. Click OK, and then close all the tabs.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 4: Describing the Physical and Logical Architecture Chapter 4 - Page 18.

(73) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. le. s. b a r e f. an r t n. no a s a h Practices for Lesson 5: ) ฺ e m o a New Setting uid ODI Project ฺcUp e G l rac 5 dent Chapter o @ Stu a m r is a h t h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 5: Setting Up a New ODI Project Chapter 5 - Page 1.

(74) Practices for Lesson 5: Overview Practices Overview. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. In this practice, you create a new ODI project and import Knowledge Modules that will be used in subsequent practice sessions.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 5: Setting Up a New ODI Project Chapter 5 - Page 2.

(75) Practice 5-1: Setting Up a New ODI Project. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. Use Case Scenario: John is responsible for an integration project, which includes data loading, transformation, and validation. He created contexts, a data server, and physical and logical schemas in the Topology. To start an integration project, John needs to create a new ODI project and import knowledge modules that will be used for development of his project.. Background: Now you assume the role of a project leader who is starting an integration project. You create a project named HandsOnLoads with a folder named HandsOn. You import knowledge modules for working with three sources (Hypersonic SQL, XML, and File) and one target (Oracle.) Note: Completing this practice is critical for all the following practice sessions.. s. an r t n. o. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Your Tasks: 1.. Create a project, HandsOnLoads, with a folder called HandsOn. a.. In Designer Navigator, click the Projects tab, click the New Project select New Project.. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 5: Setting Up a New ODI Project Chapter 5 - Page 3. le. b a r e f. icon, and then.

(76) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. b.. Enter the name of the project: HandsOnLoads. The Code field is automatically filled with your Name field entry.. c.. Click the Save button to create the project. The project appears in the tree view. Expand the HandsOnLoads project node.. le. b a r e f. s. an r t n. no a s a h ) ฺ e m d o i eฺc t Gu l c ra den o @ Stu a m rFirstFolder. isDouble-click this folder. In the editing window a h t d. ODI creates a folder named h ฺsthe name sein the Name field to HandsOn. Click Save. g that appears, change ฺ u l hu se to a r ( en a m ic l r a h ul S. h. Ra. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 5: Setting Up a New ODI Project Chapter 5 - Page 4.

(77) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ. 2.. Import the knowledge modules that are required for working with the following technologies: • Sources: − Hypersonic SQL, XML, File •. Targets: − Oracle. a.. Select the HandsOnLoads project node. Right-click, and then select Import > Import Knowledge Modules.. le. s. b a r e f. o. an r t n. an s ha ฺ ) om uide c ฺ cle ent G a r @o Stud a rm this a h ฺs use g ฺ l hu se to a r ( en a lic arm. h. lS u h a. R. Copyright © 2012, Oracle and/or its affiliates. All rights reserved.. Practices for Lesson 5: Setting Up a New ODI Project Chapter 5 - Page 5.

No results found