oracle data integrator 11g: integration and...

Oracle Data Integrator 11g: Integration and Administration

Activity Guide - Volume II

D64974GC20

Edition 2.0

September 2012

D78957 O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.

Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Disclaimer 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.

Author Richard Green

Technical Contr ibutors and Reviewers 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

Graphic Designers Satish Bettegowda Seema Bopaiah

Publishers Giri Venugopal Sumesh Koshy Srividya Rameshkumar

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Oracle Data Integrator 11g: Integration and Administration Table of Contents

i

Table of Contents

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Oracle Data Integrator 11g: Integration and Administration Table of Contents

ii

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

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 12: Designing Interfaces: Advanced Topics 2

Chapter 12 - Page 1


Chapter 12

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 2

Practices for Lesson 12: Overview

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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 3

Practice 12-1: Using Temporary ODI Interfaces

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.

• Use datastores SRC_ORDERS and SRC_ORDER_LINES from the HSQL_SRC model, joined on ORDER_ID.

• Use SRC_CUSTOMER as a lookup table.

• Create a temporary target table, TEMP_AGG_ORDERS.

• Aggregate some of its columns by using the MIN, MAX, and AVG functions.

2. Create interface INT_12-1-2

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

• Use the datastore TRG_SALES as the target.

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 4

Your Tasks: 1. Develop an ODI Temporary interface.

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

b. In the Projects tab, expand: HandsOnLoads > HandsOn. Right-click Interfaces, and

then select New Interface. Name the new interface INT_12-1. In the Optimization Context field, select Development. For Staging Area, select Hypersonic SQL: HSQL_DEMO_SRC. Click the Mapping tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 5

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 6

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 7

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 8

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 9

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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 12 - Page 10

h. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




i. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




j. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




k. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




l. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




m. Edit the expression to read LKUP_SRC_CUSTOMER.FIRST_NAME || ‘ ‘ ||

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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




n. Scroll down, and in the Target Column Properties, rename this column CUST_NAME.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




o. 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).

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




p. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




q. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




r. 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.

Column Execution Location

SUM_AMOUNT Staging Area

AVG_AMOUNT Staging Area

CUST_ID Source

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




CUST_NAME 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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




t. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




2. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. 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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. To view data derived from the temporary interface, right-click the temporary interface

and select Data. Close the Data Editor window.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




h. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. Execute the interface INT_12-1-2 and verify the execution results.

a. Execute the interface INT_12-1-2.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Practice 12-2: Developing a New Knowledge Module

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-EXP-FF-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.

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.

21

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. On the Details screen, review the commands in the current KM. Select Insert new

rows and then click the Add icon to add the new command.

e. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Note: You can find this command in the file 12-2.txt, provided in the following location: c:\Labs\Text.

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)

)

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. 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 .

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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Note: You can find this command in the file 12-2.txt, provided in the following location: c:\Labs\Text.

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”]).

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




getColList:

• Notice the “PK” parameter. If it is used, only the columns that are part of the primary key are included.

`

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




h. After clicking Save, verify that your new knowledge module, IKM Oracle UI – Audit, appears in the Knowledge Modules tree.

2. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




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.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Click the Operator tab to open ODI Operator. Click to open the All Executions node. If

necessary, click the Refresh icon , find your new interface session, and view the session steps.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Similarly, view the Insert Audit Records step information. Refer to the following screens. Click Close.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. 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 .

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. 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 .

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




h. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 13: Using ODI Procedures

Chapter 13 - Page 1


Chapter 13

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 2


Practices Overview In this practice, you define an ODI procedure to create a relational table and populate it with data.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 3

Practice 13-1: Creating an ODI Procedure

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-create-populate-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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 4

Your Tasks: 1. 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 icon, and select New Project.

b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 5

c. 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 to add a step in the procedure.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 6

f. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 7

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.

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")

)

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 8

h. Click the Details tab. Click the icon to enter the step to populate the SRC_SALES_PERSON table.

i. 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.

Parameter Value

Name Populate Table

Technology Oracle

Schema ODI_STAGE

Ignore Errors Deselected

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 13 - Page 9

begin

insert into ODI_STAGE.SRC_SALES_PERSON values

(11,'Andrew','Andersen','22/02/1999',sysdate);


(12,'John','Galagers','20/04/2000',sysdate);


(13,'Jeffrey','Jeferson','32422',sysdate);


(20,'Jennie','Daumesnil','28/02/1988',sysdate);


(21,'Steve','Barrot','24/09/1992',sysdate);


(22,'Mary','Carlin','14/03/1995',sysdate);


(30,'Paul','Moore','36467',sysdate);


(31,'Paul','Edwood','18/03/2003',sysdate);


(32,'Megan','Keegan','29/05/2001',sysdate);


(40,'Rodolph','Bauman','29/05/2000',sysdate);


(41,'Stanley','Fischer','37233',sysdate);


(42,'Brian','Schmidt','25/08/1992',sysdate);


(50,'Anish','Ishimoto','30/01/1992',sysdate);


(51,'Cynthia','Nagata','28/02/1994',sysdate);


(52,'William','Kudo','28/03/1993',sysdate);

end; Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




j. Select the Details tab and note the three steps. Click Save and close the tab.

2. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




5. 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

6. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




7. Execute your procedure. In Operator, verify that the procedure executed successfully.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




8. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 14: Using ODI Packages

Chapter 14 - Page 1


Chapter 14

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 2


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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 3

Practice 14-1: Creating an ODI Package

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




Finally, you run this package and review its execution in ODI Operator.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 4

Your Tasks: 1. 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 .

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 5

d. 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

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 6

f. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 7

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 8

g. Open the Details tab to view the commands. Click the Save button, and then close the procedure tab.

2. Create a package, PKG_14-1, that runs:

• The DELETE_TARGET procedure

• The INT_8-1 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 14 - Page 9

c. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. 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.

3. 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.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. In the Operator Navigator, click the Refresh icon. In the session list, expand the All

Executions node. The session called PKG_14-1 should appear as complete. You can expand the nodes in the session to see the steps and tasks corresponding to your procedure commands and the interfaces that have been executed.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. Close the PKG_14-1 tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Practice 14-2: Using ODI Package with Variables and User Functions

Use Case Scenario: John needs to add more complexity to the workflow by creating a user function for mapping the values. This function will convert the values (0, 1, 2) to (“Mr,” “Mrs,” “Ms”). He also needs to create a variable DELETION_FLAG for counting the number of lines in TRG_CUSTOMER and to have the Delete Target step running only if DELETION_FLAG is greater than 0.

Background: A common task that is performed by using ODI is using user functions and variables to add more complexity to your workflows and mappings. This practice will explain how to use variables, user functions, and the more advanced features in the packages.

1. In the HandsOnLoads project, define a user function group named Conversion, containing a user function named DearConvert.

2. Use this function in the interface INT_9-1 to convert the values (0, 1, 2) to (“Mr”, “Mrs”, “Ms”).

3. In ODI Designer, create a numeric variable DELETION_FLAG that counts the number of lines in TRG_CUSTOMER.

4. Duplicate the PKG_14-1 package, naming the new package PKG_14-2.

5. Have the DELETE_TARGET procedure run only if the DELETION_FLAG variable is greater than 0.

Your Tasks: 1. Create a user function called DearConvert in a new user function group called

Conversion. This function converts the values (0, 1, 2) to (“Mr,” “Mrs,” “Ms”). Implement this function for Oracle as linked technology with a CASE WHEN expression.

Note: Remember to use the dollar sign ($) when using the parameters.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




a. Select the User Functions node in the HandsOnLoads project. Right-click this node and select New User Function.

b. Enter DearConvert in the Name field, enter Conversion in the Group field, and enter

the following in the Description field:

Converts the values (0, 1, 2) to (“Mr”, “Mrs”, “Ms”). In the Syntax field, enter: DearConvert($1)

Note: $1 is the parameter name you use in the decision case structure shown in the following screenshot.

Click the Implementations tab, and then click the Add button.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. In the Implementation window, select Hypersonic SQL from the list of Linked

Technologies. Verify that Oracle technology is also selected. Enter the following code in the Implementation syntax field. Click OK to save this implementation. Click the Save icon, and then close the DearConvert user function tab.

Note

• In Locking/Unlocking Object windows that may appear, click Yes.

• To enter the commands, you may refer to the file 14-2.txt provided at the following location: c:\Labs\Text.

CASE WHEN $1 = '0' THEN 'Mr' WHEN $1 = '1' THEN 'Mrs'

WHEN $1 = '2' THEN 'Ms' ELSE NULL END

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




2. You use this function in the interface INT_9-1 to convert the source DEAR column into the target DEAR column.

a. In the Projects tree view, expand Interfaces. Double-click the interface INT_9-1 to open it. In the Locking window, click Yes. Click the Mapping tab.

b. In the Target datastore, select the Dear column. Edit the implementation in the

Properties panel to have the following code:

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




DearConvert(SRC_CUSTOMER.DEAR). Scroll down to select the Staging Area option from the Execute on panel. Click the Save button to save the interface.

3. Run the INT_9-1 interface and review the conversion result.

a. In the interface window, click the Execute button. In the Execution window, select Development context, and then click OK. Click OK when the “Session started” message appears. Close the INT_9-1 editing tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Click the Operator tab. Select All Executions, and then click Refresh. Expand the All Executions node. The session called INT_9-1 should appear as complete. Note that the steps you see may not be identical to those shown below.

c. In the Mapping tab, right-click the Target datastore and select Data. Verify that the

column DEAR was populated with “Mr” as shown below.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




4. Create a numeric variable called DELETION_FLAG that counts the number of lines in

TRG_CUSTOMER.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




a. In the Designer, open the Projects tab, and select the Variables node in the HandsOnLoads project. Right-click this node and then select New Variable.

b. Enter DELETION_FLAG in the Name field, select Numeric as the Datatype, and enter

0 in the Default Value field. In the Keep History field, select All Values to enable viewing the history of all values for this variable.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. Click the Refreshing tab. Select ORACLE_ORCL_LOCAL_SALES as the schema, and in the Select Query field, enter the following expression. Click the Save button to save the variable. Close the DELETION_FLAG variable tab.

Note: To enter commands, refer to the file 14-2.txt provided at the following location: c:\Labs\Text.

SELECT COUNT(*) FROM <%=odiRef.getObjectName("L", "TRG_CUSTOMER", "ORACLE_ORCL_LOCAL_SALES", "", "D") %>

5. Modify a package to have the Delete Target step running only if DELETION_FLAG is

greater than 0.

a. Duplicate the PKG_14-1 package. Open the package Copy of PKG_14-1 and rename it PKG_14-2.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Select the Diagram tab. In the project tree, expand the Variables node. Drag the

DELETION_FLAG variable from the tree view to the diagram. Click the Deletion_FLAG step in the diagram. In the Properties > General tab beneath the diagram, rename this step Refresh Customer Count. Ensure that the Type field is set to the Refresh variable. Note: You need to click the step in the diagram to view the new step name.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. Drag the DELETION_FLAG variable from the tree view to the diagram again. Rename this step Flag > 0?, change the Type to Evaluate Variable. In the Operator field, select “>” as the operator, and enter 0 in the Value field.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Right-click the Refresh Customer Count step and select First Step. Click the

“Next step on success” tool on the Package toolbar, and link the step Refresh Customer Count to the step Flag >0? Using the same tool, link the step Flag >

0? to the step Delete Target tables. Click the “Next step on failure” tool on the Package toolbar, and link the step Flag > 0? to the Countries step, as shown below. Click Save to save the package. Close the tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




6. Run the new package and check if the Delete Target Tables step was executed.

a. Right-click the package PKG_14-2, and then select Execute. Click OK in the Execution window, and then click OK when the “Session started” message appears.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. In the Operator, verify that the latest session called PKG_14-2 appears as complete, with a Delete Target Tables step executed.

c. Open the Designer tab. Open the package PKG_14-2 again, and then select the

Diagram tab. To delete records from the target tables, select the Delete Target Tables step, right-click, and select Execute Step. Click OK in the Execution window, and then click OK when the “Session started” message appears.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. In the Operator, verify that the latest session called PKG_14-2 appears as complete,

with a Delete Target Tables step executed.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. Now, with records from the target tables having been deleted, execute the entire package PKG_14-2 again. Click OK in the Execution window, and then click OK when the “Session started” message appears.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Open the Operator and expand the latest session called INT_14-2. Verify that the Delete Target Tables step is no longer executed. Note: Because the Customers tables were emptied by the execution of the Delete Target Tables step, the value of variable Flag >0? changes when refreshed, to “false”, and the Delete Target Tables step is no longer executed. Close the PKG_14-2 tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 15: Managing ODI Scenarios

Chapter 15 - Page 1


Chapter 15

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 2


Practices Overview In this practice, you create an ODI scenario and schedule its execution with an ODI agent in scheduling mode.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 3

Practice 15-1: Creating and Scheduling an ODI Scenario

Use Case Scenario: Earlier, John created the ODI procedure to create a relational table and populate it with data. Now John needs to create an ODI scenario and schedule its execution.

Background: A common task that is performed by using ODI is to create an ODI scenario and schedule its execution. You already developed the ODI procedure that creates a relational table and populates the relational table with data.

In this practice, you learn how to create and schedule an ODI scenario to run the procedure you created in Practice 12-1.

First, in the Designer Navigator’s Projects tab, you generate a scenario for the PRD-create-populate-table procedure.

Then, in Topology Navigator, you verify connection to ODI Agent.

Next, in Designer Navigator, you schedule the scenario with ODI Agent.

You switch back to Topology Navigator to review the scheduling of the agent.

Finally, in Operator Navigator, after the scheduled scenario execution time, you verify that the scenario has executed.

Your Tasks: 1. Create a new scenario with Oracle Data Integrator.

a. If not connected, connect to the Work Repository WORKREP. Select DEV_ODI_REPO from the Login Name drop-down list. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to log in.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 4

b. In ODI Designer, click the Projects tab. On the Projects tab, expand the project:

Procedure-CRT-TBL > First Folder > Procedures. Right-click the PRD-create-populate-table procedure and select Generate Scenario.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 5

c. Accept the default scenario name PRD_CREATE_POPULATE_TABLE. The version is set to 001. Click OK.

d. Expand the PRD-create-populate-table procedure and expand Scenarios. Right-click

the PRD_CREATE_POPULATE_TABLE scenario to view the possible options, as shown in the following screenshot. Note: The scenario has now been successfully created. You can now execute the scenario directly, use the scenario within a package, or schedule the scenario within ODI.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 6

2. Now, you need to schedule an ODI scenario with ODI Agent. To schedule the scenario, perform the following steps:

a. In Topology Navigator, verify connection to ODI agent, as shown in the following screen. Note: If ODI Agent is not set up and running, you must perform the steps specified in Practice 3-1 before continuing.

b. Expand the PRD-create-populate-table procedure. Expand Scenarios > PRD_CREATE_POPULATE_TABLE Version 001. Right-click Scheduling and select New Scheduling.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 7

Note: To schedule a scenario, an ODI Agent must be set up. If an ODI Agent is not set up within the ODI Topology Navigator, perform Practice 3-1, “Setting Up and Installing an ODI Agent.”

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 8

c. On the screen that follows, select the agent where the scheduled scenario will run: localagent. Set Context as Development and log level to 5. Set Execution to Simple

and click the button. Set the execution time to approximately five minutes from the current system time, as shown in the following screenshot. Click the Save button.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 15 - Page 9

d. Expand Scheduling and verify that the DEVELOPMENT / localagent entry is now

inserted under Scheduling.

e. Open Topology Navigator to review the scheduling of the Agent. In the Physical

architecture, expand the Agents node and double-click localagent. On the localagent screen, click Update Schedule. On the screen that follows, click OK. Click OK again.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Click the View Schedule button. The screen that appears shows you the scheduling

information. Click OK to close the schedule. Close the tabs: Localagent tab and Scenario Scheduling.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. To verify the execution of the scheduled scenario, perform the following steps:

a. Click the Operator tab to open ODI Operator. In ODI Operator, click the Session List tab. Wait until the scheduled execution time to view the execution results, and then click the Refresh icon.

b. Expand: Agent > localagent > PRD_CREATE_POPULATE_TABLE, and view the

execution results for the PRD-create-populate-table procedure. Note the execution time of this scenario. That is the time that you scheduled with ODI Agent. You have now successfully scheduled a scenario with an agent scheduler.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 16: Using Load Plans

Chapter 16 - Page 1


Chapter 16

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 2


Practices Overview In this practice, you will use the load plan editor to create a simple load plan with a set of parallel and serial steps.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 3

Practice 16-1: Using Load Plans

Use Case Scenario: Earlier, John created the ODI procedure to create a relational table and populate it with data. In the last practice, John created an ODI scenario to run the procedure.

In this practice, John will use the load plan editor to create a simple load plan with a set of parallel and serial steps.

Background: When ODI is used to populate very large data warehouses, it is common to have thousands of tables being populated by using hundreds of scenarios. The execution of these scenarios has to be organized in such a way that the data throughput from the sources to the target is the most efficient within the batch window. Load Plans help organize the execution of scenarios in a hierarchy of sequential and parallel steps for these types of use cases.

Load Plans are objects that organize the execution of packages and scenarios at a high level. Load Plans provide features for parallel, sequential, and conditional scenario execution, restartability, and exception handling.

In this practice, you will use the load plan editor to create a very simple load plan with a set of steps running in parallel, followed by a set of steps running sequentially.

First, define a load plan.

Then, add a node for independent steps to run in parallel. Create two procedures for loading dimensions, and add them as steps to execute in parallel.

Next, add a node for interdependent steps to run in a specific sequence. Add several of your previously defined objects as steps to run in the following order:

1. DELETE_TARGET procedure, which deletes records from the TRG_SALES, TRG_CUSTOMER, TRG_CITY, TRG_REGION, and TRG_COUNTRY tables.

2. INT_8-1, which adds records to the TRG_COUNTRY table.

3. INT_8-2, which adds records to the TRG_REGION table.

4. INT_8-3, which adds records to the TRG_CITY table.

5. INT_9-1, which adds records to the TRG_CUSTOMER table. Note: These five steps are the same set of operations you used in creating the PKG_14-1 package.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 4

Your Tasks 1. Create a simple load plan to execute two steps in parallel, and then five steps serially.

a. In the Designer Navigator, click the Load Plans and Scenarios tab. From the tab’s menu, select New Load Plan.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 5

b. Enter Data Warehouse Load Plan in the Name field, and click the Steps tab.

c. The load plan editor opens with the root step node. Click the Add Step button, and

select Parallel Step.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 6

d. The Parallel step node appears under the root step. You will define two new procedures and place them under the parallel node so they will execute in parallel. Note that the default restart type for parallel steps is Restart all children, and the restart type for root_step is Restart from failure. Click Save to save your load plan.

2. Define two procedures to load PRODUCT and TIME dimensions, and add them to the load

plan Parallel step node.

a. In the Projects tab of the Designer navigator, expand the HandsOnLoads > HandsOn folder. Right-click the Procedures node and select the New Procedure option.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 7

b. Enter Load_PRODUCT_dimension in the name field. Do not enter any other information, because this will be a nonfunctioning skeletal procedure that you create solely for the purpose of seeing how load plans work. Later, when you execute the load plan, this procedure will run, but do nothing. Click Save and close the Load_PRODUCT_dimension editing tab.

c. Repeat the previous step to create the Load_TIME_dimension procedure. Click

Save and close the Load_TIME_dimension editing tab.

d. Return to the Data Warehouse Load Plan editor. With the Parallel step highlighted,

click the Add Step button and select Run Scenario Step.

e. A load plan uses scenarios in its steps. You can select an existing scenario for a step,

or choose other objects (packages, interfaces, variables, and procedures), for which ODI will automatically generate a scenario to run the step.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 8

You now add the two procedures you just defined as steps under the Parallel node of the load plan. In the Add Run Scenario Step screen, click the Lookup Scenario button.

f. In the Executable Object Type field, select Procedure. Select

Load_PRODUCT_dimension. Click OK.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 16 - Page 9

g. In the New Scenario window, accept the name LOAD_PRODUCT_DIMENSION as the

name of the scenario that ODI will create to run this procedure, and click OK.

h. ODI returns to the Add Run Scenario Step window.

Note that LOAD_PRODUCT_DIMENSION is the name of the new scenario that ODI will generate for the procedure, as well as the name for the step you are adding to the load plan. Click Finish.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




i. The first of your two parallel steps appears. Click the Parallel node of the Steps Hierarchy, and click the Add Step button again. Select Run Scenario Step.

j. Repeat steps d. through g. to add your other new procedure, Load_TIME_dimension,

to the Parallel node of the Steps Hierarchy. The result should look like the following screenshot.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. Add ODI objects to the load plan as serial steps. These are the same objects you used earlier to create the PKG_14-1 package. a. Click root_step in the Steps Hierarchy. Click the Add Step button and select Serial

Step.

b. The Serial node appears in the Steps Hierarchy. With Serial node selected, Click the

Add Step button and select Run Scenario Step.

c. In the Add Run Scenario Step window, click Lookup Scenario .

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Select Procedure as the executable object type. Select DELETE_TARGET. Click OK. In the New Scenario window, accept the name DELETE_TARGET as the name of the scenario that ODI will create to run this procedure, and click OK.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. ODI returns to the Add Run Scenario Step window. Click Finish.

f. The first of your five serial steps appears. Click the Serial node of the Steps Hierarchy,

and click the Add Step button again. Select Run Scenario Step.

g. Click Lookup Scenario and select Interface.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




h. You will add four interfaces to the Serial node: INT_8-1, INT_8-2, INT_8-3, INT_9-1. First, select INT_8-1 and click OK. In the New Scenario window, accept the name INT_8-1 as the name of the scenario that ODI will create to run this interface, and click OK. ODI returns to the Add Run Scenario Step window. Click Finish.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




i. Repeat steps f. through h. to add the remaining three interfaces: INT_8-2, INT_8-3, and INT_9-1. Your results should look like the following screen. Note the default restart values for the different types of steps. Click Save to save your load plan.

4. Now, execute the load plan and examine the list of executed steps in the Operator

Navigator.

a. Click to execute the Load plan. Click OK in the Start Load Plan window, and then click OK when the “Load Plan started” message appears.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Open the Operator Navigator, click Refresh, and examine the results of executing the

load plan. The seven steps in the load plan appear as a grouping of seven executions. For each load plan step, ODI generated a scenario for execution.

First, the procedures for loading the TIME and PRODUCT dimensions executed in parallel. Then, the DELETE_TARGET procedure and the four interfaces executed in the sequence you defined.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 17: Managing ODI Versions

Chapter 17 - Page 1


Chapter 17

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 2


Practices Overview In this practice, you create multiple versions of individual ODI objects.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 3

Practice 17-1: Working with ODI Versions

Use Case Scenario: In addition to the group of Interfaces created earlier, John needs to create a project and an interface to export data from a flat file and load this data into a relational table. He also needs to verify the quality of the data loaded in the table.

Background: In the previous practices, you learned how to create ODI Interfaces, which transform data from one or more relational tables in the source to a relational table in the target.

In this practice, you take an interface and create a first version of it, version 1.0.0.0. Next, you change some details of the interface’s mapping and create a second version of the interface, version 1.0.0.1.

Then you use the ODI Version Browser and Version Comparison tool to compare these two versions of the interface and to detect the differences between them.

Finally, you generate a PDF report of the versions, and then restore the original version.

1. Create an interface.

2. Create a new version 1.0.0.0 of the interface.

3. Change the interface flow and create another version of the interface, version 1.0.0.1.

4. Use the Version Browser to compare versions of the interface and create a version comparison report.

Note: Completing this practice is critical for all the following practice sessions.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 4

Your Tasks:

1. Create a new Interface with Oracle Data Integrator.

a. If not connected, connect to the ODI Work Repository DEV_ODI_REPO. Log in with the following details. Click OK.

• Login name: DEV_ODI_REPO

• User: SUPERVISOR

• Password: SUNOPSIS.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 5

b. In Designer navigator, click the Projects tab. Expand HandsOnLoads > HandsOn > Interfaces, and select INT_9-1. Right-click this interface and select Duplicate Selection. In the Confirmation window, click Yes.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 6

c. Double-click Copy of INT_9-1. Name the new interface INT_17-1. Click Save to save the interface. Close the INT_17-1 tab.

2. Create new versions of INT_17-1.

a. To create a new version of INT_17-1: Right-click INT_17-1 and select Version > Create Version. On the screen that follows, view the suggested version number. Leave this default number. Click OK.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 7

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 8

b. Double-click INT_17-1 to edit it. Click the Flow tab. On the flow tab, click Target Datastore. Select the Distinct Rows check box. Click the Save button and close your interface tab.

c. To create another version of this modified Interface, right-click INT_17-1 and select

Version > Create Version.

On the screen that follows, note the previous (original) version of your interface.

Click the Hide/Unhide Previous Versions button to hide and then reshow your original version.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 17 - Page 9

Optionally, enter the description of the new version in the Description field. Click OK to save your new version.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Now, you explore your versions by using the Version Browser. Click ODI > Version

Browser. View your versions with all the necessary information.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. Compare the newly created version. a. With Version Browser open, select both versions: (1.0.0.0) and (1.0.0.1) for interface

INT_17-1. Right-click the selected versions and select Compare.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Select the two Modified filters and deselect all other check boxes. Now you have a

more compact view of your changes. Click the Print button.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. On the Report generation screen, select the New objects check box. Click the button to specify the PDF file location.

d. Specify the PDF file location: C:\Documents and Settings\Administrator\My

Documents\. Name your report: Practice 17-1 Interfaces comparison. Click Save. Your report will be saved in C:\Documents and Settings\Administrator\My Documents\Practice 17-1 Interfaces comparison. Click Generate. Click Close.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. Use Windows Explorer to open the PDF file with your report. Scroll down to view the report. Note the different values for Distinct Rows in the two versions of the interface. Close the report.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




4. Restore the original version of your interface. a. In the Version Browser, select INT_17-1 version 1.0.0.0, and click the Restore this

version icon. Click Yes. Click OK. Close the Version Browser.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. In the Designer Navigator, double-click the INT_17-1 interface. Click the Version tab. View your current restored version: 1.0.0.0. Close the INT_17-1 editor.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 18: Enforcing Data Quality with ODI

Chapter 18 - Page 1


Chapter 18

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 2


Practices Overview In this practice, you enforce data quality with an interface that you created in Practice 10-1, and you perform error management.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 3

Practice 18-1: Enforcing Data Quality with ODI Interface

Use Case Scenario John is responsible for data loading, transformation, and validation. He created a project and an interface to export data from a flat file and load this data into a relational table. Now John needs to verify the quality of data loaded in the table.

Background In the previous practices, you learned how to create ODI Interfaces, which transform data from one or more relational tables in the source to a relational table in the target and to export data from a flat file to a relational table. In this practice, you perform data quality control for the interface created in Practice 10-1.

First, for a certain datastore, you verify that the primary key constraint is marked static, and two columns are marked mandatory with static control.

You then create an interface by using that datastore as a target.

On the interface’s Flow tab, you set STATIC_CONTROL to True. On the Controls tab, you ensure that the knowledge module is set to CKM SQL (a knowledge module that you will import) and you set maximum allowed errors to 5.

For two columns in the target datastore, you select the Check Not Null check box so that control errors are generated if these columns are not loaded.

You run the interface with Flow Control set to FALSE. You verify that there are control errors on three records, and that the three records with errors were loaded into the target datastore.

Finally, you rerun the interface with Flow Control set to TRUE and verify that errors are excluded from the target datastore.

Note: Completing this practice is critical for all the following practice sessions.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 4

Your Tasks

1. If not connected, connect to the Work Repository, WORKREP. To connect, click Connect To Repository. Select DEV_ODI_REPO from the Login Name drop-down list. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.

2. Modify INT-Exp-FF-RT (created in Practice 10-1) to enable checking data quality in the

target table. a. On the Projects tab, expand the Export-FF-RT project. Select Knowledge Modules >

Check (CKM). Right-click Check (CKM) and select Import Knowledge Modules.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 5

b. In the File import directory field, select CKM SQL and click OK. Close the Import Report window. Verify that CKM SQL was successfully imported and is shown in the tree view.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 6

c. On the Models tab, expand Oracle_RDBMS1 > TRG_SALES_PERSON >

Constraints, and then double-click TRG_SALES_PERSON_PK. Click the Controls tab and verify that Static is selected.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 7

d. In the model Oracle_RDBMS1, expand TRG_SALES_PERSON > Columns, double-click the column LAST_NAME, and then click the Controls tab. Select Static to enable static control. Select the Mandatory check box. Click the Save button. Repeat the same step to enable Static control and Mandatory for the DATE_HIRED column. Save your changes and close the tabs.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 8

e. On the Projects tab, expand the Export-FF-RT project, and then duplicate the interface INT-EXP-FF-RT. Open the copy of the interface and rename it as INT-EXP-FF-RT- Quality, and then click the Mapping tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 18 - Page 9

f. Delete the filter for the column SALES_PERSON_ID, as shown in the following screenshot, and then click the Flow tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. On the Flow tab, click Target (ODI_STAGE) to open the Options section. Set the static control option to true. Click the Controls tab.

h. On the Controls tab, ensure that your KM is set to CKM SQL. Set the maximum

number of errors allowed to 5. Leave the other options with the default values. Click the Mapping tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




i. In Target Datastore, select the LAST_NAME column, and then select the Check Not Null check box. Repeat the same step for the DATE_HIRED column. Click the Save button to save your changes.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




j. Execute your interface . Click the Execution tab of the INT-EXP-FF-RT-QUALITY editor to view the execution results showing three errors, as shown in the following screenshot.

Note: You may need to click the Refresh icon to view your last execution.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




k. On the Models tab, right-click the TRG_SALES_PERSON target datastore and select

Control > Errors. View the records with errors as shown in the following screenshot:

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




l. In Designer Navigator, right-click the TRG_SALES_PERSON datastore and select View Data. Verify that the three rows with errors are still inserted in the target datastore. Close the TRG_SALES_PERSON data tabs.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




m. Click the INT-EXP-FF-RT-Quality tab. On the Interface screen, click the Flow tab. Click Target (ODI_STAGE) to open the Options section. Set Flow Control to true.

Save your changes, and then execute the interface .

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




n. On the Models tab of Designer Navigator, right-click TRG_SALES_PERSON and select View Data. Verify that the rows with errors are excluded from the data in the target datastore. Close the tabs.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 19: Working with Changed Data Capture

Chapter 19 - Page 1


Chapter 19

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 2


Practices Overview In this practice, you implement Changed Data Capture to detect changes in the source environment.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 3

Practice 19-1: Implementing Changed Data Capture

Use Case Scenario As a database administrator, John is responsible for data loading, transformation, and validation. He wants to be able to detect changes in his source environment. For that purpose, he needs to implement Changed Data Capture in his data integration project.

Background A common task that is performed by using ODI is to implement Changed Data Capture. In this practice, students implement Changed Data Capture to detect changes in the source environment for the interface that was created in Practice 8-1. You will capture data changes in the SRC_CITY table in the Oracle Sales Application model. Then you modify the interface to process changed records only. In this practice, you perform changed data capture in an interface, using the JKM Oracle Simple knowledge module.

First, you import this knowledge module and create a model by using this journalizing knowledge module. Then you create an interface by using the IKM Oracle Incremental Update knowledge module, specifying capture of journalized (changed) data only. 1. Import the JKM Oracle Simple knowledge module.

2. Create a model, Oracle_RDBMS2, specifying this knowledge module on the Journalizing tab.

3. Reverse-engineer the model, verifying the resulting structure.

4. Add the model’s SRC_SALES_PERSON table to CDC and start the Journal by using the default subscriber SUNOPSIS.

5. Use Data Viewer to change data in the table and verify in Journal Data that the data change was captured.

6. Create an interface to process only changed data.

Your Tasks

1. Import the appropriate JKM in the project.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 4

a. If it is not open, log in to the WORKREP Work Repository with the login name DEV_ODI_REPO (enter SUPERVISOR for username and SUNOPSIS for password).

b. In Designer Navigator, click the Projects tab. Expand the Procedure-CRT-TBL

project. Expand the Knowledge Modules node, right-click Journalization (JKM), and select Import Knowledge Modules.

c. Select JKM Oracle Simple to import and click OK. Close Import Report.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 5

d. On the Models tab, create a new model named Oracle _RDBMS2. For Technology,

enter Oracle. Select the logical schema ODI_STAGE. Click the Reverse Engineer tab and set Context to Development. Verify the setting, as shown in the following screenshot. Click the Journalizing tab.

Note: You created the SRC_SALES_PERSON table in the ODI_STAGE schema in Practice 13-1.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 6

e. In the Knowledge Module menu, confirm that the JKM Oracle Simple.Procedure-

CRT-TBL knowledge module is selected, as shown in the following screenshot. Click

the Save icon to save your model, and then close the tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 7

From the Models tab of Designer Navigator, reverse-engineer the Oracle_RDBMS2 model (not Oracle_RDBMS1). Expand this model and verify its structure as follows:

2. Set up the CDC Infrastructure. You will start the CDC on the SRC_SALES_PERSON table in the Oracle_RDBMS2 model.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 8

a. To add the table to CDC, expand the Oracle_RDBMS2 model, right-click the SRC_SALES_PERSON table, and select Changed Data Capture > Add to CDC. Click Yes to confirm.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 19 - Page 9

b. Click the Refresh icon. The small yellow clock icon is added to the table

. Right-click the SRC_SALES_PERSON table again and select Changed Data Capture > Start Journal.

c. In this practice, you use the default subscriber SUNOPSIS. Consequently, you do not

have to add another subscriber. Click OK to confirm that your subscriber is SUNOPSIS. In the Execution window, click OK. In the Information window, click OK

again. Wait several seconds, click Refresh , and then verify that the tiny clock icon

on the SRC_SALES_PERSON table is green now . This means that your journal has started properly.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Open Operator Navigator. Click Refresh. Select All Executions and verify that the

SRC_SALES_PERSON session executed successfully.

3. View the data, change some data, and confirm that the changed data is captured.

a. In Designer Navigator, click the Models tab. Right-click the SRC_SALES_PERSON datastore and select Data.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Select the row with SALES_PERSON_ID = 11. In the LAST_NAME column, double-click Andersen to edit that cell. Change the value from Andersen to Anderson. Similarly select the row with SALES_PERSON_ID = 51 and change the value to Smith. Click the

Save changes icon and close the tab.

c. Right-click the SRC_SALES_PERSON table again and select View Data. Scroll down

and verify that the rows are modified. Close the tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. To verify that your changed data is captured, right-click SRC_SALES_PERSON and select Changed Data Capture > Journal Data. Find the captured changed records in the journal data. Close the tab.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




4. Create an interface that processes captured data changes. The interface loads the TRG_SALES_PERSON_CDC datastore in the Oracle_RDBMS1 model with the content of the SRC_SALES_PERSON table from the Oracle_RDBMS2 model. The purpose of this interface is to process and load only changed data.

a. First you need to add one more knowledge module. In ODI Designer, click the Projects tab, and then expand the Export-FF-RT project. Expand the Knowledge Modules node. Right-click Integration (IKM) and select Import Knowledge Modules. Import the new Knowledge Module: IKM Oracle Incremental Update. Close the report.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Now you need to create one more target datastore to store only changed data. On the

Models tab, expand Oracle_RDBMS1, duplicate the TRG_SALES_PERSON datastore, and rename the copy as TRG_SALES_PERSON_CDC. Save the TRG_SALES_PERSON_CDC datastore, and then close the tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. Open Data in the TRG_SALES_PERSON_CDC datastore, and then click the Delete

Current Row icon repeatedly to delete all the rows. Save your changes and close the tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. On the Projects tab, expand Export-FF-RT > First Folder > Interfaces. Right-click

Interfaces and select New Interface.

e. In the Interface: NEW window, enter INT_SALES_PERSON_CDC as the name of the

interface. Ensure that the Optimization Context is Development. Click the Mapping tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Click the Models tab. In the tree view, expand the Oracle_RDBMS1 model. Drag the TRG_SALES_PERSON_CDC datastore from the tree view to the Target Datastore zone. Expand the Oracle_RDBMS2 model and drag the SRC_SALES_PERSON datastore from the model tree to the Sources zone of your diagram. Click Yes to perform automatic mapping. Save your interface.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. Right-click the caption of the source datastore and select Data. Scroll down to preview

the current source data. Close the Data Editor window.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




h. Click the Flow tab, click the Staging Area caption, and verify that the selected IKM is IKM Oracle Incremental Update. Select Distinct Rows.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




i. Now you modify this interface to process only the changed data. Click the Mapping tab. Click the source datastore. In the Source Properties section of the Property Inspector, select the Journalized Data only check box.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




j. To check the new source data set, repeat step g. View your data again. Only the changed data should be visible. Close the Data Editor window.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




k. Save your interface and close the tab. On the Projects tab, execute the interface to process only journalized records. In the Execute and Information windows, click OK.

5. Verify the execution results.

a. Open Operator Navigator. Click Refresh. Expand the All Executions node and verify that your session, INT_SALES_PERSON_CDC, executed successfully.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. View the data in the TRG-SALES_PERSON_CDC datastore and verify that only the two changed rows are inserted in the target datastore.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




6. On the Models tab, in the Oracle_RDBMS2 model, right-click the SRC_SALES_PERSON datastore and select Drop Journal. Click OK in the Execution window. Click OK in the Information window. Right-click the SRC_SALES_PERSON datastore again, and then select Remove from CDC. Click Yes to confirm removing the datastore from CDC.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 20: Advanced ODI Administration

Chapter 20 - Page 1


Chapter 20

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 2


Practices Overview In Practice 20-1, you create a new user profile, assign authorization by the profile to objects, and define a password policy. In Practice 20-2, you deploy Enterprise Manager and ODI Enterprise Manager Plug-in to an existing WLS domain with the deployed and configured ODI Java EE. You then use the Enterprise Manager and ODI Console to monitor and manage ODI components, processes, and resources.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 3

Practice 20-1: Setting Up ODI Security

Use Case Scenario As a database administrator at FN Enterprise, John is responsible for performing database management and integration tasks on various resources within the organization. John is also responsible for enforcing the ODI security policies. To secure this ODI environment, he needs to set up the security with ODI.

Background Using the ODI Security Manager module, you can manage security in Oracle Data Integrator.

In this practice, you explore the security features of ODI Security Manager. You create a profile, grant three methods (actions) to the profile, and create a new ODI user to whom you assign that profile.

You then assign the profile to your new user and grant a specific method (View) for a specific object (Scheduling) to your new user. You also define user parameters that enable specific functionality for your new user.

Finally, you define password policies that control the case-sensitivity and length of user-defined passwords. 1. In ODI Security Navigator, expand the Profiles panel and duplicate the Repository

Explorer profile, naming the new profile REVIEWER. You grant three methods to the REVIEWER profile: “Compare with version,” “Duplicate,” and “Export from object Model.”

Note: You may need to click the View menu and open Security Navigator.

2. Create a new ODI user, INT_PROJECT_REVIEWER.

3. Assign the new profile REVIEWER to your newly created user. To connect to ODI Master and Work Repositories, assign the built-in profile, CONNECT, to the new user INT_PROJECT_REVIEWER.

4. Grant the View method for the Scheduling object to the INT_PROJECT_REVIEWER user.

5. Define user parameters to enable specific functionality for INT_PROJECT_REVIEWER.

6. Define password policies to enforce passwords to be uppercase and greater than eight characters.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 4

Your Tasks

1. To define an ODI security policy, you need to create an appropriate profile for the working methods used in your project.

a. Start Oracle Data Integrator and connect to the Master Repository. For Login Name, enter DEV_ODI_REPO. Enter SUPERVISOR for user and SUNOPSIS for password.

b. From the View menu, open ODI Security Navigator. Click the Profiles tab. Right-click the generic REPOSITORY EXPLORER profile and select Duplicate Selection. Click Yes to confirm.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 5

c. Double-click Copy of REPOSITORY EXPLORER to edit.

d. Rename your new profile REVIEWER. Click the Save button to save this profile. The

profile REVIEWER appears in the Profiles tree structure.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 6

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 7

2. Assign the appropriate authorizations to REVIEWER.

a. In Security Navigator, click the Objects tab. On the Profiles tab, expand REVIEWER > Model, as shown in the following screenshot. Only the View method is permitted for Model now.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 8

b. On the Objects tab, expand Model and select the Compare with Version method. Drag the Compare with Version method to the REVIEWER profile. In the Confirmation window, click Yes.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 20 - Page 9

c. Repeat step b to add the methods “Duplicate” and “Export...” to the REVIEWER profile. In Profiles, expand REVIEWER > Model, and verify that the methods “Compare with version,” “Duplicate,” and “Export...” are added to the Model object, as shown in the following screenshot. Save the REVIEWER profile and close the tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. Create a new ODI user. a. In Security Navigator, click the Users tab and select New User. The New User window

opens.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Name the new user: INT_PROJECT_REVIEWER. Click Enter a password.

c. Enter the password (INTEGRATION), and then confirm this password. Do not select the

“Allow expiration date” check box. Click OK. Click the Save button to save this new user. The INT_PROJECT_REVIEWER user is now displayed in the Users tree structure.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




4. Assign the profile REVIEWER to the INT_PROJECT_REVIEWER user.

a. Make sure that both the Profiles and Users panels are displayed together in ODI Security Navigator. Click the profile REVIEWER in the Profiles panel and drag it to the INT_PROJECT_REVIEWER user in the Users panel. Click Yes to confirm granting the profile.

b. In the Users panel, expand the INT_PROJECT REVIEWER > Profiles node and verify

that the profile REVIEWER was assigned to this user.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. To connect to ODI Master and Work Repositories and other objects, assign the built-in profile CONNECT to your new user. Select the CONNECT profile and drag it to the INT_PROJECT_REVIEWER user. Click Yes to confirm granting CONNECT to your new user.

d. Verify that the profiles CONNECT and REVIEWER are added to your new user:

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. You can also grant privileges to use certain methods for objects directly to your user. Ensure that the Objects and Users panels are open. In the Objects panel, scroll down, and then expand the object Scheduling. Select View. Drag this method to INT_PROJECT_REVIEWER.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Verify that authorization to the Scheduling object with the View method was added to the INT_PROJECT_REVIEWER user, as shown in the following screenshot:

5. Connect your new user to the ODI Work Repository and verify your access to ODI artifacts.

a. From the ODI menu, select Disconnect “DEV_ODI_REPO”. Connect to your Work Repository as the newly created user. Select DEV_ODI_REPO for the Login Name. Enter INT_PROJECT_REVIEWER as the user and INTEGRATION as the password.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. On the Designer tab, click the Projects tab and Models tab. Expand your projects and models to verify that you have access to view all your artifacts.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




6. Define user parameters so that the user can enable specific ODI functionality. Ensure that a help message is displayed if an interface diagram is empty and objects remain unlocked when they are opened. In addition, you may want to set a delay between two refreshes in seconds and the number of elements displayed in a branch of the Operator tree view. a. In ODI Security Manager, select ODI > User Parameters.

b. Confirm or edit the user parameters as shown in the following table. Click OK. Click the

ODI menu and select Disconnect DEV_ODI_REPO to close this user connection.

Parameter Value Functionality

Help for Interface Diagram 1 Display a Help message when the Interface

Diagram is empty

Lock object when opening 0 Lock object when it is opened

Delay between two refresh (seconds)

2 Inter-refresh delay

Operator Display limit (0=no limit)

200 Number of elements displayed in a branch of the Operator tree view

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




7. Open your connection with the SUPERVISOR user. Select Security Navigator. Define

Password Policies. These policies consist of a set of rules that will be checked when the password is defined by the user.

a. Connect to DEV_ODI_REPO with the SUPERVISOR user. For the password, enter

SUNOPSIS. Open Security Navigator. Click the icon, and then select Password policy. Click the Add a Policy icon.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Name this policy Policy 1. Enter the rule description as shown in the following

screenshot. Set “Conditions to match” to All. In the Rules section, click Text and select is uppercase. Click the add rule button (the + sign).

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. Enter another rule: Length >= 8. Click OK.

d. On the screen that follows, select the Active check box. Select the Passwords are

valid for check box and set it to 30 days. Click OK to save this Password policy. Click OK again. Close the Security Navigator tab.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Practice 20-2: Integration with Enterprise Manager and Using ODI Console

Use Case Scenario Oracle Data Integrator provides an extension integrated into the Fusion Middleware Control Console (Enterprise Manager). The Oracle Data Integrator components can be monitored as a domain through this console, and administrators can have a global view of these components, along with other Fusion Middleware components from a single administration console. John decided to begin using this feature. He created the WebLogic Server (WLS) domain, ODI-DOMAIN. Now John needs to deploy and configure the ODI Java EE Agent with the existing WLS domain. He also needs to configure the ODI Enterprise Manager and ODI Console Plug-in with WLS.

Background In this practice, you perform the steps needed to deploy and configure the ODI Java EE Agent, Enterprise Manager 11g, and ODI Console plug-in on an existing WLS domain. You use Enterprise Manager 11g and ODI Console to access your ODI objects.

1. Deploy and configure the ODI Java EE agent, Enterprise Manager, and ODI Console on the existing WebLogic domain.

2. Connect to the WebLogic Server and managed server, odi_server1.

3. Use Enterprise Manager to administer ODI.

4. Use ODI Console to explore the ODI environment.

Your Tasks 1. Deploy and configure the ODI Java EE agent and Enterprise Manager on the existing

WebLogic domain, ODI-DOMAIN.

Note: This domain was pre-created for this practice.

a. In Topology Navigator, open Physical Architecture and then create a new ODI Agent. Name this agent OracleDIAgent. Set the port to 8002 and Host to localhost. Verify that the Web application context is set to oraclediagent, as shown in the following screenshot. Save your new physical agent.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Note: The agent name is case-sensitive. Use the exact name OracleDIAgent, as shown in the following screenshot:

b. In the Logical Architecture, create a new logical agent with the same name as your

new physical agent, OracleDIAgent. Link this logical agent to the physical agent OracleDIAgent in all three contexts, as follows. Click Save to save your agent definition.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




2. Extend the WebLogic server domain ODI-DOMAIN with the ODI agent application.

a. To deploy and configure domains with WLS, you start the config.bat file from the ODI Home Install directory – C:\Oracle\Middleware\Oracle_ODI1\common\bin. Use Start > Run > CMD to start the command shell and change directory to C:\Oracle\Middleware\Oracle_ODI1\common\bin. Execute the command config, which starts the Configuration Wizard.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Select Extend an Existing WebLogic domain and click Next.

c. Select the WebLogic domain directory ODI-DOMAIN, as shown in the following

screenshot. Click Next.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. In the screen that follows, select Oracle Data Integrator – Agent – 11.1.1.0 (Oracle Oracle Data Integrator – Agent Libraries – 11.1.1.0 and Oracle JRF – 11.1.1.0 will also be selected; leave these options selected).

Select the Oracle Enterprise Manager Plugin For ODI – 11.1.1.0, which will also include the Oracle Enterprise Manager – 11.1.1.0.

Also select Oracle Data Integrator – Console - 11.1.1.0, which will include the Oracle Data Integrator SDK Shared Library Template – 11.1.1.0. Click Next.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. On the next screen, accept the default application location. Click Next.

f. The wizard will set up the ODI repository connection within the domain server. During

the wizard configuration, the repository connection information must be supplied. Select the check boxes next to ODI Master Schema and ODI Work Schema. This will cause most of the fields to be filled with relevant values.

Change the Host Name field on the upper-right to localhost. This will automatically add localhost to the other two Host Name fields at the bottom of the screen. Fill the Schema Password field below the Schema Owner field with oracle1. This will automatically add the password to the two other password fields on the lower-right corner of the screen.

Check the values filled in by the wizard, and the values you entered, against the following table and screen. Click Next.

Database Connection (Master Repository)

Parameter Value

Vendor Oracle

Driver Oracle’s Driver (Thin) for Service connections

Schema Owner DEV_ODI_REPO

Schema Password oracle1

DBMS/Service orcl

Host Name Localhost

Port 1521

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




g. Verify that the test was successful (if not, correct any connectivity settings). Click Next.

h. On the next screen, select Managed Servers, Clusters and Machines. Click Next.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




i. The Configure Managed Servers screen shows two managed servers: odi_server_1 and odi_server1. odi_server1 has been predefined specifically for this practice, so use the Delete button to remove the first entry in the list, odi_server_1.

j. Set Listen port to 8002 for odi_server1. This matches the port setting that you used

when defining the OracleDIAgent physical agent. Click Next.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




k. On the Configure Clusters screen, click Next.

l. On the Configure Machines screen, click Next.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




m. The Assign Servers to Machines screen will already have odi_server1 assigned to LocalODIMachine. Click Next.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




n. Review the Configuration Summary, and then click Extend. When the domain is created, click Done.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. Connect to the WebLogic server and the managed server, odi_server1.

a. In the command shell, change directory to the directory of the WLS Home: C:\oracle\Middleware\user_projects\domains\ODI-DOMAIN\bin. Execute the startweblogic command. It will take approximately one minute to initialize the server. Finally the following message is displayed: Server started in RUNNING mode.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Minimize (but do not close) this Admin server command window.

b. Security must be set up for the Java EE application to have access to the ODI repository. The entry is created within the credential store, which will allow the Java EE Agent to authenticate itself to be able to use the required resources. This user should be a user that is already set up in ODI Security Navigator. In this practice, you use the SUPERVISOR user. To do this, you execute the WebLogic Scripting Tool (WLST), connect to the running Admin server, and add the credential store. To start WLST, open a new command window, change directory to the WLS_HOME common bin, C:\oracle\Middleware\oracle_common\common\bin, and then execute the command wlst.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. To connect to the running Admin server, execute the following command: connect('weblogic','welcome1','t3://localhost:7001')

d. Execute the following command to add the correct credential store for ODI Supervisor:

createCred(map="oracle.odi.credmap", key="SUPERVISOR", user="SUPERVISOR", password="SUNOPSIS", desc="ODI SUPERVISOR Credential").

Also execute the command to add the correct credential store for the domain ODI-DOMAIN: createCred(map="oracle.odi.credmap", key="ODI-DOMAIN", user="weblogic", password="welcome1", desc="ODI-DOMAIN Credential").

To exit WLST, execute the command exit().

Note: All commands are case-sensitive. You can copy commands from the text file 20-2.txt, which is provided in the c:\Labs\Text folder.

Note: At run time, the Java EE Agent will access this credential store authentication.

When you exit WLST, you return to the Windows command shell.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. To start the odi_server1 managed server, change directory to WLS Home –C:\oracle\Middleware\user_projects\domains\ODI-DOMAIN\bin, and use the execute command StartManagedWeblogic odi_server1. For Username, enter weblogic. For password, enter welcome1. The server will take a minute or two to start up and reach the RUNNING mode. Verify that your managed server is started in RUNNING mode.

Minimize (but do not close) this odi_server1 command window.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Return to ODI Topology Navigator. Click the Physical Architecture tab and open the OracleDIAgent definition tab. Click Test to test the connectivity of your configured ODI Java EE agent. When you see the success message in the ODI Information window, click OK.

4. Use Enterprise Manager to administer ODI.

a. Start the browser and enter http://localhost:7001/em. Log in to Enterprise Manager. For User Name, enter weblogic. For Password, enter welcome1. On the Accessibility Preference screen that follows, click Continue.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. With Enterprise Manager, you can monitor and manage processes and resources. You

can explore the deployed application by clicking each link. You can see running processes, workload, log file information, and more.

c. Drill down into ODI objects by expanding ODI in the left panel. Expand the ODI node.

Then click MASTER REPOSITORY to view more details.

Note: Your information may differ from the information provided on these screens.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Click the OracleDIAgent node in the left panel to see the Java EE agent information.

e. Click the localagent node to see details for the stand-alone agent.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Expand the Application Deployments node, and then click oraclediagent. View

the WebLogic server application deployment information.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




5. Use ODI Console to explore the ODI environment. a. In the browser, click Logout to exit Enterprise Manager. Enter

http://localhost:8002/odiconsole to start the Oracle Data Integrator Console. To log in, enter SUPERVISOR as the User Id and enter SUNOPSIS as the Password.

b. In the left panel, click the Design Time tab. Expand Projects, Models, and Topology.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




c. Select the Export-FF-RT project, and then click the View icon:

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




d. Scroll down and click the First Folder link. Click the Interfaces tab. Explore the interfaces created in your Export-FF-RT project.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. Exit the First Folder by clicking the View button again in the left panel. Click other tabs in the right panel. Explore the different ODI objects in your project.

f. Similarly, explore models in your Work Repository. To explore the datastores, scroll down and select the Global sub-model name. Scroll down and view the datastores.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




When you have finished exploring, log out of ODI Console and close the browser.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




6. Stop the odi_server1 managed server and the WebLogic server.

a. To stop odi_server1, open a command shell and change directory to WLS Home – C:\oracle\Middleware\user_projects\domains\ODI_DOMAIN\bin. Then execute the command stopManagedWebLogic odi_server1. For username, enter weblogic. For password, enter welcome1.

b. To stop the WebLogic server, execute the command stopWebLogic.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.


Practices for Lesson 21: Extending Oracle Data Integrator with SDK, Web Services, and SOA

Chapter 21 - Page 1


Chapter 21

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 2


Practices Overview In this practice, you create a new scenario, modify a Web service, and invoke the Web service to execute the scenario.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 3

Practice 21-1: Executing an ODI Scenario Through the ODI Public Web Service

Use Case Scenario John defined the procedure that creates a relational table and populates it with data. Now, John needs to generate a scenario and create an ODI public Web service to execute the scenario.

Background: This practice has two aims: 1) to teach you how to execute a scenario through the ODI public Web service and 2) to become familiar with the use of the OdiInvokeWebService tool. Therefore, the OdiInvokeWebService tool will be used to invoke the ODI public Web service that commands an agent to connect to a given Work Repository, and to start a specific scenario. In this practice, you use the interface that was created in Practice 10-1.

1. Create and execute a new scenario, INT_EXP_FF_RT.

2. Create a new package, OdiInvokeWebService. 3. Select the OdiInvokeWebService tool from the toolbox and create the corresponding step

in the package.

4. Define parameters of the Web service by using the Advanced dialog box and the SOAP Editor.

5. Invoke your Web service from the SOAP Editor to execute the scenario INT_EXP_FF_RT.

6. View the response and verify the execution results.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 4

Your Tasks 1. If not connected, connect to the Work Repository, WORKREP. To connect, click Connect

To Repository. Select DEV_ODI_REPO from the Login Name drop-down list. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to log in.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 5

a. In Designer Navigator, expand the project Export-FF-RT > First Folder > Interfaces. Right-click the INT-EXP-FF-RT interface and select Generate Scenario. Name the new scenario: INT_EXP_FF_RT. Set the version to 001.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 6

b. In Topology Navigator, verify the connection to your stand-alone agent localagent. Note: Ensure that your stand-alone agent is created and successfully started. To create and start an ODI agent, refer to Practice 3-1.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 7

c. In Designer Navigator, expand the INT-EXP-FF-RT interface and the Scenarios node. Right-click the INT_EXP_FF_RT Version 001 scenario and execute your newly created scenario, as follows. For Agent, select localagent. Click OK to confirm the execution.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 8

d. View the results of the execution in Operator Navigator. Click Refresh. Expand the All

Executions node. The INT_EXP_FF_RT session should appear as complete.

.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.



Chapter 21 - Page 9

2. Create a new package by using the OdiInvokeWebService tool. a. On the Projects tab, expand the Procedure-CRT-TBL project > First Folder. Right-

click Packages and select New Package. Enter OdiInvokeWebService in the Name field, and then click the Diagram tab.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. In the Toolbox panel, scroll down to the OdiInvokeWebService tool. Drag the OdiInvokeWebService tool to the empty panel on the right. An OdiInvokeWebService tool step appears in the package.

c. In the Properties panel, retain the default name OdiInvokeWebService 1 for the “Step

name” field. Scroll down and define parameters in the Properties panel as follows:

Parameter Value

WSDL URL - Leave blank -

Port Type - Leave blank -

Operation - Leave blank -

XML Request - Leave blank -

Request File - Leave blank -

Storage Mode for Response File FILE APPEND

File Encoding for Response File ISO8859_1

XML Encoding for Response File ISO-8859-1

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Response File C:/TEMP/WebServiceToolStep

Response File Format SOAP

HTTP User - Leave blank -

HTTP Password - Leave blank -

Execution Timeout (ms) - Leave blank -

d. Click the Advanced button. A new window opens. Enter http://localhost:20910/oraclediagent/OdiInvoke?wsdl in the Web Service Description File (WSDL) URL field. You can optionally copy this path from the text file 21-1.txt in

the C:\Labs\Text folder. Click the Connect to WSDL button . The appropriate port OdiInvokePortType is automatically detected and the available operations are displayed on the left.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




e. Select the invokeStartScen operation from the panel on the left. Click the Editor tab at

the bottom of the middle panel. Widen the middle panel, if necessary, to see the Name and Value columns. Enter the parameters in the request parameter fields as provided in the following table. Your SOAP Editor panel should appear as follows. Note: Press Enter each time to confirm your entries.

Parameter Value

<OdiUser> SUPERVISOR

<OdiPassword> SUNOPSIS

<WorkRepository> WORKREP

<ScenarioName> INT_EXP_FF_RT

<ScenarioVersion> 001

<Context> DEVELOPMENT

<Synchronous> true

LogLevel 5

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




f. Click the Source tab to view the scenario request. Click the Remove Blank Optional

Elements icon to remove all blank optional elements from the query. This is necessary to construct a valid query. The request should be as follows.

Note: You can also select the Clean up before execution check box . This option automatically deletes the empty optional elements in the SOAP request.

O

racl

e U

nive

rsity

and

(Ora

cle

Cor

pora

tion)

use

onl

y.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




3. Invoke your Web service.

a. To invoke your Web service, click the Invoke Web Service icon. The response is displayed in the right panel of the editor. Verify that your Web service processed successfully, as shown in the following screenshot:

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.




b. Click OK to close the Advanced Editor window. Click the Save button to save your

package and close the tab.

c. In ODI Operator, verify that the INT_EXP_FF_RT scenario successfully executed.

Ora

cle

Uni

vers

ity a

nd (O

racl

e C

orpo

ratio

n) u

se o

nly.

Thes

e eK

it m

ater

ials

are

to b

e us

ed O

NLY

by

you

for t

he e

xpre

ss p

urpo

se S

ELF

STU

DY.

SH

ARIN

G T

HE

FILE

IS S

TRIC

TLY

PRO

HIB

ITED

.