Creating RDBMS Schema for
ODI SourceDatastore
You
need to create a schema to host the RDBMS table. In this OBE, you use the RDBMS
user/schema ODI_STAGE.
To
create a new RDBMS schema for the ODI datastore,perform the following steps:
1. Start
SQL Developer. You will create the new schema/user by executing the following
SQL commands:
create
user ODI_STAGE identified by ODI_STAGE
default
tablespace users temporary tablespace temp;
|
grant
connect, resource, create trigger, create view to ODI_STAGE;
|
2. If not done before, in SQL Developer create new connection called ODI_STAGE. Enter User name as ODI_STAGE with password ODI_STAGE. For SID enter ORCL. Click Test to verify connection, and then click Connect
Creating a New ODI Dataserver, Physical
and Logical Schemas for RDBMS table
Note: You may skip this
section if you completed one of the following OBEs:
- Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table
- Creating an ODI Project and Interface: Exporting an RDBMS Table to a Flat File
After
you 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:
1. In ODI, Open ODI Topology Navigator and then
select the Physical Architecture tab. Expand Technologies, right-click Oracle
and select New Data Server.
2. In
the Data Server: New window, enter the values provided in the table below.
Click the JDBC tab.
Parameter
|
Value
|
Name
|
ODI_STAGE
|
Instance/dblink
(Data Server)
|
ORCL
|
User
|
ODI_STAGE
|
Password
|
ODI_STAGE
|
3. 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 may cause problems with entering a valid URL string.
4. Click Test Connection. In the window that opens, click Yes to save your data. In the Information window, click OK. Click Test to verify successful connection. Click OK.
5. Click
Expand Oracle technology node, right-click ODI_STAGE dataserver, and then
select New Physical Schema.
6. In
Schema (Schema) and Schema (Work Schema) field enter your ODI_STAGE schema.
Click Save button. Close ODI_STAGE.ODI_STAGE physical schema window. In the
Information window, click OK.
7. Open
Logical Architecture tab, expand Technologies > Oracle. Right-click Oracle
technology and then select New Logical Schema.
1. Name logical schema ODI_STAGE. In the Global
context, connect this logical schema to ODI_STAGE physical schema as shown
below. Click Save
button and then close the tabs.
Creating a New ODI Project
To
create a new project within Oracle Data Integrator, perform the following
steps:
1. Start
ODI Designer: Start > Programs > Oracle > Oracle
Data Integrator > ODI Studio . Select WORKREP1 from the
Login Name drop-down list if not already selected. Enter SUPERVISOR in
the User field and SUNOPSIS in the Password field. Click OK to
login.
2. In
the designer tab, click the Projects tab, click New Project icon
, and then click New Project.
3. On
the screen that appears, set the Name of the project to Procedure-CRT-TBL
in the Name field. The Code field is filled automatically. Click Save
icon
The newly created Procedure-CRT-TBL
project now appears in the Projects tree view. You have now successfully added
a new ODI project.
The newly created Procedure-CRT-TBL
project now appears in the Projects tree view. You have now successfully added
a new ODI project.
Creating ODI Procedure to Create and
Populate RDBMS Table
To
create a new ODI procedure to create and populate RDBMS table, perform the
following steps:
1. In the Projects tab, expand: Procedure-CRT-TBL
> First Folder. Right-click Procedures and select New Procedure.
2. 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.
to add a step in the procedure. 
3. In
the Command: New window, enter the parameters of the procedure and the command
on target provided below. You need this command to drop the table in case the
table with this name exists. Select the Ignore Errors check box. Select details
tab.
Parameter
|
Value
|
Name
|
Drop
table
|
Technology
|
Oracle
|
Schema
|
ODI_STAGE
|
DROP
table ODI_STAGE.SRC_SALES_PERSON1
Note: You may find this and
following commands here.
DROP table ODI_STAGE.SRC_SALES_PERSON1
-----------------------------------------------------------------------------------------
CREATE table "SRC_SALES_PERSON1" (
"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_PERSON1_PK" primary key("SALES_PERSON_ID")
)
-----------------------------------------------------------------------------------------
begin
insert into ODI_STAGE.SRC_SALES_PERSON1 values(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(20,'Jennie','Daumesnil','28/02/1988',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values(51,'Cynthia','Nagata','28/02/1994',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values(52,'William','Kudo','28/03/1993',sysdate);
end;
4. Click
the icon
 to add another step in the
procedure. In the Command: New window, if not selected, click the “Command on
Target” tab. Enter the parameters of the procedure and the command provided
below, and then click the Details tab.
  |
5. Click the icon
to enter the step to populate the SRC_SALES_PERSON
table. Enter the parameters of the procedure and the command provided below,
and then click the Details tab.
Parameter
|
Value
|
Name
|
Populate
table
|
Technology
|
Oracle
|
Schema
|
ODI_STAGE
|
begin
insert into
ODI_STAGE.SRC_SALES_PERSON1 values
(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON1 values (12,'John','Galagers','20/04/2000',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (13,'Jeffrey','Jeferson','32422',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (20,'Jennie','Daumesnil','28/02/1988',sysdate);
insert into
ODI_STAGE.SRC_SALES_PERSON1 values
(21,'Steve','Barrot','24/09/1992',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (22,'Mary','Carlin','14/03/1995',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (30,'Paul','Moore','36467',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (31,'Paul','Edwood','18/03/2003',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (32,'Megan','Keegan','29/05/2001',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (40,'Rodolph','Bauman','29/05/2000',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (41,'Stanley','Fischer','37233',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (42,'Brian','Schmidt','25/08/1992',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (50,'Anish','Ishimoto','30/01/1992',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON1 values (51,'Cynthia','Nagata','28/02/1994',sysdate);
insert into
ODI_STAGE.SRC_SALES_PERSON1 values
(52,'William','Kudo','28/03/1993',sysdate); end |
6. View your newly created commands and click Click Save icon
to save the procedure. Close the tab.
7. Expand
Procedure-CRT-TBL > Procedures, select the newly created procedure
PRD-create-populate-table, and then select Execute. Click OK. Click OK again.
8. Open ODI Operator. In ODI Operator, click All
executions node, click the
Refresh 
icon, find your session, and verify that your
procedure executed successfully.
icon, find your session, and verify that your
procedure executed successfully. 
9. Open the Oracle SQL developer and expand ODI_STAGE connection. Select Tables node and click the Refresh
Double–click the newly created table
SRC_SALES_PERSON1, click Data tab, and verify that your table SRC_SALES_PERSON1
was successfully created and populated with records as shown below. 
No comments:
Post a Comment