Creating ODI Interface: Exporting a Flat File to a Relational Table 2

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









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.






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.




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 (User Name: “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 ODI_STAGE is created successfully.
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;


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.





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




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



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.



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.

 
 
 
 
e. Expand Oracle technology node, right-click ODI_STAGE data server, and then select New Physical

f. In the Schema (Schema) and Schema (Work Schema) field, enter your ODI_STAGE schema. Click the Save button. Close the ODI_STAGE.ODI_STAGE physical schema window. In the Information window, click OK.




g. Open the Logical Architecture tab, expand Technologies > Oracle. Right-click Oracle technology and then select New Logical Schema.

h. Name logical schema ODI_STAGE. In all three contexts, connect this logical schema to the ODI_STAGE physical schema as shown here. Click the Save button and then close the tabs.

a. Open ODI Designer. Click the Models tab and select New Model. On the screen that appears, enter the values provided in the following table. Click the Reverse Engineer tab.



Parameter                                  Value
Name                                      Oracle_RDBMS1
Technology                                  Oracle
Logical Schema                       ODI_STAGE

8. Create a new ODI target model that will be used within your ODI Interface. To create a new ODI target model, perform the following steps:
b. On the Reverse Engineer tab, set the Context to Development. Click the Save button and then close the Oracle_RDBMS1 tab.


c. In the Models tab, right-click the Oracle_RDBMS1 model. Select Reverse Engineer. To verify that the TRG_SALES_PERSON datastore is successfully reversed, expand the model as shown here.