Practices for Lesson 10: Overview Practices Overview
Practice 10-1: Creating ODI Interface: Exporting a Flat File to a Relational Table
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 data loaded into 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 create from scratch an ODI project and an interface to export data from a flat file to a relational table, execute and monitor the Interface with ODI, and check data quality.
Note: Completing this practice is critical for all the following practice sessions.
In this practice, you perform the following 10 steps:
1. In Topology Navigator, define the FILE_GENERIC.C:\labs\Files\Flat_files physical schema.
2. In Topology Navigator, define the FLAT_FILES_SRC logical schema.
3. In Designer Navigator, create the Export-FF-RT project.
4. In Designer Navigator, create the Flat_File_1 source model.
a. Create the SRC_SALES_PERSON datastore.
b. Point to the resource: C:\labs\Files\Flat_files\SRC_SALES_PERSON.TXT.
c. Reverse-engineer and format the data (fixed length positions, data types).
5. In SQL Developer, create the RDBMS schema ODI_STAGE to host the ODI target datastore.
6. In SQL Developer, create the TRG_SALES_PERSON table to serve as the ODI datastore for the target model.
7. In Topology Navigator, create the ODI target data server, ODI_STAGE; physical schema ODI_STAGE; and logical schema ODI_STAGE.
8. In Designer Navigator, create the ODI target model, Oracle_RDBMS1.
9. Reverse-engineer the model and check the populated TRG_SALES_PERSON datastore table.
10. Create a new ODI interface to perform the flat file to RDBMS table transformation.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
Your Tasks:
1. Create a new physical schema for the source flat-file model.
a. If not still connected, connect to the Work Repository, DEV_ODI_REPO. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to log in.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
b. Click Topology navigator. In Topology navigator, click the Physical Architecture tab, expand Technologies > File. Right-click FILE_GENERIC, and then select New Physical Schema.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
c. For the Directory (Schema) and Directory (Work Schema) fields, enter the path to the directory where your input flat files are located (C:\Labs\Files\Flat_files).
Select the Default check box and click Yes in the Confirmation window, and then click Save. In the Information window that reminds you that no context has been specified for this schema, click OK. Close the editing window for your new physical schema.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
d. Verify that your new Physical schema was added under the FILE_GENERIC data server in the Technologies tree view.
e. Open Logical Architecture, navigate to Technologies > File, right-click File, and select New Logical Schema.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
f. Enter the Logical Schema name: FLAT_FILES_SRC, and select the Physical schema FILE_GENERIC.C:\Labs\Files\Flat_files in all three contexts as shown here.
Click Save and close the editing window.
g. Verify that your new Logical schema was added under the File node in the Technologies tree view.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
2. Create the new project in ODI.
a. Open the Designer tab and then click Projects. In the window that opens, click the NewProject button and then select New Project to add a new project.
b. On the screen that appears, set the Name of the project to Export-FF-RT in the Namefield. The Codefield is filled in automatically. Click Save, and then close the tab.
The newly created Export-FF-RT project now appears in the Projects pane.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
3. Create a new model for the flat-file source datastore.
a. In ODI Designer, click the Models tab, and then click the New Model . Then select the New Model option to insert a new model.
b. On the screen that follows, enter the values provided in the following table. Click the Reverse Engineer tab. Set the Context to Development. Click the Save icon.
Parameter Value
Name Flat_File_1 Technology File
Logical Schema FLAT_FILES_SRC
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
4. Create a new ODI source datastore.
a. Right-click the newly created Flat_File_1 model, and then select New Datastore.
b. On the screen that appears, set the Name to SRC_SALES_PERSON. Click the button next to the Resource Name field. Navigate to the C:\Labs\Files\Flat_files folder, and then select the SRC_SALES_PERSON.txt file. Click Open.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
c. Click the Files tab. Set the File Format to Fixed, and then click the Columns tab.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
d. On the Columns tab, click the Reverse Engineer button. A Confirmation message appears, asking you to save your changes. Click Yes. The Column Setup Wizard appears.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
e. Place the cursor, and then click the ruler at the beginning of each column as shown next. The wizard marks each column as shown in the following screenshots.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
f. After the columns are marked, click each column data to select a column, and then set the name and data type for each column in the Definition panel to the right, as shown in the following screens. For the names and data types of each column, refer to this table. Click OK when you have finished.
Column Name Data type
C1 SALES_PERSON_ID numeric
C2 FIRST_NAME string
C3 LAST_NAME string
C4 DATE_HIRED string
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
g. The columns should look as follows. Click Save to save the model. If the Unlocking Object window appears, select the check box and click OK. Expand Flat_File_1 >
SRC_SALES_PERSON > Columns and view the newly created columns.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
h. In the Flat_File_1 model, right-click the SRC_SALES_PERSON datastore, and then select the View Data option. Close all tabs as shown next.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
5. You must create a schema to host the ODI target datastore. To create a new RDBMS schema for the ODI datastore, perform the following steps:
a. Open the Oracle SQL Developer window .You will create the schema by executing the following SQL commands:
create user <MY_SCHEMA> identified by <MY_PASS>
default tablespace <MY_TBS> temporary tablespace <MY_TEMP>;
Note: If not started, you need to start SQL Developer and open the Administrator connection (Username: “system”, Password: “oracle1”). Refer to Practice 2-1.
In this command:
• <MY_SCHEMA> corresponds to the name of the schema you want to create:
ODI_STAGE
• <MY_PASS> corresponds to the password that you gave: ODI_STAGE
• <MY_TBS> corresponds to the Oracle tablespace where the data will be stored:
USERS
• <MY_TEMP> corresponds to the temporary default tablespace: TEMP
To create a user, enter the following command. Verify that user account ODI_STAGE is created successfully.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
Note: These commands also are provided in the text file 10-1.txt, which is located in the C:\LABS\Text folder.
create user ODI_STAGE identified by ODI_STAGE default tablespace users temporary tablespace temp;
b. Grant connect privileges to the newly created user account by executing the following SQL command:
grant connect, resource, create trigger, create view to ODI_STAGE;
6. Now you need to create a new ODI datastore for the target model. This datastore will be used within the ODI Interface. To create a new ODI target datastore, perform the following steps:
a. In SQL Developer, create a new connection called ODI_STAGE. Enter the username as ODI_STAGE with the password ODI_STAGE. For SID enter ORCL. Click Test to verify the connection. Click the Save button and then click Connect.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
b. Create your target table by executing the following SQL commands. Expand the connection, ODI_STAGE > Tables > TRG_SALES_PERSON, and verify that the table is created successfully.
CREATE table "TRG_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 "TRG_SALES_PERSON_PK" primary key("SALES_PERSON_ID") )
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
7. After you have created the new database target datastore, you need to create a new ODI target data server and the physical schema. To create the ODI target data server and physical schema, perform the following steps:
a. In ODI, open ODI Topology Navigator and then select the Physical Architecture tab.
Expand Technologies, right-click Oracle, and select New Data Server.
b. In the Data Server: New window, enter the values provided in the following table. Click the JDBC tab.
Parameter Value
Name ODI_STAGE
Instance/dblink (Data Server) ORCL
User ODI_STAGE
Password ODI_STAGE
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
c. Click for JDBC Driver. Select Oracle JDBC Driver. Click OK. Click for JDBC Url, select jdbc:oracle:thin:@<host>:<port>:<sid>, and then click OK. Edit the Url to read: jdbc:oracle:thin:@localhost:1521:ORCL for Oracle Database.
Note: Do not copy and paste in the JDBC Url field. This action may cause problems with entering a valid URL string.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable
license to use this Student Guideฺ
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 10: Interfaces: Monitoring and Debugging
d. Click Test Connection. In the window that opens, click Yes to save your data. In the Information window, click OK. Click Test to verify a successful connection. Click OK.
Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ
Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ
e. On the Physical Architecture tab, expand Oracle technology node, right-click ODI_STAGE data server, and then select New Physical Schema.
f. In the Schema (Schema) and Schema (Work Schema) fields, enter your ODI_STAGE schema. Click the Save button. In the Information window reminding you that no context has been specified for this schema, click OK. Close the
ODI_STAGE.ODI_STAGE physical schema window.